Grumpy DBA (12/9/2008)

---

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.

Jerry Hung (12/9/2008)

---

It will work for SQL 2005 as well if we change

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

Alan Vogan (12/9/2008)

---

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 1
Online 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

Jeffrey Williams (12/9/2008)
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.

Jeffrey Williams (12/9/2008)

---

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.

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

dustin.mueller (9/3/2010)

---

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!