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?
- Do not run "Shrink Database Task"
- Turn off "Auto Shrink" property
- Run "EXEC sp_MSforeachtable "dbcc dbreindex('?', '', 90)"
- Then run "EXEC sp_updatestats"