At present I got here throughout a dice processing efficiency concern with considered one of our shoppers. So I began a step-by-step troubleshooting together with optimising named queries. In some circumstances the named queries had been truly querying some SQL views from the supply information warehouse.
In spite of everything, I created about 35 new indexes and I wanted to justify that each one of these indexes are actually used. As I processed the defective dice a number of occasions throughout my step-by-step troubleshooting course of it appeared all of these indexes had been used.
However, I knew that I created some indexes that lined by among the new ones and people indexes gained’t be used.
I wanted to rebuild all of the indexes, nevertheless, rebuilding all of these indexes from SSMS UI can be such a ache. So I wanted to do a batch index rebuild.
So I googled and I’ve discovered some scripts which truly are doing the job, however, all of them had been utilizing cursors. Sadly, I hate cursors so they’re the final merchandise in my ebook. Certainly, I’ll by no means use cursors till it’s completely crucial and there’s no different higher selections.
Subsequently, I made a decision to do it in my approach and I wrote the next script. I assumed I’d be completely happy to share it with you guys as it’d assist a few of you as effectively.
declare @ix varchar(max), @tbl varchar(max), @counter int, @CustomIx Varchar(max)
declare @desk desk (id int, tbl varchar(max), ix varchar(max))
set @CustomIx = ‘YOUR_INDEX_NAME_STARS_WITH’ –Customized index identify shall be like MY_IX_***
insert into @desk (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= (choose max(id) from @desk)
whereas @counter >=1
start
set @ix = (choose ix from @desk the place id = @counter)
set @tbl = (choose tbl from @desk the place id = @counter)
exec(‘ALTER INDEX ‘+@ix+‘ ON [dbo].[‘+@tbl+‘] REBUILD PARTITION = ALL ‘)
print @tbl + ‘.’ + @ix + ‘ Rebuild profitable’
set @counter-=1
finish
NOTE: @CustomIx is outlined as a result of I don’t wish to do a batch index rebuild on all indexes, however, solely these ones which I newly created. As I all the time use a naming conference to create SQL objects I can simply discover no matter I want. I take advantage of IX_[IndexType][IndexName][TableName]_[MajorColumnsIndexed] as my naming conference for indexes. So as an example my index must be one thing like this: IX_NC_PurchOrder_PurchLine_CodeItemCategory.
All accomplished!
UPDATE 1: Utilizing cursors or not utilizing them! The talk is round for ages. Personally, I hate cursors from SQL Server 2000 as a result of they had been ugly, sluggish they usually consumed a variety of assets. After SQL Server 2005 SP2 cursors improved and optimised, however, they had been and nonetheless are ugly. Once I say I hate cursors that doesn’t imply I like WHILE loops.
Typically talking of cursors, they put locks on the tables by default whereas utilizing a WHILE loop you truly forestall locks. Once more, I insist to not use cursors until you’ve got a agency justification. In the event you can assemble a set-based question, then go for it. In the event you can’t then I say it is determined by your case and you must determine to go for a CURSOR or a WHILE loop.
As we nonetheless have SQL Server 2005 SP1 shoppers, saying that I all the time have this in my head that “DO NOT GO FOR A CURSORS”, I want to do the job with the WHILE loop as I wish to hold the codes constant throughout all initiatives.
On this explicit case, you continue to want to make use of a WHILE loop to run the dynamic SQL even in case you outline a cursor. The usage of cursor for this situation is to learn Tables’ and indexes’ names out of DMVs. I constructed the question with a FAST_FORWARD CURSOR and guess what? The variety of reads utilizing CURSOR is far greater as compared with the WHILE loop and it took longer to get the job accomplished. No shock. Right 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 want to thank Aaron Cutshall for sharing his model of code which even avoids the WHILE loop. Under code snipped does the job very properly. Frankly, this one is smarter and higher resolution than utilizing a WHILE loop. It’s higher within the variety of reads, writes, useful resource consumption and due to this fact it’s quicker than the WHILE loop development.
DECLARE @CmdStr VARCHAR(max) = NULL,
@CustomIx VARCHAR(max) = ‘YOUR_INDEX_NAME_STARS_WITH’; –Customized index identify shall 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);
Associated
Supply hyperlink
