locked
Use Maintenance task in SQL Server 2005 to Reindex selected indexes by their fragmentation percentage RRS feed

  • Question

  • Hi,

         I was wondering if there was a way to use the Maintenance Task in Sql Server 2005 to rebuild only those indexes that are over 90%, or is a script needed.

    Thanks

    Pam

     

     

    Tuesday, June 21, 2011 9:47 PM

Answers

  • I suggest you use Ola's script for this (there's nothing in the maint plans for this): http://ola.hallengren.com/
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by Pskubal Friday, June 24, 2011 2:44 PM
    Wednesday, June 22, 2011 8:16 AM
  • Check this link : - http://msdn.microsoft.com/en-us/library/ms188917.aspx  and look for section: - D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes

     

    Here they have mentioned about the script that could solve your purpose though you will need little editing.

     

     


    Harsh Chawla(MSFT) Personal Blog:-http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    • Marked as answer by Pskubal Friday, June 24, 2011 2:44 PM
    Wednesday, June 22, 2011 10:02 AM
  • Hey Pam,
    Yes, there is, but it is sort of a hack.
    Most SQL Server DBA's found (or were told about) the "sys.dm_db_index_physical_stats" section of SQL Server Books Online that includes a script to rebuilds or reorganizes indexes based on their level of fragmentation. You'll need to look at example "D", which is described as follows: "The following example automatically reorganizes or rebuilds all partitions in a database that have an average fragmentation over 10 percent."
    Once you get the batch, you can run it using the "Execute T-SQL Statement Task"
    Good luck!
    ~CA
    • Proposed as answer by Mr. Wharty Wednesday, June 22, 2011 3:42 AM
    • Marked as answer by Pskubal Friday, June 24, 2011 2:44 PM
    Wednesday, June 22, 2011 3:20 AM
  • Thanks to all your answers and ideas....appreciate the help!
    • Marked as answer by Pskubal Friday, June 24, 2011 2:44 PM
    Friday, June 24, 2011 2:43 PM

All replies

  • Hey Pam,
    Yes, there is, but it is sort of a hack.
    Most SQL Server DBA's found (or were told about) the "sys.dm_db_index_physical_stats" section of SQL Server Books Online that includes a script to rebuilds or reorganizes indexes based on their level of fragmentation. You'll need to look at example "D", which is described as follows: "The following example automatically reorganizes or rebuilds all partitions in a database that have an average fragmentation over 10 percent."
    Once you get the batch, you can run it using the "Execute T-SQL Statement Task"
    Good luck!
    ~CA
    • Proposed as answer by Mr. Wharty Wednesday, June 22, 2011 3:42 AM
    • Marked as answer by Pskubal Friday, June 24, 2011 2:44 PM
    Wednesday, June 22, 2011 3:20 AM
  • I suggest you use Ola's script for this (there's nothing in the maint plans for this): http://ola.hallengren.com/
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by Pskubal Friday, June 24, 2011 2:44 PM
    Wednesday, June 22, 2011 8:16 AM
  • Check this link : - http://msdn.microsoft.com/en-us/library/ms188917.aspx  and look for section: - D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes

     

    Here they have mentioned about the script that could solve your purpose though you will need little editing.

     

     


    Harsh Chawla(MSFT) Personal Blog:-http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    • Marked as answer by Pskubal Friday, June 24, 2011 2:44 PM
    Wednesday, June 22, 2011 10:02 AM
  • Thanks to all your answers and ideas....appreciate the help!
    • Marked as answer by Pskubal Friday, June 24, 2011 2:44 PM
    Friday, June 24, 2011 2:43 PM