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

Continue reading “Batch Index Rebuild without Using Cursor” →