How to optimize performance for MSSQL databases?

Question

I'm a dba with a database with ~280 tables, and total datasize of ~1,5GB. I'd like to tune the database to make it perform better.

What do you do to keep your SQL databases performant? How much fiddling with indexes, statistics, and defragmentation do you do?

What are the biggest performance killers/improvements, and how do you troubleshoot to find out where to optimize?

Edit: This is a database from a 3rd party CRM system, so I have no control over the code. They have added lots of indexes (in sensible places), but I would like to know how I can maintain the speed of the server.

I do run nightly

EXEC sp_MSforeachtable "dbcc dbreindex('?', '', 90)"

to rebuild indexes and (hopefully) update statistics, using a maintenance plan. The same plan also executes a "Shrink Database Task".

What other nightly/weekly maintenance tasks, or one-time optimizations could be done?

Edit 2:

Tips gathered:

• Do not run "Shrink Database Task"
• Turn off "Auto Shrink" property
• Run "EXEC sp_MSforeachtable "dbcc dbreindex('?', '', 90)"
• Then run "EXEC sp_updatestats"

Answer 1

you should remove the "shrink database" part of your maintenance plan. "shrink database" will fragment your index! here is a great blog post by Paul S. Randal which explains this in detail.

you can rebuild your statistics with:

EXEC sp_updatestats

update your statistics always after your database reindex. here is a blog post by Colin Stasiuk which explains this best practice.

Answer 2

I got orders of magnitude performance improvement only by indexing (although I didn't really fiddle with it much)

After you handle the obvious (decent hardware, indexing and defrag'ing) you're best source for performance is looking at your own code.

Answer 3

Make sure your SQL server isn't doing anything other than serving SQL, no file shares, no web servers. A database that size shouldn't have any performance issues, running Profiler will help zero in on any slow spots.

Make sure you pre-allocate enough space so the database isn't auto-growing all the time.

Answer 4

if you have sp2 of sql2005, you can (and since you're a dba probably already did) install the performance dashboard report plugin. after rebooting the server, i'll watch the performance monitor for helpful indices. the performance monitor provides a wealth of information. over time, i've found that it accumulates to much stuff (but that might be because i have a database setup like fogbugz ... i have many client databases, all with the same structure, on the same server ... sql server will treat needed indices in each of these as separate and that leads to a bunch of effectively duplicate entries in the report). anyway, i spend a lot of time looking at the missing index reports and adding/modifying indices as a result.

best regards, don

Answer 5

Stick your main database files on different physical disks than your log files. ie - separate your .MDF and your .LDF files.

Answer 6

All hardware fixes sorry but I'd suggest using;

64-bit Windows 2008 Lots of memory (16GB or so I'd suggest, it is cheap at the moment) Logs on an SSD, data on a mirrored pair of 15krpm SCSI/SAS disks Ideally a pair of Xeon 55xx-series chips.

This will all cost money obviously but will guarantee significant performance increases quickly - you can make your DB better but this takes time (which costs too of course) and isn't guaranteed to make it faster.

Answer 7

Test run your sql/stored procs in the SQL Management Studio. When you run them, turn on View Execution Plan and Client Statistics. Look for table scans or other steps that use take up large %'s of the execution time. If you are running SQL 2008, it will suggest indexes that will make your code run faster.