2008 Index Fragmentation Maintenance

  • Comments posted to this topic are about the item 2008 Index Fragmentation Maintenance

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • This is good for a basic start. I would simplify this a little - for example, I would change the dynamic query to the following:

    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'

    And move the ordering of the results to the cursor instead (guarantees the order will always be what I want).

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

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

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • No problem - if you look at my query, you will see that I forgot to include the index name (oops).

    The modified query is:

    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'

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

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

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

  • Total agreement.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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

  • 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

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • That's great. I hadn't even tried it in 2005. Thanks for posting.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

    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.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • 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

    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.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • 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

    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
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

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

    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

  • Jeffrey Williams (12/9/2008)


    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

    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.

    Interesting

    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

    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_name

    Note, above code doesn't run in compatibility 8.0 mode either clearly

    'object_schema_name' is not a recognized built-in function name.

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply