May 17, 2012 at 9:33 am
I have a large number of tables that need to have the indexes and statistics updated.
We are a 24/7 shop and I would like to rebuild and update the statistics in the order of most fragmented to the least I will have to update them a few at a time so not to fill up the transaction log and not to impact everyone that is using the system.
What sql scripts are you using?
How are you doing you update you indexes and statistics.
Any Ideas would be appreciated thanks
May 17, 2012 at 10:20 am
A good system for index and stats maintenance:
SQL Server Index and Statistics Maintenance - Ola Hallengren
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 17, 2012 at 11:59 am
Thanks do you use this how long does it take to run(how many tables do you run it on) and do you run it while users on on the database.
May 17, 2012 at 12:37 pm
The length of time it takes is completely dependent on the resources available, the size of the tables, contention with other processes... the list goes on. There's no easy way to say that a table of size X will take Y amount of time. In general, I recommend running index rebuilds during off hours. They will cause locking and blocking as well as CPU, disk & memory hits.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 17, 2012 at 12:48 pm
twdavis-893252 (5/17/2012)
Thanks do you use this how long does it take to run(how many tables do you run it on) and do you run it while users on on the database.
Yes I use it currently and have implemented it at many client sites. I recommend running it nightly on most databases, as long as there is a maintenance window. If you are running this on a database that has not had its indexes and statistics regularly maintained it can generate quite a bit of transaction log activity the first time you run it, to the point where it may grow your logs significantly. You may even consider shrinking the logs once you are caught up and the maintenance job is not re-indexing a lot of tables every night. One of many nice things about Ola's solution is you can run it with logging (implement the CommandLog table and set @LogToTable to Y). Another nice option is you can run it with @Execute = 0 to see what it would do the first time you run it. After seeing what it would do in the CommandLog table you may want to run some of the index rebuilds manually, a few a night, until you get caught up, then schedule it with @Execute = 1 to run nightly.
Also, take everything Grant said to heart.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 17, 2012 at 12:55 pm
Thank You
I will try this with @Execute = 0
Thus database is big old and ugly
Thanks again
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply