Defragmenting Indexes in SQL Server 2005
UPDATE: This stored procedure has been updated.
I have modified isp_ALTER_INDEX, which is the stored procedure that I use to defragment indexes in SQL Server 2005. The changes include one bug fix and one feature request.
The bug fix was simply to add "SET QUOTED_IDENTIFIER ON" to the script. You'd only see the bug in special circumstances, such as if you were using indexed views.
The feature request was to add the ability to log the fragmentation data. The logged data could be used to determine if you want to rearrange the index or perhaps add/remove columns to reduce fragmentation. The logged data is stored in a table called DefragmentIndexes. If you don't want to log the data, then you'll still need to create the table if you want to use this new version, otherwise the CREATE PROC statement would fail due to a missing object. To configure the stored procedure to log the data, simply pass 1 to the new input parameter, @logHistory. The default for this new input parameter is 0, so you do not need to modify your job if you don't care about this new feature.
You can download the new version of the stored procedure and the required table here. I put them in the same script to make it easier to deploy the new version.
Let me know if you run into any issues with it.
Legacy Comments
|
vadym
2009-03-31 |
re: Defragmenting Indexes in SQL Server 2005 Even some of my Maintenance plans fail with error about SET QUOTED_IDENTIFIER wrong value. I can't understand what index defragmentation has to do with qouted identifiers. .. Another problem with maint plans is inability to set fragmentation threshold for defragmentation and as the result incredible log file growth |
|
Sachin
2009-04-07 |
re: Defragmenting Indexes in SQL Server 2005 I did notice a bug in the code. The sproc execution fails when it tries to rebuild a Non-Clustered index online with an included column of type varchar(MAX). The fix I think would be to detect included columns of type Nvarchar(MAX) and flag it for an offline rebuild. |
|
Tony Fountain
2009-04-16 |
re: Defragmenting Indexes in SQL Server 2005 Tara, I'm replacing our old scripts written about 10 years ago with yours. Well, I'm merging yours with ours :). Either way, would you find it beneficial to add code that sets the recovery model to BULK LOGGED? Ours currently does this and sets it back when done when the initial recovery model is FULL. |
|
Jim
2009-04-17 |
re: Defragmenting Indexes in SQL Server 2005 Hi Tara, I'm experiencing database performance issues after restoring a SQL 2005 9.0SP3 database to another server. The db compatibility level must be 80 on this db. When I run your script, I receive the following error: --- Msg 102, Level 15, State 1, Procedure isp_ALTER_INDEX, Line 81 Incorrect syntax near '('. --- Line 81 is: --- IF @statsMode NOT IN ('LIMITED', 'SAMPLED', 'DETAILED') --- Thanks! Jim |
|
Peso
2009-04-19 |
re: Defragmenting Indexes in SQL Server 2005 Great change using QUOTENAME for objects. Now I can use the script when I visit a new customer as a consultant :-) But I have come across a problem with all the IFs. Some of my clients use case sensitive collation, so a small change would be great, see below IF UPPER(@defragType) |
|
Tony Fountain
2009-04-24 |
re: Defragmenting Indexes in SQL Server 2005 I am running this now on our servers (total of 7 servers, 107 databases) and I am finding a few situations and would like to toss some ideas off you. First, I am finding the call to sys.dm_db_index_physical_stats is extremely resource intensive. I am considering calling it index by index (or table by table). My initial tests show that calling sys.dm_db_index_physical_stats on an individual index (or even just a table) runs faster for each call. This might increase the total runtime of the job but will reduce contention since each call is much shorter. I'm talking 4-5 minutes for all objects in the database to 2-3 seconds for an individual index or table. Have you considered this before and if so, what were your considerations for not going in that direction? The second thing is I modified the logging to record the start time and end time of each index rebuild. This way I can gain insight into the time frame each index rebuild takes. I also might add some logic to calculate space needed for a rebuild as I've already generated an out of log space condition on one of our very large indexes. In conjunction with this, I might modify the job to include sorting in tempdb instead. The main thing I was hoping to get your opinion on is the DMV view item. The rest is just informational and if you want my final code when completed I'm more than happy to shot it over to you. |