-1

I've been tasked with maintaining an old (SQL 2005; no, I can't upgrade it!) server which has 461,000 tables and 1.15M indexes. How do I go about analyzing all those indexes in bite-sized chunks?

My first thought is to create a list (stored in a table) of indices, having these attributes:

  • schema name
  • table name
  • index name
  • page count (null at first)
  • frag pct (null at first)
  • date rebuilt/reorganized (null at first)

From there, I would -- in as many tables as I can analyze each night -- query sys.dm_db_index_physical_stats and update the table. Eventually I'd have a list of all tables and their frag statistics. After that, I can defrag (reorg or rebuild as necessary) all the indexes which I deem require it.

Are there any better ways to do this?

Improve this question
1

1 Answer 1

Reset to default
1

I don't think you need to reinvent the wheel, this is already done by many SMEs before you thought this as a problem. Please check solution offered by Mr. Ola Hallengren(MVP). You just need to download them and define your parameters which is also explained at his website. This is fairly easy, straight forward and very much reliable.

For index maintenance you can check this link --> https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

And for details on other solutions check --> https://ola.hallengren.com/

You might face some compatibility issue since you are on SQL 2005 however it shouldn't be show-stopper, you should be able to find a work around fairly easily.

In case, you would like to stick to something native to SQL then you can define a maintenance plan yourself and define at what fragmentation level you would be doing reorg and what should be threshold for rebuild, updating stats can also be taken care from there. Just note that its not that much reliable and configurable as former suggested one.

Many DBAs worldwide use Ola Hallengren's script and there is no issue at all.

Since sizing seems an issue here, I would suggest to use below parameters:

MinNumberOfPages - You could set this value to exclude small objects. Set a size, in pages; indexes with fewer number of pages are skipped for index maintenance.

MaxNumberOfPages - Set a size, in pages; indexes with greater number of pages are skipped for index maintenance. set this value to exclude larger objects.

OnlyModifiedStatistics - Update statistics only if any rows have been modified since the most recent statistics update.

Indexes - One more parameter Indexes can be used to define wildcard index names say from a to g, in next run from h to m etc.

Since you haven't given details of resources at this server, I am not sure whether your server would be able handle them all

Improve this answer
4
  • But the system can't handle running it all at once. Otherwise, I'd just run it all myself with a much shorter script which does all the important bits that the Hallengren does and is much easier to read.
    – RonJohn
    Jul 9, 2020 at 5:46
  • Well, you didn't mention that - there are parameters you can use at Ola's script to split the maintenance job. First would be MinNumberOfPages - You could set this value to exclude small objects. Second would be MaxNumberOfPages - set this value to exclude larger objects. For stats - you can use parameter OnlyModifiedStatistics. One more parameter Indexes can be used to define wildcard index names say from a to g, in next run from h to m etc. Since you haven't given details of resources at this server, I am not sure whether your server would be able handle them all.
    – Learning_DBAdmin
    Jul 9, 2020 at 5:55
  • "Well, you didn't mention that". I thought I did when I wrote in as many tables as I can analyze each night.
    – RonJohn
    Jul 9, 2020 at 6:20
  • I think I need to retitle the question.
    – RonJohn
    Jul 9, 2020 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.