Today I came across a cube processing performance issue with one of our clients. So I started a step-by-step troubleshooting including optimising named queries. In some cases the named queries were actually querying some SQL views from the source data warehouse.
After all, I created about 35 new indexes and I needed to justify that all of those indexes are really used. As I processed the faulty cube several times during my step-by-step troubleshooting process it seemed all of those indexes were used.
But, I knew that I created some indexes that covered by some of the new ones and those indexes won’t be used.
I needed to rebuild all the indexes, however, rebuilding all of those indexes from SSMS UI would be such a pain. So I needed to do a batch index rebuild.
So I googled and I’ve found some scripts which actually are doing the job, but, all of them were using cursors. Sadly, I hate cursors so they are the last item in my book. Indeed, I’ll never use cursors until it’s absolutely necessary and there is no other better choices.
Therefore, I decided to do it in my way and I wrote the following script. I thought I’d be happy to share it with you guys as it might help some of you as well.
declare @ix varchar(max), @tbl varchar(max), @counter int, @CustomIx Varchar(max)
declare @table table (id int, tbl varchar(max), ix varchar(max))
set @CustomIx = ‘YOUR_INDEX_NAME_STARS_WITH’ –Custom index name will be like MY_IX_***
insert into @table (id, tbl, ix)
SELECT ROW_NUMBER() over (order by ix.[NAME]) id
, OBJECT_NAME(ixstat.[OBJECT_ID]) AS [OBJECT NAME]
, ix.[NAME] AS [INDEX NAME]
FROM SYS.DM_DB_INDEX_USAGE_STATS AS ixstat
INNER JOIN SYS.INDEXES AS ix
ON ix.[OBJECT_ID] = ixstat.[OBJECT_ID]
AND ix.INDEX_ID = ixstat.INDEX_ID
WHERE OBJECTPROPERTY(ixstat.[OBJECT_ID],‘IsUserTable’) = 1
and ix.[NAME] like @CustomIx+‘%’
set @counter= (select max(id) from @table)
while @counter >=1
begin
set @ix = (select ix from @table where id = @counter)
set @tbl = (select tbl from @table where id = @counter)
exec(‘ALTER INDEX ‘+@ix+‘ ON [dbo].[‘+@tbl+‘] REBUILD PARTITION = ALL ‘)
print @tbl + ‘.’ + @ix + ‘ Rebuild successful’
set @counter-=1
end
NOTE: @CustomIx is defined because I don’t want to do a batch index rebuild on all indexes, but, only those ones which I newly created. As I always use a naming convention to create SQL objects I can easily find whatever I need. I use IX_[IndexType][IndexName][TableName]_[MajorColumnsIndexed] as my naming convention for indexes. So for instance my index should be something like this: IX_NC_PurchOrder_PurchLine_CodeItemCategory.
All done!
UPDATE 1: Using cursors or not using them! The debate is around for ages. Personally, I hate cursors from SQL Server 2000 because they were ugly, slow and they consumed a lot of resources. After SQL Server 2005 SP2 cursors improved and optimised, but, they were and still are ugly. When I say I hate cursors that doesn’t mean I love WHILE loops.
Generally speaking of cursors, they put locks on the tables by default while using a WHILE loop you actually prevent locks. Again, I insist not to use cursors unless you have a firm justification. If you can construct a set-based query, then go for it. If you can’t then I say it depends on your case and you should decide to go for a CURSOR or a WHILE loop.
As we still have SQL Server 2005 SP1 clients, saying that I always have this in my head that “DO NOT GO FOR A CURSORS”, I prefer to do the job with the WHILE loop as I like to keep the codes consistent across all projects.
In this particular case, you still need to use a WHILE loop to run the dynamic SQL even if you define a cursor. The use of cursor for this scenario is to read Tables’ and indexes’ names out of DMVs. I constructed the query with a FAST_FORWARD CURSOR and guess what? The number of reads using CURSOR is much higher in comparison with the WHILE loop and it took longer to get the job done. No surprise. Here is the code snipped with CURSOR:
DECLARE @TBL VARCHAR(255)
, @INX VARCHAR(255)
, @CUSTOMIX VARCHAR(MAX) = ‘YOUR_INDEX_NAME_STARS_WITH’
, @SQL VARCHAR(MAX)
DECLARE CURSORTBL CURSOR FAST_FORWARD FOR
SELECT OBJECT_NAME(IXSTAT.[OBJECT_ID]) AS [OBJECT NAME]
,IX.[NAME] AS [INDEX NAME]
FROM SYS.DM_DB_INDEX_USAGE_STATS AS IXSTAT
INNER JOIN SYS.INDEXES AS IX
ON IX.[OBJECT_ID] = IXSTAT.[OBJECT_ID]
AND IX.INDEX_ID = IXSTAT.INDEX_ID
WHERE OBJECTPROPERTY(IXSTAT.[OBJECT_ID], ‘ISUSERTABLE’) = 1
AND IX.[NAME] LIKE @CUSTOMIX+‘%’
OPEN CURSORTBL
FETCH NEXT FROM CURSORTBL INTO @TBL, @INX
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = ‘ALTER INDEX ‘ + @INX + ‘ ON DBO.’ + @TBL
+ ‘ REBUILD PARTITION = ALL;’
EXEC (@SQL)
FETCH NEXT FROM CURSORTBL INTO @TBL, @INX
END
CLOSE CURSORTBL
DEALLOCATE CURSORTBL
GO
UPDATE 2: I would like to thank Aaron Cutshall for sharing his version of code which even avoids the WHILE loop. Below code snipped does the job very nicely. Frankly, this one is smarter and better solution than using a WHILE loop. It is better in the number of reads, writes, resource consumption and therefore it is faster than the WHILE loop construction.
DECLARE @CmdStr VARCHAR(max) = NULL,
@CustomIx VARCHAR(max) = ‘YOUR_INDEX_NAME_STARS_WITH’; –Custom index name will be like MY_IX_***
SELECT @CmdStr = COALESCE(@CmdStr + Char(13), ”)
+ ‘ALTER INDEX ‘ + ix.NAME + ‘ ON [dbo].[‘
+ Object_name(ixstat.[object_id])
+ ‘] REBUILD PARTITION = ALL;’
FROM SYS.DM_DB_INDEX_USAGE_STATS AS ixstat
INNER JOIN SYS.INDEXES AS ix
ON ix.[object_id] = ixstat.[object_id]
AND ix.index_id = ixstat.index_id
WHERE Objectproperty(ixstat.[object_id], ‘IsUserTable’) = 1
AND ix.NAME LIKE @CustomIx + ‘%’;
EXEC (@CmdStr);
