February 8, 2011 at 6:13 am
heres a simple example.
Lets say you have a large db that has to be online24/7. It has tables with many indexes that need to be maintained.
Most systems will have a downtime, or time of low usage where indexes can be rebuilt or reorganized.
what if there is no room for even this downtime or period of reduced performance?
How do people like facebook, amazon etc manage this?
February 8, 2011 at 7:28 am
Clustering and load balancing.
Have 1000 servers. Take 1 of them down for maintenance and noone will ever know.
The same can be done with only 2 servers assuming 1 server can "easily" handle the workload at some point in time of the day.
February 8, 2011 at 7:58 am
winston Smith (2/8/2011)
Lets say you have a large db that has to be online24/7. It has tables with many indexes that need to be maintained.Most systems will have a downtime, or time of low usage where indexes can be rebuilt or reorganized.
what if there is no room for even this downtime or period of reduced performance?
How do people like facebook, amazon etc manage this?
Lets assume you are not running a large non-sql database farm then you have to deal with a single server architecture.
I still have to see a single 24/7 database that does not allows for planned maintenance windows - believe me, I do work with very large, very critical 24/7 databases.
Having said that, lets assume that for any - clear or obscure reason - you cannot get a maintenance window to rebuild indexes.
In such an scenario...
1- Determine wich indexes really - as in really, really really - need to be reorganized; remember, in most cases indexes tend to always reach a comfort threashold.
2- Following Microsoft guidelines..
... if your target index is less than 30% fragmented use alter index reorganize - which does the job online.
... if your target index is more than 30% fragmented use alter index rebuild online - which does the job online.
Hope this helps.
_____________________________________
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.February 8, 2011 at 8:05 am
PaulB-TheOneAndOnly (2/8/2011)
winston Smith (2/8/2011)
Lets say you have a large db that has to be online24/7. It has tables with many indexes that need to be maintained.Most systems will have a downtime, or time of low usage where indexes can be rebuilt or reorganized.
2- Following Microsoft guidelines..
... if your target index is less than 30% fragmented use alter index reorganize - which does the job online.
... if your target index is more than 30% fragmented use alter index rebuild online - which does the job online.
Hope this helps.
This is the best and most customizable script I've seen out there. If anything can be run live, this will.
http://sqlfool.com/2010/04/index-defrag-script-v4-0/
Of course if you have enterprise version, then it's much more faisable to reindex online.
February 8, 2011 at 10:40 am
Is this enterprise edition? If so, then you have some online operations available.
If not, you'll have to get creative with ideas, as some of the people have mentioned above.
February 8, 2011 at 2:56 pm
I know of several organizations that schedule maintenance quarterly, or yearly, or somewhere in between. Part of that scheduled maintenance would be index rebuilds, etc...
Honestly, if this was my system I would upgrade to Enterprise Edition, add more CPU's and memory, schedule at least weekly online rebuilds for critical tables and schedule update statistics on critical tables as often as needed.
For any indexes that could not be rebuilt online, I would schedule a reorganize as often as possible. Reorganize is an online operation also, but it will take a lot longer than a rebuild.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 8, 2011 at 3:18 pm
A way we used to deal with this on some critical systems, specifically the reindexing, was object swaps.
Copy the table in question, make sure it's auditing so you can re-process any necessary changes.
Rebuild the indexing.
Process the audited changes since your copy.
Put up a 10 second splash page on the application asking customers to hold on a second.
sp_rename your tables around.
Double check functionality.
Take down splash page.
It takes some imagination but there's ways to deal with it, but you've got to prepare for it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply