Ramblings of a DBA

# 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

# 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.

# 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.

# 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

# 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)

# 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.