Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Does anyone know what is the best tool to optimize an entire SQL Server database?

I'm not talking about how to write the queries but more how to manage the database file, indexes, paging and fragmentation.

PAINFUL TRUTH
The eternal fight between tools vs DBAs is still there ...but what I just wanted to know the basics till the product grows and then I can hire a non grouchy DBA :--)

share|improve this question

migrated from stackoverflow.com Nov 12 '11 at 18:31

This question came from our site for professional and enthusiast programmers.

4  
A competent DBA. Just don't go calling them a tool, they can get a bit grouchy. –  Mark Storey-Smith Nov 12 '11 at 21:15
    
@MarkStoreySmith haha too funny, that is great and so true. No way to automate pure database administration. –  Thomas Stringer Nov 12 '11 at 21:25
    
99% of improvements require changes to the application code. The best tool is a developer that can understand SQL performance and take corrective actions. Best if done before release. –  Remus Rusanu Nov 27 '12 at 15:58

1 Answer 1

I'd echo @Mark Storey-Smith's comment - a competent DBA is the best way to go here. You can't really automate a well tuned SQL Server but a good DBA can setup various maintenance items to keep it running well.

Sounds like you were asking a lot about maintenance so one great spot to look for some scripts to help setup a best of breed monitoring solution is Olla Hallengren's Maintenance Solution scripts explained on Olla's site here.

That will help ensure you are at least doing the important maintenance items (Index rebuilding/reorganizing, statistics updating, backups, updating statistics, checking database integrity, etc.)

As far as the ongoing optimization, I'd suggest picking up a copy of the Professional SQL Server 2008 Internals and Troubleshooting book. It has just the right amount of internals knowledge to help you understand the "why" behind best practices and contains plenty of practical examples for implementing the best practices.

Or I'd recommend the same Internals & Troubleshooting book but for SQL Server 2012. Contains some great chapters just for your question. Like how to perform a SQL Server Health Check, by Glenn Berry.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.