Ever have one of those moments when you look at something inside SQL Server, and say "Why'd they do that?" I had one of those recently. I was researching for a customer the impact of SQL Server 2005 changes to their existing index maintenance scripts before the upgrade. These scripts use the good ol' DBCC SHOWCONTIG and INDEXDEFRAG\DBREINDEX. Wanting to take advantage of new features and syntax, I started re-writing it (quick and dirty of course) to see what the effort would be to update the script for SQL 2005.
I excitedly started by using the highly touted dm_db_index_physical_stats() collecting just the indexes that require maintenance. Wow! Great feature these DMVs. Nice work by the folks in Redmond. I then started looking at how to pass that data to an ALTER INDEX statement. That's when it happened - my head turned sideways, my hand crept to my head and began to scratch, and the words actually came out of my mouth, "Why'd they do that?".
You see, ALTER INDEX takes index name, database name, schema name, table name as parameters. But dm_db_index_physical_stats() returns IDs, not names. "Easily solved", I thought. "A simple join will do the trick, and I'll have all the names I want. I can pass them to ALTER INDEX and ....uh, oh. The schema, table, and index names are inside the databases, and I have a database_ID. Sounds like some looping is in order."
That's when I was became a bit disappointed with the Redmond bunch. They went to all the trouble of creating DMVs and making it easier to get the fragmentation data, but they actually made it more difficult to do something with the data to alleviate fragmentation. You see, DBCC INDEXDEFRAG has the really nice feature of allowing you to pass in either the name or ID. Which means I have less looping to do to defrag the indexes. For a quick and dirty version of this look at the examples in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_46cn.asp.
Well, I took a stab at creating the script to do it in SQL 2005. Here's what I have so far. I'm pretty sure I can do better than this, but it works fairly well. I'd be interested to see others have come up with.
use master
go
set nocount on
declare @fragPctThreshold decimal(5,2)
declare @fragCntThreshold int
declare @ONLINE bit
set @fragPctThreshold = 20
set @fragCntThreshold = 2
set @online = 0
declare @dbID int
declare @dbName sysname
declare @tblID int
declare @tblName sysname
declare @indexID int
declare @indexName sysname
declare @schemaName sysname
declare @partitionNumber int
declare @strSQL nvarchar(3000)
declare @parmDef nvarchar(300)
declare @rc int
create table #fragObjects
(
database_id int,
object_id int,
index_id int
)
declare crDBs cursor FAST_FORWARD for
select database_id, name from sys.databases
where database_id > 4
open crDBs
Fetch next from crDBs into @dbID, @dbName
while @@FETCH_STATUS = 0
BEGIN
insert #fragObjects
select [database_id], [object_id], [index_id]
from sys.dm_db_index_physical_stats(@dbid, null, null, null, null)
WHERE [avg_fragmentation_in_percent] > @fragPctThreshold and [fragment_count] > @fragCntThreshold
declare crTbls cursor FAST_FORWARD for select [object_id], [index_id] from #fragObjects
open crTbls
Fetch next from crTbls into @tblID, @indexID
WHILE @@Fetch_status = 0
BEGIN
set @parmDef = N'@tblIDIN int, @indexIDIN int,
@tblNameOUT sysname OUTPUT,
@indexNameOUT sysname OUTPUT,
@schemaNameOUT sysname OUTPUT'
set @strSQL = N'use ' + @dbname + '; select @tblNameOUT = o.name, @schemaNameOUT = s.name, @indexNameOUT =
ISNULL(i.name, ''ALL'') from
(select * from sys.objects where type in (''u'', ''v'')) o
inner join sys.schemas s on o.schema_id = s.schema_id
inner join sys.indexes i on o.object_id = i.object_id
WHERE o.object_ID = @tblIDIN and i.index_id = @indexIDIN'
exec @rc = sp_EXECUTESQL @strSQL,
@parmDef,
@tblIDIN = @tblID,
@indexIDIN = @indexID,
@tblNameOUT = @tblName OUTPUT,
@indexNameOUT = @indexName OUTPUT,
@schemaNameOUT = @schemaName OUTPUT
set @strSQL = 'ALTER INDEX ' + @indexName + ' on '+ @dbName + '.' + @schemaName + '.' + @tblName +
' REBUILD '
if @ONLINE = 1
set @strSQL = @strSQL + 'with (ONLINE = ON)'
exec sp_EXECUTESQL @strSQL
Fetch next from crTbls into @tblID, @indexID
END --While @tblName fetch_status
DEALLOCATE crTbls
truncate table #fragObjects
Fetch next from crDBs into @dbID, @dbName
END --While @@Fetch Status
DEALLOCATE crDBs
DROP TABLE #fragObjects