2008 Index Fragmentation Maintenance

  • Grant Fritchey

    SSC Guru

    Points: 396617

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeffrey Williams

    SSC Guru

    Points: 88593

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Grant Fritchey

    SSC Guru

    Points: 396617

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeffrey Williams

    SSC Guru

    Points: 88593

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Grant Fritchey

    SSC Guru

    Points: 396617

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grumpy DBA

    SSChampion

    Points: 11669

    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.

  • Grant Fritchey

    SSC Guru

    Points: 396617

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Alan Vogan

    SSCrazy

    Points: 2483

    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

  • Jerry Hung

    SSChampion

    Points: 12908

    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

    SQLServerNewbie MCITP: Database Administrator SQL Server 2005
  • Grant Fritchey

    SSC Guru

    Points: 396617

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeffrey Williams

    SSC Guru

    Points: 88593

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Jeffrey Williams

    SSC Guru

    Points: 88593

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Jeffrey Williams

    SSC Guru

    Points: 88593

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Alan Vogan

    SSCrazy

    Points: 2483

    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

  • Jerry Hung

    SSChampion

    Points: 12908

    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.

    SQLServerNewbie MCITP: Database Administrator SQL Server 2005

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

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