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
