April 9, 2012 at 3:45 pm
We currently have 20 database instances with anywhere from 20 to 400 databases per instance. All databases have exactly the same schema. I am trying to come up with the best maintenance plan to keep the indexes defragmented.
My first thought is to run multiple jobs on each server and run parallel rebuild\reindex procs , maybe 5 at a time. So If I have 400 databases I would have 80 databases per job . Each database can vary in size anywhere from a few hundred megabytes to a few hundred gigabytes.
Do you think this is the right approach? Could I potentially run more than 5 at a time or do you think that would kill CPU?
We have a minimum 60gigs of memory on each machine.
April 9, 2012 at 4:12 pm
pamozer (4/9/2012)
We currently have 20 database instances with anywhere from 20 to 400 databases per instance. All databases have exactly the same schema. I am trying to come up with the best maintenance plan to keep the indexes defragmented.My first thought is to run multiple jobs on each server and run parallel rebuild\reindex procs , maybe 5 at a time. So If I have 400 databases I would have 80 databases per job . Each database can vary in size anywhere from a few hundred megabytes to a few hundred gigabytes.
Do you think this is the right approach? Could I potentially run more than 5 at a time or do you think that would kill CPU?
We have a minimum 60gigs of memory on each machine.
The only way to know is to test and see what happens.
Either way the key portion of it all is the reorg storedproc, be sure the process does not wastes time on indexes that do not require reorg, also be sure process does a REORG when reorg is needed and REBUILD when rebuilt is needed.
As a rule of thumbs, if target index:
< 100 pages in size... do nothing
< 5% Fragmented... do nothing
< 30% Fragmented... do a REORG
>= 30% Fragmented... do a REBUILD
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 9, 2012 at 4:15 pm
Ok. So you think I am headed in the right direction. Just wanted to make sure I wasn't thinking crazy.
thanks
April 9, 2012 at 4:35 pm
I really like the maintenance scripts over at:
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Very flexible, and easy to use.
With that topography it sounds as if many of your databases might be replicated from a central location.
If you're using snapshot you might want to make sure that the important indexes are distributed to all sites as they don't get replicated by default, you need to take special steps to replicate indexes.
One thing I'd also look out for is to make sure you have enough drive space for your tempdb to grow if you haven't been doing a lot of index maintenance in the past that's something easily overlooked.
April 9, 2012 at 4:41 pm
Thank you. We are using Ola's scripts. We do not use replication. The multiple databases are per customer databases with one shared database.
The one thing that Ola's script does is go through each database one by one to reindex/rebuild. I am trying to figure out how have it do multiple databases at the same time. By doing it serially it is taking 8 to 10 hours to reindex one server.
One thought I had was to create a table on each server that groups a certain number of databases together and then run the script multiple times on one server in different jobs.
April 10, 2012 at 1:51 am
Service broker might work here as well. You could queue up all the databases, and use an activation proc to call ola's script. Then you can adjust the number of concurrent workers easily. (Alter queue...)
April 10, 2012 at 9:22 am
I have not ever used service broker. I'll have to do some reading on that approach. Thanks for the input.
April 10, 2012 at 3:46 pm
pamozer (4/9/2012)
Thank you. We are using Ola's scripts. We do not use replication. The multiple databases are per customer databases with one shared database.The one thing that Ola's script does is go through each database one by one to reindex/rebuild. I am trying to figure out how have it do multiple databases at the same time. By doing it serially it is taking 8 to 10 hours to reindex one server.
One thought I had was to create a table on each server that groups a certain number of databases together and then run the script multiple times on one server in different jobs.
I use Ola's scripts as well. They accept a databases-parameter. Can you not schedule two jobs to run concurrently with mutually exclusive sets of databases to operate on?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 10, 2012 at 3:48 pm
That is the plan.
April 11, 2012 at 1:03 pm
PaulB-TheOneAndOnly (4/9/2012)
The only way to know is to test and see what happens.
Either way the key portion of it all is the reorg storedproc, be sure the process does not wastes time on indexes that do not require reorg, also be sure process does a REORG when reorg is needed and REBUILD when rebuilt is needed.
As a rule of thumbs, if target index:
< 100 pages in size... do nothing
< 5% Fragmented... do nothing
< 30% Fragmented... do a REORG
>= 30% Fragmented... do a REBUILD
Agreed on the testing; try not to do too many databases that use the same drive spindles at once. As far as CPU goes, parallel index maintenance of compressed indexes is very CPU intensive (I've seen it CPU bound on a 16 core box); but maintenance of uncompressed indexes don't seem bad.
Paul: Perhaps you can explain more of when, and why, you'd choose a reorg vs. an online rebuild vs. an offline rebuild. In some situations (Standard edition, some nondedicated 'slower time' and a too small dedicated maintenance window) reorganizing as much as possible seems to make sense. In others (any edition, a dedicated maintenance window hours longer than actually required once regular maintenance has started), I prefer to rebuild all the time. If the maintenance window's going to be there, use it to the best advantage possible, and rebuilds deliver superior end results.
*ETA: Only indexes in need of maintenance should be maintained. Don't maintain every index, determine which can benefit enough from maintenance and which don't need it. For the advanced course, adjust fillfactor so maintenance is required less often.
April 11, 2012 at 2:41 pm
I don't have to worry about compression. The plan is to run the reorg/rebuild 2 times a week on one server to start and see how that goes. I'm going to try running 5 at a time and see how that goes and then see if I can increase it after that.
April 11, 2012 at 3:03 pm
Nadrek (4/11/2012)
PaulB-TheOneAndOnly (4/9/2012)
The only way to know is to test and see what happens.
Either way the key portion of it all is the reorg storedproc, be sure the process does not wastes time on indexes that do not require reorg, also be sure process does a REORG when reorg is needed and REBUILD when rebuilt is needed.
As a rule of thumbs, if target index:
< 100 pages in size... do nothing
< 5% Fragmented... do nothing
< 30% Fragmented... do a REORG
>= 30% Fragmented... do a REBUILD
Agreed on the testing; try not to do too many databases that use the same drive spindles at once. As far as CPU goes, parallel index maintenance of compressed indexes is very CPU intensive (I've seen it CPU bound on a 16 core box); but maintenance of uncompressed indexes don't seem bad.
Paul: Perhaps you can explain more of when, and why, you'd choose a reorg vs. an online rebuild vs. an offline rebuild. In some situations (Standard edition, some nondedicated 'slower time' and a too small dedicated maintenance window) reorganizing as much as possible seems to make sense. In others (any edition, a dedicated maintenance window hours longer than actually required once regular maintenance has started), I prefer to rebuild all the time. If the maintenance window's going to be there, use it to the best advantage possible, and rebuilds deliver superior end results.
Okay, let me get this straight. You would rather rebuild all indexes on all tables during your maintenance window? How much space do you have for your t-log, or do you switch to bulk_logged recovery model before rebuilding?
Some of my environments have had databases with 50,000+ tables. I'm not rebuilding every index on every table. I hit those that need it.
April 11, 2012 at 3:10 pm
Lynn Pettis (4/11/2012)
Nadrek (4/11/2012)
PaulB-TheOneAndOnly (4/9/2012)
The only way to know is to test and see what happens.
Either way the key portion of it all is the reorg storedproc, be sure the process does not wastes time on indexes that do not require reorg, also be sure process does a REORG when reorg is needed and REBUILD when rebuilt is needed.
As a rule of thumbs, if target index:
< 100 pages in size... do nothing
< 5% Fragmented... do nothing
< 30% Fragmented... do a REORG
>= 30% Fragmented... do a REBUILD
Agreed on the testing; try not to do too many databases that use the same drive spindles at once. As far as CPU goes, parallel index maintenance of compressed indexes is very CPU intensive (I've seen it CPU bound on a 16 core box); but maintenance of uncompressed indexes don't seem bad.
Paul: Perhaps you can explain more of when, and why, you'd choose a reorg vs. an online rebuild vs. an offline rebuild. In some situations (Standard edition, some nondedicated 'slower time' and a too small dedicated maintenance window) reorganizing as much as possible seems to make sense. In others (any edition, a dedicated maintenance window hours longer than actually required once regular maintenance has started), I prefer to rebuild all the time. If the maintenance window's going to be there, use it to the best advantage possible, and rebuilds deliver superior end results.
Okay, let me get this straight. You would rather rebuild all indexes on all tables during your maintenance window? How much space do you have for your t-log, or do you switch to bulk_logged recovery model before rebuilding?
Some of my environments have had databases with 50,000+ tables. I'm not rebuilding every index on every table. I hit those that need it.
I am with Lynn. I prefer to just target the tables that need maintenance and leave the others be.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 11, 2012 at 3:15 pm
Lynn Pettis (4/11/2012)
Nadrek (4/11/2012)
PaulB-TheOneAndOnly (4/9/2012)
The only way to know is to test and see what happens.
Either way the key portion of it all is the reorg storedproc, be sure the process does not wastes time on indexes that do not require reorg, also be sure process does a REORG when reorg is needed and REBUILD when rebuilt is needed.
As a rule of thumbs, if target index:
< 100 pages in size... do nothing
< 5% Fragmented... do nothing
< 30% Fragmented... do a REORG
>= 30% Fragmented... do a REBUILD
Agreed on the testing; try not to do too many databases that use the same drive spindles at once. As far as CPU goes, parallel index maintenance of compressed indexes is very CPU intensive (I've seen it CPU bound on a 16 core box); but maintenance of uncompressed indexes don't seem bad.
Paul: Perhaps you can explain more of when, and why, you'd choose a reorg vs. an online rebuild vs. an offline rebuild. In some situations (Standard edition, some nondedicated 'slower time' and a too small dedicated maintenance window) reorganizing as much as possible seems to make sense. In others (any edition, a dedicated maintenance window hours longer than actually required once regular maintenance has started), I prefer to rebuild all the time. If the maintenance window's going to be there, use it to the best advantage possible, and rebuilds deliver superior end results.
Okay, let me get this straight. You would rather rebuild all indexes on all tables during your maintenance window? How much space do you have for your t-log, or do you switch to bulk_logged recovery model before rebuilding?
Some of my environments have had databases with 50,000+ tables. I'm not rebuilding every index on every table. I hit those that need it.
My apologies - I wasn't clear. For those indexes that need maintenance, I would rather rebuild than reorganize unless there is a specific reason to choose the reorganization. I do not advocate doing index maintenance on everything; set some level of criteria for what does and does not need maintenance, and only maintain those that need it.
I am, however, questioning the reorg vs. rebuild choice, and asking what benefits a reorg has over a rebuild.
April 11, 2012 at 3:19 pm
Reorg is fully logged and can be aborted without losing any work already completed and it olny works at the leaf level so runs faster.
A rebuild is all or nothing. If something happens to cause a rebuild to abort, the work completed up to that point is lost during the rollback of the rebuild.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply