October 4, 2010 at 8:18 am
Hi,
I wanted to know what all checks I should make daily, weekly or monthly to ensure that the sql server runs at an optimal level.?
What are the best practices to be followed or what could be done to improve the performance of the SQL Server systems.
Thanks,
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 4, 2010 at 9:35 am
How often should reorg and rebuild indexes should be run on a production database?
How is update statistics differ from above two. Its runnning frequency should be diffferent?
Checking integrity checkup does help or not?
These all I could find regarding optimization on the maintenace plans.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 4, 2010 at 12:52 pm
What should be the order of these maintenance plans when performing on anyy datbaase :-
Update statisitcs, reorganize index, shrinking database, rebuilding index, checking integrity
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 4, 2010 at 12:56 pm
Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2010 at 1:11 pm
Yes db shrinking obviously affects performance. If you dont have space .. forced to do that.. you do.
Normally its not a good practice to shrink the db frequently.
The order may..
checking integrity
rebuilding index
reorganize index (not required if rebuilding as it will do reorganizing)
Update statisitcs
checking integrity
Rebuilding is required if the index is fragmented and that too it is having more than 30% fragmentation.
It all depends on the usage of the database. If the insert and updates are happening very frequently then you might need to rebuilding every day.
If it is too much you can even reduce this time. Normally whenever you do bulk insert and updated indexes will be fragmented. So depends on this kind of transactions.
Check integrity you can schedule weekly or monthly.
However make sure you are running Update stats whenever you rebuild index. Even though you have set auto update statistics, sometimes its good to update statistics whenever you rebuild indexes. So that the query optimizer can use the latest statistics.
If you are making huge insert/updates which might affect whole table its good drop/disable index and rebuild after your insert update operation.
If you have any doubts/questions ?
If your problem solved or satisfied atleast tell me thanks 🙂
Please excuse if any typo.
October 4, 2010 at 1:19 pm
brainy (10/4/2010)
However make sure you are running Update stats whenever you rebuild index. Even though you have set auto update statistics, sometimes its good to update statistics whenever you rebuild indexes.
Rebuilding indexes always updates the stats on that index with fullscan. Hence is it only necessary to update the column stats (stats not associated with an index) if you're rebuilding everything.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2010 at 1:21 pm
I'm assuming you're not talking about disaster recovery (DR)?
Just for tuning performance, first thing I'd do is set up data collection to get a good set of metrics from performance monitor, or use a third party tool for the same thing. I'd also set up a mechanism to sample procedure performance using a server side trace. All that data gets kept so you can generate reports and see how things are behaving over time. That's one of the best ways to tell when you have a problem.
Other than that, you're asking about maintenance. Frequency of these operations really depends on the system, but you need to update statistics, reorganize or rebuild your indexes based on the level of fragmentation and the number of pages... huh, except for all the DR stuff, that's all I can think of that we do automatically for performance. After that, most everything else is determined by individual systems and the data we collect in monitoring.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 4, 2010 at 1:23 pm
@ brainy...
Why have you mentioned checking database integrity twice (at starting and at end)?
Also, my production databases are150GB, 6GB , 3 GB so how frequently should I run these steps for each database?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 4, 2010 at 1:26 pm
Thanks Gail... giving correct information.
Yes update statistics will update stastics of columns.
But its good to update statistics frequently on heavily running transaction database.
As you may be planning for scheduled maintainance, its good to update statistics as you rebuild index.
October 4, 2010 at 1:30 pm
@Sushant... i assume you are night shift 🙂 ?
That is just to make sure the db is in good state before you run rebuild. Its required.. just to check..
If you feel if it taking more time.. you skip that step.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply