Sql Server improving performance with monitoring etc

Question

What are some ways I can constantly monitor and report on my production sql server so that I can determine where locks and performance improvements can be made?

Answer 1

This is a very open ended question and the answer varies whom you talk to as there is so much that can be done.

At a minimum, you should do the following.

1. Look at these diagnostic queries from Glenn Berry and find out the expensive procs based on IO, CPU etc...

http://sqlserverperformance.wordpress.com/2011/05/16/may-2011-sql-server-2008-diagnostic-information-queries/

1. Look at Perfmon counters and act upon what you find that stand out.

http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

1. Look at memory usage, plan cache etc...

2. Look at sys.dm_io_virtual_file_stats and identify if you have IO bottlenecks

3. Proper Index maintenance including updating the statistics

4. Use sp_whoisactive to look at the current activity on the server.

http://sqlblog.com/files/folders/release/entry35240.aspx

This is a very high level starting point and don't consider this as a final list though.