November 7, 2012 at 8:31 pm
Comments posted to this topic are about the item Smart Index Rebuild - All Databases
November 8, 2012 at 12:23 pm
I ran this script on one of my development servers & all worked fine.
I ran it a second time thinking it shouldn't find any more indexes to rebuild, but it's reporting nearly all the same indexes and the same amount of fragmentation during the second run.
I ran it a third time and same thing.
Any idea or explanation for this behavior?
November 8, 2012 at 12:31 pm
Yes - I've also found that. For some indexes, the LogicalFrag never reduces further than a certain value. This may be because there are very few values in the index so the way in which the LogicalFrag is calculated may always yield and above 0 value - just a guess. I've not worried about these because on investigation they've turned out to be non-consequential indexes, or not even being used by the query optimiser at all.
I also keep in mind that a highly used index covering a large table that is 30% fragmented is potentially more of a performance concern that an index on a table with 100 rows that is 90% fragmented.
November 8, 2012 at 12:46 pm
You've modified the code to use the ALTER INDEX instead of the DBCC DBREINDEX, but you're still using the legacy DBCC SHOWCONTIG to determine fragmentation. Also, I don't see anything specifically filtering on number of pages. Some indexes with very few pages will never show an improvement in fragmentation no matter how many times you rebuild them.
I'd recommend reviewing Ola Hallengren's maintenance processes here: http://ola.hallengren.com/, it's an industry standard run by many DBA's.
We use a customized version of Andrew Kelly's maintenance process you can find here: http://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help.
I built a wrapper stored procedure for Andrew Kelly's script that loops through all of the databases confirming that they are in an online and available state before attempting to perform the maintenance. It's worked very well for us for the last 3 and a half years.
December 12, 2013 at 11:16 am
George M Parker already posted both of these points, but due to the importance of each, I though they could use some additional discussion.
1) In other articles about index fragmentation, I have seen recommendations that range from 600 to 1000 as the minimum-number-of-pages filter which should be included in a script that performs index maintenance. So they are saying don't waste resources rebuilding indexes that are not causing performance problems, even if they are highly fragmented.
2) According to both the SQL Server documentation and the Transact-SQL reference:
"DBCC SHOWCONTIG will be removed in a future version of SQL Server. Avoid using this feature in new development work,
and plan to modify applications that currently use this feature.
Use sys.dm_db_index_physical_stats instead."
Furthermore, even on versions where it hasn't been removed (SQL Server 2005 or later), you should still use the sys.dm_db_index_physical_stats function because DBCC SHOWCONTIG has some restrictions which will cause it to not display results under certain conditions (for example, newer data types, table partitioning, etc.).
December 13, 2013 at 1:59 pm
What version of SQL is this script meant for?
September 4, 2015 at 7:50 am
Hi all,
Longtime lurker, first time contributor. I have the following script deployed in an SSIS package on a SQL2008 server, scheduled to run weekly, and it seems to work very well - and it doesn't suffer from the issue Dave Wendt and SR have experienced with the featured script. It takes user-defined fragmentation, index page count, and table row count thresholds, so that only indexes that are large enough to need rebuilding are picked up when the script runs.
It's limited in that it STUFFs all of the REBUILD commands into a single dynamic SQL string, rather than iterating over the list of indexes, which is subject to the varchar 8000-character limit. One way to resolve this is to run the script several times initially, then scheduling it to run at a regular enough interval so as to catch everything in a single execution. This could be modified to iterate over the list of indexes, but so far I've not had the need to do so.
The script as I have it deployed is executed in a Foreach container, which loops over a collection of databases - the names of these are passed into the first variable in the script.
/* DEFINE VARIABLES */
DECLARE @DB AS VARCHAR(100) = ?
,@Fragmentation TINYINT = 20
,@PageCount SMALLINT = 500
,@RowCount SMALLINT = 10000
;
/* BUILD SCRIPT */
DECLARE @sql AS VARCHAR(8000) = ''
;
WITH ToRebuild AS (
SELECT
o.name [Table]
,i.name [Index]
,s.index_type_desc
,s.avg_fragmentation_in_percent
FROM sys.objects o
INNER JOIN sys.indexes i
ON i.object_id = o.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(@DB), NULL, NULL, NULL, NULL) s
ON s.index_id = i.index_id
AND s.object_id = o.object_id
INNER JOIN sys.dm_db_partition_stats p
ON p.object_id = o.object_id
WHERE 1=1
AND o.[type] = N'U'
AND NOT s.index_type_desc = N'HEAP'
AND s.avg_fragmentation_in_percent > @Fragmentation
AND s.page_count >= @PageCount
AND p.row_count >= @RowCount
GROUP BY
o.name
,i.name
,s.index_type_desc
,s.avg_fragmentation_in_percent
)
SELECT @sql = STUFF((SELECT CHAR(10) + 'ALTER INDEX [' + i.[Index] + '] ON [' + i.[Table] + '] REBUILD;' FROM ToRebuild i ORDER BY i.[Table] FOR XML PATH ('')),1,1,'')
/* REMOVE ANY PARTIAL COMMAND IN STRING RESULTING FROM 8000-CHARACTER VARCHAR LIMIT BEING REACHED */
SELECT @sql = REVERSE(RIGHT(REVERSE(@SQL),LEN(@SQL)-CHARINDEX(REVERSE('REBUILD;'),REVERSE(@SQL))+1))
/* REBUILD INDEXES */
--EXEC sp_sqlexec @sql
PRINT @sql
Hope you find this useful!
Mart
EDIT: Just noticed the age of this thread... this script was the featured script in today's SQL Server Central, so I naïvely thought this discussion to be current. Apologies for resurrecting a dead thread.
September 8, 2015 at 3:00 pm
We have a similar script.
May 5, 2016 at 7:11 am
...but I see this one has some good code in it. Thanks.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply