Sql-server – How to optimize performance for MSSQL databases

database-administrationperformancesql server

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"

Best Answer

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.

Related Topic