SQLServerCentral / Discuss Content Posted by Grant Fritchey / Article Discussions / Article Discussions by Author / 2008 Index Fragmentation Maintenance / Latest Posts

RE: 2008 Index Fragmentation Maintenance

Grant Fritchey — Fri, 11 Jan 2013 08:16:35 GMT

Not just zero pages. It should have a page limit so that the defrag is across an index that's greater than 1 extent or the defrag just won't work. I haven't touched this since it was published 5 years ago. I may have to update it at some point.

RE: 2008 Index Fragmentation Maintenance

webtomte — Fri, 11 Jan 2013 07:39:46 GMT

Hi,Very nice script. I've been searching ages for this. I am not so comfortable with cursors, they tend to always crash for me.However,Do you really want to reorganize or rebuild indexes that has no pages?I my self only look for defragmentation when page_count are above 1000 for small databases and 500 for bigger ones.Your script is easily changed, as it is a very good script, to perform this.I have done thisAdded a variable ,@PctCount INT in DECLAREAdded av Column and a Value in #Frag table (pCount INT having pCount = s.page_count + Added AND s.page_count > 500 in Where clause)Added ,@PctCount to the CURSOR AND FETCHInserted IF @PctCount > 499 WITH a BEGIN and END-- CHOOSE DB TO CHECK FIRST (or master will be checked as default)SET NOCOUNT ONDECLARE @DBName NVARCHAR(255),@TableName NVARCHAR(255),@SchemaName NVARCHAR(255),@IndexName NVARCHAR(255),@PctFrag DECIMAL,@PctCount INTDECLARE @Defrag NVARCHAR(MAX)CREATE TABLE #Frag(DBName NVARCHAR(255),TableName NVARCHAR(255),SchemaName NVARCHAR(255),IndexName NVARCHAR(255),AvgFragment DECIMAL,pCount INT)EXEC sp_msforeachdb 'INSERT INTO #Frag (DBName,TableName,SchemaName,IndexName,AvgFragment,pCount) SELECT ''?'' AS DBName ,t.Name AS TableName ,sc.Name AS SchemaName ,i.name AS IndexName ,s.avg_fragmentation_in_percent ,s.page_count --,s.*FROM ?.sys.dm_db_index_physical_stats(DB_ID(''?''), NULL, NULL, NULL, ''Sampled'') AS s JOIN ?.sys.indexes i ON s.Object_Id = i.Object_id AND s.Index_id = i.Index_id JOIN ?.sys.tables t ON i.Object_id = t.Object_Id JOIN ?.sys.schemas sc ON t.schema_id = sc.SCHEMA_IDWHERE s.avg_fragmentation_in_percent > 10 AND s.page_count > 500AND t.TYPE = ''U''ORDER BY TableName,IndexName'SELECT * FROM #FragDECLARE cList CURSORFOR SELECT * FROM #FragOPEN cListFETCH NEXT FROM cListINTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag,@PctCount WHILE @@FETCH_STATUS = 0 BEGIN IF @PctCount > 499 BEGIN IF @PctFrag BETWEEN 10.0 AND 30.0 BEGIN SET @Defrag = N'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] REORGANIZE' EXEC sp_executesql @Defrag PRINT @Defrag PRINT '' END ELSE IF @PctFrag > 30.0 BEGIN SET @Defrag = N'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] REBUILD' EXEC sp_executesql @Defrag PRINT @Defrag PRINT '' END END FETCH NEXT FROM cList INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag,@PctCount END CLOSE cListDEALLOCATE cListDROP TABLE #Frag

RE: 2008 Index Fragmentation Maintenance

Grant Fritchey — Fri, 03 Sep 2010 13:32:22 GMT

[quote][b]dustin.mueller (9/3/2010)[/b][hr]Grant, just wanted to let you know that I have used this particular script (or a slight variation of it) at 3 different clients/employers now, and it has GREATLY improved the performance of their SQL servers. Thanks for you wonderful contributions to the SQL Server community![/quote]Thank you! Glad it's useful.

RE: 2008 Index Fragmentation Maintenance

Dustin_Mueller — Fri, 03 Sep 2010 10:47:10 GMT

Grant, just wanted to let you know that I have used this particular script (or a slight variation of it) at 3 different clients/employers now, and it has GREATLY improved the performance of their SQL servers. Thanks for you wonderful contributions to the SQL Server community!

RE: 2008 Index Fragmentation Maintenance

Jeffrey Williams 3188 — Wed, 10 Dec 2008 11:41:39 GMT

Jerry - that is definitely interesting. Did not know that it was introduced sometime after SP1.

RE: 2008 Index Fragmentation Maintenance

Jerry Hung — Wed, 10 Dec 2008 11:30:51 GMT

[quote][b]Jeffrey Williams (12/9/2008)[/b][hr][quote][b]Jerry Hung (12/9/2008)[/b][hr]It will work for SQL 2005 as well if we change[code]Select db_name() ,object_name(s.object_id) As ObjectName [b] --,object_schema_name(s.object_id) As SchemaName -- 2008 ,object_name(s.object_id) As SchemaName -- 2005[/b] ,i.Name As IndexName ,s.avg_fragmentation_in_percent[/code][/quote]Jerry - object_schema_name is available in SQL Server 2005 and above. Not sure why you are repeating the object_name as the schema_name for the object in the above.[/quote]Interesting[code]Select db_name() ,object_name(s.object_id) As ObjectName ,object_schema_name(s.object_id) As SchemaName -- 2008 ,object_name(s.object_id) As SchemaName -- 2005 ,i.Name As IndexName ,s.avg_fragmentation_in_percent From sys.dm_db_index_physical_stats(db_id(), Null, Null, Null, 'Sampled') s Join sys.indexes i On i.object_id = s.object_id And i.index_id = s.index_id Where i.index_id > 0 And i.index_id < 255 And s.avg_fragmentation_in_percent > 20[/code]On 2005 9.0.2047 (SP1), 1399 (RTM)'object_schema_name' is not a recognized built-in function name.2005 9.0.3042 (SP2) supports object_schema_nameNote, above code doesn't run in compatibility 8.0 mode either clearly'object_schema_name' is not a recognized built-in function name.

RE: 2008 Index Fragmentation Maintenance

Alan Vogan — Tue, 09 Dec 2008 13:16:06 GMT

[quote][b]Jeffrey Williams (12/9/2008)[/b]You can check the column 'lob_data_space_id' in the table sys.tables. If there is a value there, then you have LOB data in the table.[/quote]Woohoo! That's what I was lookin' for! :w00t:I also use a combination of SQL 2005 provided maintenance tasks and custom scripts.Thanks for the tip!Alan

RE: 2008 Index Fragmentation Maintenance

Jeffrey Williams 3188 — Tue, 09 Dec 2008 12:28:49 GMT

[quote][b]Alan Vogan (12/9/2008)[/b][hr]Good morning,I'm running a similar script and yes, it is WAY better to write your own optimizations. How do you handle the exception when the index contains one of the following types?Msg 2725, Level 16, State 2, Line 1Online index operation cannot be performed for index 'TEST_PK' because the index contains column 'TESTCOL' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.The script still runs, but it's irritating to show up in the morning and see that the job has 'failed'.Alan[/quote]You can check the column 'lob_data_space_id' in the table sys.tables. If there is a value there, then you have LOB data in the table.

RE: 2008 Index Fragmentation Maintenance

Jeffrey Williams 3188 — Tue, 09 Dec 2008 12:23:44 GMT

[quote][b]Jerry Hung (12/9/2008)[/b][hr]It will work for SQL 2005 as well if we change[code]Select db_name() ,object_name(s.object_id) As ObjectName [b] --,object_schema_name(s.object_id) As SchemaName -- 2008 ,object_name(s.object_id) As SchemaName -- 2005[/b] ,i.Name As IndexName ,s.avg_fragmentation_in_percent[/code][/quote]Jerry - object_schema_name is available in SQL Server 2005 and above. Not sure why you are repeating the object_name as the schema_name for the object in the above.

RE: 2008 Index Fragmentation Maintenance

Jeffrey Williams 3188 — Tue, 09 Dec 2008 12:21:45 GMT

[quote][b]Grumpy DBA (12/9/2008)[/b][hr]You have more control over what actually happens to the tables, indexes, etc. when you write your own maintenance jobs versus using SQL Server's Maintenance Plans. I have my own jobs for creating compressed full and t-log backups, monitoring database growth, index defrag/rebuild based on level of fragmentation, etc. Maintenance Plans are a better alternative than no DB maintenance for a novice DBA.[/quote]This is interesting, because I use a combination of my own procedures/code and the maintenance plans. I do this because the maintenance plans are very good at managing the steps a process needs to do, but the plug-ins that are provided are not very good.So, instead of using the Check Database Integrity Task - I will use the Execute SQL Task and put in that task: DBCC CHECKDB(mydb) WITH PHYSICAL_ONLY;Which, of course is not an option that is available with the plug-in.

RE: 2008 Index Fragmentation Maintenance

Grant Fritchey — Tue, 09 Dec 2008 10:51:32 GMT

That's great. I hadn't even tried it in 2005. Thanks for posting.

RE: 2008 Index Fragmentation Maintenance

Jerry Hung — Tue, 09 Dec 2008 10:23:25 GMT

It will work for SQL 2005 as well if we change[code]Select db_name() ,object_name(s.object_id) As ObjectName [b] --,object_schema_name(s.object_id) As SchemaName -- 2008 ,object_name(s.object_id) As SchemaName -- 2005[/b] ,i.Name As IndexName ,s.avg_fragmentation_in_percent[/code]

RE: 2008 Index Fragmentation Maintenance

Alan Vogan — Tue, 09 Dec 2008 09:35:43 GMT

Good morning,I'm running a similar script and yes, it is WAY better to write your own optimizations. How do you handle the exception when the index contains one of the following types?Msg 2725, Level 16, State 2, Line 1Online index operation cannot be performed for index 'TEST_PK' because the index contains column 'TESTCOL' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.The script still runs, but it's irritating to show up in the morning and see that the job has 'failed'.Alan

RE: 2008 Index Fragmentation Maintenance

Grant Fritchey — Tue, 09 Dec 2008 07:57:30 GMT

Total agreement.

RE: 2008 Index Fragmentation Maintenance

Grumpy DBA — Tue, 09 Dec 2008 07:53:31 GMT

You have more control over what actually happens to the tables, indexes, etc. when you write your own maintenance jobs versus using SQL Server's Maintenance Plans. I have my own jobs for creating compressed full and t-log backups, monitoring database growth, index defrag/rebuild based on level of fragmentation, etc. Maintenance Plans are a better alternative than no DB maintenance for a novice DBA.

RE: 2008 Index Fragmentation Maintenance

Grant Fritchey — Tue, 09 Dec 2008 07:51:20 GMT

Yes, I want direct control over what is run and how it is run within my system. Maintenance jobs are OK, but they can be problematic. I have a lot more ability to fine tune this process.

RE: 2008 Index Fragmentation Maintenance

Jeffrey Williams 3188 — Mon, 10 Nov 2008 14:35:03 GMT

No problem - if you look at my query, you will see that I forgot to include the index name (oops).The modified query is:[code]EXEC sp_msforeachdb 'Use ?;INSERT INTO #Frag (DBName,TableName,SchemaName,IndexName,AvgFragment)Select db_name() ,object_name(s.object_id) As ObjectName ,object_schema_name(s.object_id) As SchemaName ,i.Name As IndexName ,s.avg_fragmentation_in_percent From sys.dm_db_index_physical_stats(db_id(), Null, Null, Null, ''Sampled'') s Join sys.indexes i On i.object_id = s.object_id And i.index_id = s.index_id Where i.index_id > 0 And i.index_id < 255 And s.avg_fragmentation_in_percent > 20'[/code]

RE: 2008 Index Fragmentation Maintenance

Grant Fritchey — Mon, 10 Nov 2008 06:22:12 GMT

I like that clean up. Fewer joins and a simpler query. Nicely done. Thanks for making me look at the query again. I've still got a bit of the debugging info in there that needs to get cleaned out. Sloppy of me.

RE: 2008 Index Fragmentation Maintenance

Jeffrey Williams 3188 — Sun, 09 Nov 2008 10:18:31 GMT

This is good for a basic start. I would simplify this a little - for example, I would change the dynamic query to the following:[code]EXEC sp_msforeachdb 'Use ?;INSERT INTO #Frag (DBName,TableName,SchemaName,IndexName,AvgFragment)Select db_name() ,object_name(s.object_id) As ObjectName ,object_schema_name(s.object_id) As SchemaName ,s.avg_fragmentation_in_percent From sys.dm_db_index_physical_stats(db_id(), Null, Null, Null, ''Sampled'') s Join sys.indexes i On i.object_id = s.object_id And i.index_id = s.index_id Where i.index_id > 0 And i.index_id < 255 And s.avg_fragmentation_in_percent > 20'[/code]And move the ordering of the results to the cursor instead (guarantees the order will always be what I want).

2008 Index Fragmentation Maintenance

Grant Fritchey — Sat, 08 Nov 2008 13:55:26 GMT

Comments posted to this topic are about the item [B]2008 Index Fragmentation Maintenance[/B]