UPDATE: This script has been significantly updated. Please view the latest version at http://sqlfool.com/2011/06/index-defrag-script-v4-1/.
So after much delay, here’s the latest and greatest version of my index defrag script.
A summary of the changes:
- Added support for centralized execution. Option to defrag indexes for a single database from another database, or for all non-system databases.
- Consolidated Enterprise and Standard versions of the script with new @editionCheck variable.
- Added parametrization for MaxDop restrictions during rebuilds; validates that the value does not exceed the actual number of processors on the server.
- Changed minimum fragmentation default value from 10 to 5 to match BOL recommendations.
- Limited defrags to objects with more than 8 pages.
- Added a debug option to give a little more insight into what’s happening and to assist with troubleshooting.
- Updated logic for handling partitions and LOBs.
And a couple of notes and explanations:
Don’t you know you can just pass NULL to sys.dm_db_index_physical_stats for the DatabaseID?
Yes, I realize you can do this. But I don’t want to defrag the system databases, i.e. tempdb, so I opted to handle it this way instead.
How long will this take?
It depends. I don’t necessarily recommend running it without specifying a database; at least, not unmonitored. You *can* do that, but it could take a while. For example, to run sys.dm_db_index_physical_stats for all databases and tables, totaling 2TB, took me 4.5 hours; that doesn’t even count the actual defrags.
Where should I put this?
It’s up to you. If you have a database for items like centralized maintenance or scratch tables, that may be a good place for it. If you prefer, you could also put this in each individual database and call it locally, too. I would not put this in the master or msdb databases.
This is pretty close to a complete rewrite, so please let me know if you encounter any bugs. And now… the code!
If Not Exists(Select [object_id] From sys.tables Where name = N'dba_indexDefragLog') Begin -- Drop Table dbo.dba_indexDefragLog Create Table dbo.dba_indexDefragLog ( indexDefrag_id int identity(1,1) Not Null , databaseID int Not Null , databaseName nvarchar(128) Not Null , objectID int Not Null , objectName nvarchar(128) Not Null , indexID int Not Null , indexName nvarchar(128) Not Null , partitionNumber smallint Not Null , fragmentation float Not Null , page_count int Not Null , dateTimeStart datetime Not Null , durationSeconds int Not Null Constraint PK_indexDefragLog Primary Key Clustered (indexDefrag_id) ) Print 'dba_indexDefragLog Table Created'; End If ObjectProperty(Object_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1 Begin Drop Procedure dbo.dba_indexDefrag_sp; Print 'Procedure dba_indexDefrag_sp dropped'; End; Go Create Procedure dbo.dba_indexDefrag_sp /* Declare Parameters */ @minFragmentation float = 5.0 /* in percent, will not defrag if fragmentation less than specified */ , @rebuildThreshold float = 30.0 /* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */ , @executeSQL bit = 1 /* 1 = execute; 0 = print command only */ , @database varchar(128) = Null /* Option to specify a database name; null will return all */ , @tableName varchar(4000) = Null -- databaseName.schema.tableName /* Option to specify a table name; null will return all */ , @onlineRebuild bit = 1 /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */ , @maxDopRestriction tinyint = Null /* Option to restrict the number of processors for the operation; only in Enterprise */ , @printCommands bit = 0 /* 1 = print commands; 0 = do not print commands */ , @printFragmentation bit = 0 /* 1 = print fragmentation prior to defrag; 0 = do not print */ , @defragDelay char(8) = '00:00:05' /* time to wait between defrag commands */ , @debugMode bit = 0 /* display some useful comments to help determine if/where issues occur */ As /********************************************************************************* Name: dba_indexDefrag_sp Author: Michelle Ufford, http://sqlfool.com Purpose: Defrags all indexes for one or more databases Notes: CAUTION: TRANSACTION LOG SIZE MUST BE MONITORED CLOSELY WHEN DEFRAGMENTING. @minFragmentation defaulted to 10%, will not defrag if fragmentation is less than that @rebuildThreshold defaulted to 30% as recommended by Microsoft in BOL; greater than 30% will result in rebuild instead @executeSQL 1 = execute the SQL generated by this proc; 0 = print command only @database Optional, specify specific database name to defrag; If not specified, all non-system databases will be defragged. @tableName Specify if you only want to defrag indexes for a specific table, format = databaseName.schema.tableName; if not specified, all tables will be defragged. @onlineRebuild 1 = online rebuild; 0 = offline rebuild @maxDopRestriction Option to specify a processor limit for index rebuilds @printCommands 1 = print commands to screen; 0 = do not print commands @printFragmentation 1 = print fragmentation to screen; 0 = do not print fragmentation @defragDelay time to wait between defrag commands; gives the server a little time to catch up @debugMode 1 = display debug comments; helps with troubleshooting 0 = do not display debug comments Called by: SQL Agent Job or DBA Date Initials Description ---------------------------------------------------------------------------- 2008-10-27 MFU Initial Release for public consumption 2008-11-17 MFU Added page-count to log table , added @printFragmentation option 2009-03-17 MFU Provided support for centralized execution, , consolidated Enterprise & Standard versions , added @debugMode, @maxDopRestriction , modified LOB and partition logic ********************************************************************************* Exec dbo.dba_indexDefrag_sp @executeSQL = 0 , @minFragmentation = 80 , @printCommands = 1 , @debugMode = 1 , @printFragmentation = 1 , @database = 'AdventureWorks' , @tableName = 'AdventureWorks.Sales.SalesOrderDetail'; *********************************************************************************/ Set NoCount On; Set XACT_Abort On; Set Quoted_Identifier On; Begin If @debugMode = 1 RaisError('Dusting off the spiderwebs and starting up...', 0, 42) With NoWait; /* Declare our variables */ Declare @objectID int , @databaseID int , @databaseName nvarchar(128) , @indexID int , @partitionCount bigint , @schemaName nvarchar(128) , @objectName nvarchar(128) , @indexName nvarchar(128) , @partitionNumber smallint , @partitions smallint , @fragmentation float , @pageCount int , @sqlCommand nvarchar(4000) , @rebuildCommand nvarchar(200) , @dateTimeStart datetime , @dateTimeEnd datetime , @containsLOB bit , @editionCheck bit , @debugMessage varchar(128) , @updateSQL nvarchar(4000) , @partitionSQL nvarchar(4000) , @partitionSQL_Param nvarchar(1000) , @LOB_SQL nvarchar(4000) , @LOB_SQL_Param nvarchar(1000); /* Create our temporary tables */ Create Table #indexDefragList ( databaseID int , databaseName nvarchar(128) , objectID int , indexID int , partitionNumber smallint , fragmentation float , page_count int , defragStatus bit , schemaName nvarchar(128) Null , objectName nvarchar(128) Null , indexName nvarchar(128) Null ); Create Table #databaseList ( databaseID int , databaseName varchar(128) ); Create Table #processor ( [index] int , Name varchar(128) , Internal_Value int , Character_Value int ); If @debugMode = 1 RaisError('Beginning validation...', 0, 42) With NoWait; /* Just a little validation... */ If @minFragmentation Not Between 0.00 And 100.0 Set @minFragmentation = 5.0; If @rebuildThreshold Not Between 0.00 And 100.0 Set @rebuildThreshold = 30.0; If @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]' Set @defragDelay = '00:00:05'; /* Make sure we're not exceeding the number of processors we have available */ Insert Into #processor Execute xp_msver 'ProcessorCount'; If @maxDopRestriction Is Not Null And @maxDopRestriction > (Select Internal_Value From #processor) Select @maxDopRestriction = Internal_Value From #processor; /* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */ If (Select ServerProperty('EditionID')) In (1804890536, 610778273, -2117995310) Set @editionCheck = 1 -- supports online rebuilds Else Set @editionCheck = 0; -- does not support online rebuilds If @debugMode = 1 RaisError('Grabbing a list of our databases...', 0, 42) With NoWait; /* Retrieve the list of databases to investigate */ Insert Into #databaseList Select database_id , name From sys.databases Where name = IsNull(@database, name) And database_id > 4 -- exclude system databases And [state] = 0; -- state must be ONLINE If @debugMode = 1 RaisError('Looping through our list of databases and checking for fragmentation...', 0, 42) With NoWait; /* Loop through our list of databases */ While (Select Count(*) From #databaseList) > 0 Begin Select Top 1 @databaseID = databaseID From #databaseList; Select @debugMessage = ' working on ' + DB_Name(@databaseID) + '...'; If @debugMode = 1 RaisError(@debugMessage, 0, 42) With NoWait; /* Determine which indexes to defrag using our user-defined parameters */ Insert Into #indexDefragList Select database_id As databaseID , QuoteName(DB_Name(database_id)) As 'databaseName' , [object_id] As objectID , index_id As indexID , partition_number As partitionNumber , avg_fragmentation_in_percent As fragmentation , page_count , 0 As 'defragStatus' /* 0 = unprocessed, 1 = processed */ , Null As 'schemaName' , Null As 'objectName' , Null As 'indexName' From sys.dm_db_index_physical_stats (@databaseID, Object_Id(@tableName), Null , Null, N'Limited') Where avg_fragmentation_in_percent >= @minFragmentation And index_id > 0 -- ignore heaps And page_count > 8 -- ignore objects with less than 1 extent Option (MaxDop 1); Delete From #databaseList Where databaseID = @databaseID; End Create Clustered Index CIX_temp_indexDefragList On #indexDefragList(databaseID, objectID, indexID, partitionNumber); Select @debugMessage = 'Looping through our list... there''s ' + Cast(Count(*) As varchar(10)) + ' indexes to defrag!' From #indexDefragList; If @debugMode = 1 RaisError(@debugMessage, 0, 42) With NoWait; /* Begin our loop for defragging */ While (Select Count(*) From #indexDefragList Where defragStatus = 0) > 0 Begin If @debugMode = 1 RaisError(' Picking an index to beat into shape...', 0, 42) With NoWait; /* Grab the most fragmented index first to defrag */ Select Top 1 @objectID = objectID , @indexID = indexID , @databaseID = databaseID , @databaseName = databaseName , @fragmentation = fragmentation , @partitionNumber = partitionNumber , @pageCount = page_count From #indexDefragList Where defragStatus = 0 Order By fragmentation Desc; If @debugMode = 1 RaisError(' Looking up the specifics for our index...', 0, 42) With NoWait; /* Look up index information */ Select @updateSQL = N'Update idl Set schemaName = QuoteName(s.name) , objectName = QuoteName(o.name) , indexName = QuoteName(i.name) From #indexDefragList As idl Inner Join ' + @databaseName + '.sys.objects As o On idl.objectID = o.object_id Inner Join ' + @databaseName + '.sys.indexes As i On o.object_id = i.object_id Inner Join ' + @databaseName + '.sys.schemas As s On o.schema_id = s.schema_id Where o.object_id = ' + Cast(@objectID As varchar(10)) + ' And i.index_id = ' + Cast(@indexID As varchar(10)) + ' And i.type > 0 And idl.databaseID = ' + Cast(@databaseID As varchar(10)); Execute sp_executeSQL @updateSQL; /* Grab our object names */ Select @objectName = objectName , @schemaName = schemaName , @indexName = indexName From #indexDefragList Where objectID = @objectID And indexID = @indexID And databaseID = @databaseID; If @debugMode = 1 RaisError(' Grabbing the partition count...', 0, 42) With NoWait; /* Determine if the index is partitioned */ Select @partitionSQL = 'Select @partitionCount_OUT = Count(*) From ' + @databaseName + '.sys.partitions Where object_id = ' + Cast(@objectID As varchar(10)) + ' And index_id = ' + Cast(@indexID As varchar(10)) + ';' , @partitionSQL_Param = '@partitionCount_OUT int OutPut'; Execute sp_executeSQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OutPut; If @debugMode = 1 RaisError(' Seeing if there''s any LOBs to be handled...', 0, 42) With NoWait; /* Determine if the table contains LOBs */ Select @LOB_SQL = ' Select Top 1 @containsLOB_OUT = column_id From ' + @databaseName + '.sys.columns With (NoLock) Where [object_id] = ' + Cast(@objectID As varchar(10)) + ' And (system_type_id In (34, 35, 99) Or max_length = -1);' /* system_type_id --> 34 = image, 35 = text, 99 = ntext max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */ , @LOB_SQL_Param = '@containsLOB_OUT int OutPut'; Execute sp_executeSQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OutPut; If @debugMode = 1 RaisError(' Building our SQL statements...', 0, 42) With NoWait; /* If there's not a lot of fragmentation, or if we have a LOB, we should reorganize */ If @fragmentation < @rebuildThreshold Or @containsLOB = 1 Or @partitionCount > 1 Begin Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + N' ReOrganize'; /* If our index is partitioned, we should always reorganize */ If @partitionCount > 1 Set @sqlCommand = @sqlCommand + N' Partition = ' + Cast(@partitionNumber As nvarchar(10)); End; /* If the index is heavily fragmented and doesn't contain any partitions or LOB's, rebuild it */ If @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1 Begin /* Set online rebuild options; requires Enterprise Edition */ If @onlineRebuild = 1 And @editionCheck = 1 Set @rebuildCommand = N' Rebuild With (Online = On'; Else Set @rebuildCommand = N' Rebuild With (Online = Off'; /* Set processor restriction options; requires Enterprise Edition */ If @maxDopRestriction Is Not Null And @editionCheck = 1 Set @rebuildCommand = @rebuildCommand + N', MaxDop = ' + Cast(@maxDopRestriction As varchar(2)) + N')'; Else Set @rebuildCommand = @rebuildCommand + N')'; Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + @rebuildCommand; End; /* Are we executing the SQL? If so, do it */ If @executeSQL = 1 Begin If @debugMode = 1 RaisError(' Executing SQL statements...', 0, 42) With NoWait; /* Grab the time for logging purposes */ Set @dateTimeStart = GetDate(); Execute sp_executeSQL @sqlCommand; Set @dateTimeEnd = GetDate(); /* Log our actions */ Insert Into dbo.dba_indexDefragLog ( databaseID , databaseName , objectID , objectName , indexID , indexName , partitionNumber , fragmentation , page_count , dateTimeStart , durationSeconds ) Select @databaseID , @databaseName , @objectID , @objectName , @indexID , @indexName , @partitionNumber , @fragmentation , @pageCount , @dateTimeStart , DateDiff(second, @dateTimeStart, @dateTimeEnd); /* Just a little breather for the server */ WaitFor Delay @defragDelay; /* Print if specified to do so */ If @printCommands = 1 Print N'Executed: ' + @sqlCommand; End Else /* Looks like we're not executing, just printing the commands */ Begin If @debugMode = 1 RaisError(' Printing SQL statements...', 0, 42) With NoWait; If @printCommands = 1 Print IsNull(@sqlCommand, 'error!'); End If @debugMode = 1 RaisError(' Updating our index defrag status...', 0, 42) With NoWait; /* Update our index defrag list so we know we've finished with that index */ Update #indexDefragList Set defragStatus = 1 Where databaseID = @databaseID And objectID = @objectID And indexID = @indexID And partitionNumber = @partitionNumber; End /* Do we want to output our fragmentation results? */ If @printFragmentation = 1 Begin If @debugMode = 1 RaisError(' Displaying fragmentation results...', 0, 42) With NoWait; Select databaseID , databaseName , objectID , objectName , indexID , indexName , fragmentation , page_count From #indexDefragList; End; /* When everything is said and done, make sure to get rid of our temp table */ Drop Table #indexDefragList; Drop Table #databaseList; Drop Table #processor; If @debugMode = 1 RaisError('DONE! Thank you for taking care of your indexes! :)', 0, 42) With NoWait; Set NoCount Off; Return 0 End Go |
Thanks to my beta testers, @scoinva, @davidmtate, @jdanton, and @SuperCoolMoss!
Special thanks to SCM for keeping on me to finish this.
Happy Defragging!
Michelle
Source: http://sqlfool.com/2009/03/automated-index-defrag-script/
As always your code is excellent Michelle!
One thing I played around with was changing the @database var so I could pass a comma separated list of DB names. My times to defrag are all over for groups of db’s so it makes it easier over making multiple calls on the proc. Which only means I’m being lazy 😉
Michelle,
I’ve been experimenting with your script, and so far, I like it a lot.
I notice that you look to see if the table has any LOB columns, and if so, you use a ReOrganize instead of a ReBuild. But it’s my understanding that it’s not _table_ that’s of concern, the question is if the _index_ contains any LOB columns. So, your approach is issuing ReOrganize when it could be using ReBuild. (Assuming that I’m not misunderstanding SQL Server or your script)
Taking a look at another index maintenance tool from Lara Rubbelke here:
http://blogs.digineer.com/blogs/larar/archive/2007/07/30/smart-index-defragmentation-for-an-online-world.aspx
gave me some ideas.
I added an indexType VARCHAR(18) not NULL to the #indexDefragList, which I filled via the index_type_desc column of the sys.dm_db_index_physical_stats.
Then, each time through the loop, I fill a new variable: @indexType VARCHAR(18) with the indexType column from #indexDefragList
Finally, I changed the code below “Determine if the table contains LOBs” to the following:
IF @indexType = ‘CLUSTERED INDEX’
BEGIN
SELECT @LOB_SQL = ‘ Select @containsLOB_OUT =
CASE
WHEN EXISTS(SELECT * FROM ‘ + @databaseName + ‘.SYS.COLUMNS c
WHERE c.OBJECT_ID = ‘ + CAST(@objectID AS VARCHAR(10)) + ‘
AND (user_type_id in (34, 35, 99, 241)
OR (user_type_id in (165, 167, 231) AND max_length = -1)))
THEN 1
ELSE 0
END’
, @LOB_SQL_Param = ‘@containsLOB_OUT int OutPut’;
END
IF @indexType = ‘NONCLUSTERED INDEX’
BEGIN
SELECT @LOB_SQL = ‘ Select @containsLOB_OUT =
CASE
WHEN EXISTS(SELECT * FROM ‘ + @databaseName + ‘.SYS.index_columns ic
INNER JOIN ‘ + @databasename + ‘.sys.Columns c
ON ic.Column_ID = c.column_id
AND ic.Object_ID = c.Object_ID
WHERE ic.Object_ID = ‘ + CAST(@objectID AS VARCHAR(10)) + ‘
AND ic.Index_ID = ‘ + CAST(@indexID AS VARCHAR(10)) + ‘
AND (user_type_id in (34, 35, 99, 241)
OR (user_type_id in (165, 167, 231) AND max_length = -1)))
THEN 1
ELSE 0
END’
, @LOB_SQL_Param = ‘@containsLOB_OUT int OutPut’;
END
EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT;
(I note that Laura is using slightly different methods than you for determining lob columns – I don’t know which is better)
These changes seem to be working well for me, but I haven’t given them too much testing. Hopefully they will be useful to someone.
Pingback: Database Fragmentation with Visual Defrag « Tipsy little box
Pingback: Confluence: Valdis Pukis
Thanks, Wesley! That’s a great point. I don’t work with LOB’s much in my production data, so I haven’t spent too much time on it in the defrag script. I’ll try to enhance the LOB functionality in the next release.
Thanks, Ted! That’s another great idea, I’ll add it on the list of things to include in the future. 🙂
Pingback: SQL SERVER - Automated Index Defragmentation Script « Journey to SQL Authority with Pinal Dave
Just the scrpit which i am looking for…thank you very much….
Thanks — Vijaya Kadiyala
http://www.dotnetvj.com
Pingback: gOODiDEA.NET
Pingback: Aaron Bertrand
Hi, thank you again for the excellent script. This has proved very useful indeed in a number of our environments.
I have come across a very minor bug and hope you do not mind me bringing it to your attention on this post however, I could not locate alternative contact details.
Within the loop for defragging (Titled: /* Begin our loop for defragging */), the variable @containsLOB needs to be initialised or cleared with each pass through the loop, otherwise a prior value can be persisted to a subsequent loop.
Hope my explanation makes sense.
Hi John! Do I mind if you point out a bug? Absolutely not! This is great feedback and will only improve my script. I don’t work with LOB’s much so I didn’t test that part of the script very well, obviously. 🙂
I’ve added it to the list of revisions for my next release, which I’ll hopefully get to in the next couple of weeks. Thank you for bringing it to my attention!
You’re welcome. Looking forward to the next release!
I have one minor complaint with the script (after the obligatory praise, of course). If you set the @printCommands bit on, it prints the command after executing it. This should be before the command executes and here’s why. If an error occurs during the operation of the script (as it did with me – related to one of my triggers), you cannot see where the error is occurring.
@Jason Good point! I’ll take a look at making that change in my next revision.
Great script.
Idea, how about adding an option to set the database into ‘Simple’ recovery mode before defragging/rebuilding indexes, and then set it back to ‘Full’, or is that bad practice?
@Jon I’m not an expert on recovery, but there’s several reasons why I wouldn’t want to make that an option. The biggest reason is, index defrag operations could take hours to complete, and you wouldn’t necessary want to be without point-in-time recovery for that long. Also, I believe it could cause several issues, such as breaking replication or causing errors if a backup t-log job tries to run. So yes, in general, I believe it’s bad practice.
Thanks for the suggestion and for reading my blog. 🙂
follows the error message that appeared when the script ran. What procedure should I take.
thanks.
dba_indexDefragLog Table Created
Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 226
Incorrect syntax near ‘OBJECT_ID’.
This worked great for me at very short notice on a production system. So if ever you decide to doubt your efforts, I’d encourage you to continue! Thanks for your contribution.
Hi,
I think this script is great, but it could be even better if u add a maximum quantity of index to rebuild / reorganize every time that the script get call, and another important add would be add page count range management , then for automated tasks we as DBA can create different maintenance plans based on peak times usage of the database.
I’m actually adding that to the script, and it would be great if I can send it back to you and you can make it available or merge it with the actual changes you are actually doing. If you see is worth. 🙂
@Celio what version of SQL Server are you running? What patch are you on?
Thanks, @Paul! 🙂
@Kenneth I’ll be releasing a new version in a couple of days, so it’s probably too late to make it into that version. But if you want to send me an e-mail at michelle @ sqlfool dot com, I may incorporate it into the next version. Thank you! 🙂
@Michelle I am running in SQL Server 2005 Express Edition. V.9.00.3042.00
Pingback: Aaron Bertrand
Michelle Ufford,
Does the new version of this script is released??
Hi Michelle,
We have share point databases in SQL Server 2005 Enterprise edition in one server and BizTalk Application databases in another Server. Does this script validated to index defrag for Share point databases and BizTalk databases??
Thank You
Looks fantastic!
Anyone get the following error when trying to run it on SQL2005 Ent.?
Procedure dba_indexDefrag_sp dropped
Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table ‘SP_EXECUTESQL’. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.
Chris
I am getting the same error on a SQL 2008 server :
Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table ‘SP_EXECUTESQL’. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.
Any help?
@Chris and @Tim – I’m not sure what’s causing that error. My Googling returned no results, although I did see a couple of forum discussions where the advice was “I’m not sure what’s causing that but you can safely ignore the error message.” Does the proc still run for you even after getting the error message? Also, please try upgrading to the latest version and seeing if that resolves the issue.
You can find the latest version here:
http://sqlfool.com/2009/06/index-defrag-script-v30/
Michelle,
I actually replaced ‘SP_EXECUTESQL’ with ‘sp_executesql’ and it worked. Hope this helps.
Something to check if you are getting odd errors on sp_ExecuteSQL:
If you are getting errors about sp_ExecuteSQL then check your DB’s Collation settings and you’ll most likley find that it’s a case sensative selection and that in turn can cause probelms if just one of the characters in your SQL code is not the correct case.
Just something to check that I found. The Sample AdventureWorks DB from Microsoft is set to a case senative collation
Awesome script! I’ve been chasing index maintenance manually for a good while, planning to write something just like this… but when you’re the operational, development and support DBA wrapped in one, things like that seem to slip down the to-do list, and you end up wasting sooooo much time chasing the issues later. So it’s really good to find a script like this…well done!
Thank you, Tim! I appreciate the kind feedback. 🙂
Nice work, Thank you for the script.
Found an error check that is needed… If you have the script set to reindex all, which is the default, and you have a read-only DB, you get a failure…
“Failed to update database “a” because the database is read-only.”
I’m going to add the functionality to skip any read-only DBs.
Still a great script despite the fact that it runs longer than a Maintence Plan that does the same things.
To get around the read-only DB problem, in the “Retrieve the list of databases to investigate” section, I simply added a line below
And [STATE] = 0 — state must be ONLINE
AND [is_read_only] = 0; — VC: must NOT be read-only
Of course, the semicolon has to be moved to the last line of the query, which is now the read-only check. Tested and working fine.
For starters, I am no DBA and I am working on rebuilding the indexes.
I made use of the amazing TSQL script from msdn to alter index based onthe fragmente percent returned by dm_db_index_physical_stats and if the fragment percent is more than 30 then do a REBUILD or do a REORGANISE.
What I found out was, in the first iteration, there were 87 records which needed defrag.I ran the script and all the 87 indexes (clustered & nonclustered) were rebuilt or reindexed. When I got the stats from dm_db_index_physical_stats , there were still 27 records which needed defrag and all of theses were NON CLUSTERED Indexes. All the Clustered indexes were fixed.
No matter how many times I run the script to defrag these records, I still have the same indexes to be defraged and most of them with the same fragmentation %. Nothing seems to change after this.
Note: I did not perform any inserts/ updates/ deletes to the tables during these iterations. Still the Rebuild/reorganise did not result in any change.
More information: Using SQL 2008
Script as available in msdn http://msdn.microsoft.com/en-us/library/ms188917.aspx
Could you please explain why these 27 records of non clustered indexes are not being changed/ modified ?
Any help on this would be highly appreciated.
Nod
Pingback: SQL Server 2005 Smart Defrag Page Count | Kingston Lee
Noddy heres a good link to cluster explanation. Check your settings and version of SQL as well. I manage some fairly large DB’s and this script is about o make me a VERY HAPPY Man.
Thank you. Very useful script for large databases. I fixed a few minor issues reported above. Made some additional modification and make is a permanent member of my sql_script library.
By rebuilding/reorg-ing index with this script you can cut time significantly to avoid unnecessary index rebuilds.
cheers
Hi Michelle,
I started my rebuild index job and its running for hours and i could see the job is hung, can i kill it ,if so will the indexes gets dropped or not, please let me know.
Regards
Sunil.B
Hi Sunil,
The job is probably not hung. My guess if you either have a lot of indexes that need to be defragged, or some are really large. Most likely a combination of both. Regardless, if you need to kill the job, rest assured that nothing needs to be rolled back and that the indexes will *not* be dropped.
Hope that helps.
Michelle
Hi, I apologize for my English (I’m Italian). I want to know how to use the script and the extension.
Can I use it on a database maintained by iAnywhere?
Thanks
Hello,
Have you this script for sql server 2000?
Pingback: How do you want your index? | Verity
GETTING ERROR WHEN CREATING THE PROCEDURE
Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 464
Incorrect syntax near ‘OBJECT_ID’.
Thanks very much for this – I’ve used it and trimmed it to my needs (removed the log tables etc).
Amazing piece of work though. Nice one.
Hey Michelle – I seem to remember you once had some logic in this that had the procedure quit if it went outside a maintenance window (e.g. if it ran past 5 AM server time). Is my memory playing tricks in my old age?
PS. How’s the baby?
Hi ,
Great Script…
I have few query on this script…
In this script ,
its checking if @allowPageLocks =0 (count for index which have Allow_Page_Locks=0) and if object have 1 or more LOB Objects then although index fragmentation value exceeds the rebuidthreshold value , it dont make rebuid , but make reorganization on it.
I am a little confused on it, and need to know , what is reason that we should do rebuid on index if we found ,1 or more LOB objects and Allow_Page_Locks=0
The am getting the same error as reported by IRFAN and CELIO. I am running the script on 2008R2 standard. Can anyone offer a solution?
I am getting the following results after running the script:
Procedure dba_indexDefrag_sp dropped
Cannot add rows to sysdepends for the current object because it depends on the missing object ‘sp_executeSQL’. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object ‘sp_executeSQL’. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object ‘sp_executeSQL’. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object ‘sp_executeSQL’. The object will still be created.
Great article! Thank you for the script. Worked like a charm!
if the database has online read-only file groups containing older partitions for partitioned tables, would this script work. I understand that if a filegroup is marked as read-only the index should not be fragmented anyways but if it is, I am guessing this script would blow up correct?
The new functionality in SQL Server 2012 named AlwaysOn does not mark the read only replica as read only in the sys.databases.is_read_only instead it is a new DMV sys.dm_hadr_availability_group_states in the column primary_replica. What I do is compare that to @@servername and then react based on that. The function named sys.fn_hadr_backup_is_preferred_replica is buggy and incorrectly skips stuff.
https://connect.microsoft.com/SQLServer/feedback/details/781440/backups-are-being-skipped-by-sys-fn-hadr-backup-is-preferred-replica-alwayson-available-groups#tabs
Pingback: Index Maintenance | Zidek
Pingback: How often should the indexes be rebuilt in our SQL Server database? - QuestionFocus