none
Identifying index fragmentation for very large database

    Question

  • We have a very large database (3TB). Running index_physical_stats takes too long to show up tables that are fragmented >30%. After running for almost 8hrs we got about 20K tables & then we had to cancel the query due to start of business hours. I know the object id for last table that was scanned for fragmentation. Is it anyway possible to start index_physical_stats from where it was left. I dont want to start frag report for whole database again.
    Tuesday, February 14, 2012 12:44 AM

All replies

  • Hi,

    I don't know of a way to re-start index_physical_stats from where it stopped but I would suggest that instead of taking a whole or nothing approach you might be better served by checking the very large indexes on alternate days and the small indexes in a batch. Store the information in a IndexMaint table in another DBA database on the instance if possible then use this information to plan an index maintenance strategy.

    You might for example find that certain large indexes are always > 30% fragmented. If so why bother doing the check. Use the time you would use on the check to use that time to rebuild. Indexes with LOB/spatial columns can't be re-built online therefore you will need to plan downtime or stick to doing re-organisations on those tables. A reorg is always online.


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Matt Morrell Friday, April 13, 2012 1:34 AM
    Tuesday, February 14, 2012 3:21 AM
  • Hello,

    In addition to what Sean has already said, sys.dm_db_index_physical_stats can be run on a table or view the indexes are on. This means you can run it against a single large table during your downtime and possibly hit some of the smaller tables later. Of course if you have dictionary tables that rarely change I highly doubt this would need to be done as often. I'm also assuming you ran it with the limited mode to just get an initial idea before using the detailed mode to get more information if the situation warranted.

    3TB is pretty big to let loose with this as it will at best read intermediate levels and at worst every single page in the database... that's a good amount of strain on the IO system. I would most definitely come up with some type of strategy as a single pass is just not going to work for you.

    -Sean

    Tuesday, February 14, 2012 1:14 PM
    Answerer
  • Thanks Sean M & Sean G for the inputs. What I get is there is no way to go forward from where the last scan was left for frag report.
    Tuesday, February 14, 2012 7:04 PM
  • You should also understand that as Sean G and I are trying to point out you should not run your index checks or rebuilds on the entire database in one batch. You need to develop a strategy specifically for this database and run your maintenance tasks in smaller more manageable batches that decrease the chance of your maintenance causing performance issues.

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, February 14, 2012 8:42 PM
  • Hello, 

     I have written a SQL Powershell script that identifies indexes on tables that have fragmentation greater than 30% and performs a Index Rebuild one by one. You can also control the number of indexes to process in single script run. By this approach, you can run this SQL powershell script as a recurring scheduled SQL Agent job in non-peak hours. You can take a look at http://sethusrinivasan.wordpress.com/2012/02/14/index-rebuild-on-large-database-sql-agent-powershell-job/ 

    Thanks

    Sethu Srinivasan[MSFT]

    SQL Server

    http://blogs.msdn.com/sqlagent

    Wednesday, February 15, 2012 2:01 AM
    Moderator
  • Sethu,

    That's a good start, but I don't believe he would want to do all tables and indexes in order or he'll have the same issue as he had before. It's definitely a great start to something he could pick apart and add in filters for different times or take table variables and only do the single table, index, etc.

    Basically what Sean Massey and I have already pointed out is that he needs to break down his maintenance into manageable chunks, so that the very large tables and be efficiently run through during non-peak or downtime and then the slight load from the smaller tables later.

    -Sean

    Wednesday, February 15, 2012 1:27 PM
    Answerer
  • Hello,

    Have a look to this script, I use it to rebuild indexes on big databases

    http://sql-javier-villegas.blogspot.com/2012/01/handling-indexes-fragmentation.html

    Regards


    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Thursday, February 16, 2012 12:34 PM
  • Thank Sean,

    As you stated, this script may not be complete. You could use it as a reference to build your own scripts that could filter / sort set of indexes that need to be rebuilt on your database(s)

    Thanks

    Sethu Srinivasan[MSFT]

    SQL Server

    Thursday, February 16, 2012 6:25 PM
    Moderator
  • running script that checks & rebuilds indexes that has frag >30% will run against the full database & it would spill over to business hours. When I check for large tables & indexes, it gives me almost 4K tables, now the task is creating scheduled jobs for these 4K tables for rebuild.

    Thursday, February 16, 2012 11:00 PM