July 1, 2014 at 5:19 am
Hi - I'm relatively new to SQL and am trying to obtain advice regarding regular maintenance that should be carried out as best practice. Is anyone able to give me any advice on this?
Many thanks
Dax
July 1, 2014 at 5:25 am
Backups. First and most important, get your backups set up and ensure that your backup strategy can meet your SLAs
Integrity checks. Often enough that should you encounter corruption restoring from a clean backup is always an option.
Index maintenance. Use Ola's index maintenance. ola.hallengren.com/
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
July 1, 2014 at 7:12 am
Backups. Tested backups (which means a restore to a test machine somewhere). Nothing is more important than ensuring the continuity of your organization. That means talking to the business to understand what they need in terms of two points, Recovery Point Objective and Recovery Time Objective. The RPO is basically the amount of data the business can afford to lose. They'll tell you zero, but you have to drill down to get a reasonable number, say, 10 minutes. That helps determine how you set up your backups. The RTO is how long it takes you to restore the system. Again, the business has to work with you on this. They'll say zero again, but it's driven by how long it will take to run the restores. Here's an introductory article on backups.[/url] I would get that in line before I worried about anything else at all.
Gail's nailed the rest. Index fragmentation doesn't worry me like it used to, but I absolutely sweat statistics maintenance. Ola's scripts handle that, but just in case, I'd be sure you have a good understanding of statistics and statistics maintenance. Here's an article to get you started there.[/url]
"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
July 1, 2014 at 7:49 am
To add to Grant's remarks about statistics maintenance: hard to overestimate the importance. In the past when we encountered performance issues we first checked if the maintenance plan was running properly. The MP comprised checkdb, index rebuild and update statistics. Just recently we understand better that update stats is key for performance. Also on smaller tables it is absolutely vital. We have seen multiple examples where adding a single record to a small table could cause queries to perform slow because said record is not in the stats yet.
A daily stats update is advised but depending on circumstances could be even more often.
July 2, 2014 at 11:08 am
Hi - Many thanks for the reply. Is it best to run update statistics out of hours when there is no user activity and after a backup?
Regards
Dax
July 2, 2014 at 11:54 am
dax.latchford (7/2/2014)
Hi - Many thanks for the reply. Is it best to run update statistics out of hours when there is no user activity and after a backup?Regards
Dax
Generally - that would be the ideal solution. With that said, SQL Server will update statistics automatically as needed throughout the day - but that could in some circumstances cause even more issues. When SQL Server performs an auto update of statistics - it will use the default sampling rate and that may cause a less than optimal execution plan to be generated.
Whether or not this has any affect will be determined by the index/column in question.
All this comes down to is - in general, yes - updating statistics after hours is the best, but if you have issues on specific tables you may want to consider updating those statistics more often.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply