Home Forums SQL Server 2008 SQL Server 2008 Performance Tuning What should be the best approach to identify performance problems in Production environment RE: What should be the best approach to identify performance problems in Production environment

  • Everyones approach is different but personally I find performance monitor is only realy helpful if you have a baseline of values for counters for when the system is performing as expected.

    You can do this by capturing all the counters you feel is relevant, such as CPU utilisation, batch requests per second etc using a counter log, and then summaries the data identifying your Avg \ Max readings, this gives you something to compare to.

    DMV's can be used to cover many aspects but in terms of performance analysis you can look for example at the top 10 queries by reads \ writes etc one example here:

    http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/

    As for profiler never use this is a production environment, if you are needing to capture traces either use the server side trace stored procedures

    http://technet.microsoft.com/en-us/library/cc293613.aspx

    or extended events (much better in SQL 2012)

    http://www.sqlskills.com/blogs/jonathan/converting-sql-trace-to-extended-events-in-sql-server-2012/

    You should also check out the procedure sp_whoisactive by Adam Mechanic as this is very helpfull for capturing stats about currently executing queries:

    http://sqlblog.com/blogs/adam_machanic/archive/tags/sp_5F00_whoisactive/default.aspx

    MCITP SQL 2005, MCSA SQL 2012