SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Index health and defrag (SQL Server 2k2)

 
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository View previous topic
View next topic
Index health and defrag (SQL Server 2k2)
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 1401

Post Index health and defrag (SQL Server 2k2) Reply with quote
I use this when setting up scheduled maintenance supblans for reorganizing/rebuilding indices (where/when required). A bit more sophisticated and configurable than just adding a Reorganize/Rebuild Index Task which does this job mindlessly :)

Code:

$PROMPT(operation,Show\,Reorganize or Rebuild?,,,0 1 2,Show Reorganize Rebuild)$
$PROMPT(lower_limit,Lower limit of fragmentation?
Recommended values:
Rebuild : 30%
Reorganize : 10%,,,0 10 20 30 40 50)$
$PROMPT(upper_limit,Upper limit of fragmentation?
Recommended values:
Rebuild : 100%
Reorganize : 30%,8,,20 30 40 50 60 70 80 90 100)$
$PROMPT(min_page_count,Minimum number of pages?,200)$
$PROMPT(limit,Limit to TOP x?
(0 = no limit),0)$
$PROMPT(simonly,Simulate only?,,,"PRINT @sql" EXEC(@sql),"Yes\,print only." "No\,go for it!")$
 
USE $DB$
GO

DECLARE   @lower_limit INT = $lower_limit$
      ,@upper_limit INT = $upper_limit$
      ,@min_page_count INT = $min_page_count$

DECLARE   @sql NVARCHAR( MAX ) = '', @dbid INT = DB_ID()
SELECT $$SELECT CASE WHEN $limit$ = 0 THEN '' ELSE 'TOP $limit$' END$$
$$
SELECT CASE $operation$
WHEN 0 THEN
'   s.name AS schema_name
   ,t.name AS table_name
   ,i.name AS index_name
   ,i.type AS index_type
   ,i.type_desc AS index_type_desc
   ,i.is_disabled AS is_disabled
   ,ddips.avg_fragmentation_in_percent AS avg_frag
   ,ddips.index_depth AS index_depth
   ,ddips.page_count AS index_page_count'
WHEN 1 THEN
   '   @sql = @sql
   + ''ALTER INDEX '' + QUOTENAME( i.name ) + '' ON '' + QUOTENAME( s.name ) + ''.'' + QUOTENAME( t.name ) + '' REORGANIZE WITH ( LOB_COMPACTION = ON )''
   + '' -- avg fragmentation : '' + CAST( ddips.avg_fragmentation_in_percent AS VARCHAR( 64 ) ) + ''%,  index page count : '' + CAST( ddips.page_count AS VARCHAR( 64 ) )
   + CHAR( 10 )'
WHEN 2 THEN
   '@sql = @sql
   + ''ALTER INDEX '' + QUOTENAME( i.name ) + '' ON '' + QUOTENAME( s.name ) + ''.'' + QUOTENAME( t.name ) + '' REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )''
   + '' -- avg fragmentation : '' + CAST( ddips.avg_fragmentation_in_percent AS VARCHAR( 64 ) ) + ''%,  index page count : '' + CAST( ddips.page_count AS VARCHAR( 64 ) )
   + CHAR( 10 )'
END
$$
FROM
   sys.schemas s
   INNER JOIN
   sys.tables t
      ON   t.schema_id = s.schema_id
   INNER JOIN
   sys.indexes i
      ON   i.object_id = t.object_id
   INNER JOIN
   sys.dm_db_index_physical_stats( @dbid ,NULL ,NULL ,NULL ,NULL ) ddips
      ON   ddips.[object_id] = i.[object_id]
      AND   ddips.index_id = i.index_id
WHERE   i.index_id >= 0
   AND   i.type IN ( 1 ,2 ) -- clustered & nonclustered only
   AND   i.is_hypothetical = 0
   AND i.is_disabled = 0
   AND   ddips.avg_fragmentation_in_percent BETWEEN @lower_limit AND @upper_limit
   AND   ddips.page_count > @min_page_count
ORDER BY
   ddips.avg_fragmentation_in_percent DESC
   ,ddips.page_count DESC

$simonly$

Fri Jul 01, 2011 8:14 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.