Systems Engineering and RDBMS

Tools at one’s disposal for troubleshooting performance issues

Posted by decipherinfosys on May 12, 2009

In a casual conversation with a client’s team last week, we were discussing the tools that the team uses for troubleshooting performance issues in a SQL Server environment.  Besides the third party tools that are out there and there are a lot of very good ones, the tools and utilities that are commonly used by SQL Server professionals to aid in troubleshooting performance issues are as follows:

  1. SQL Server Profiler (client and SQL Trace).
  2. SQL Server Error Logs.
  3. System objects like sp_who, sp_sho2, sp_lock, sys.processes, sysperfinfo etc.
  4. Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs).
  5. Execution Plan Analysis via Query Analyzer or SSMS (via various SET commands).
  6. Index Tuning Wizard.
  7. Default Trace.
  8. Black Box Traces.
  9. Trace Flags.
  10. Windows Application and System Logs.
  11. Perfmon.
  12. DBCC commands.
  13. Scripts from SQLCAT and PSS group:
    1. Waits and Queues Scripts (also has RDL files).
    2. Blocked Processes Script and RDL file.
  14. CDC (Change Data Capture) – only in SQL Server 2008.
  15. Audit features – either built in features like SQL Server Audit (SQL 2008) or using T-SQL code.
  16. Extended Events – only in SQL Server 2008.

The last one i.e. extended events feature in SQL Server 2008 is a general event handling system that will more than likely become the most common approach for troubleshooting in the future.  Even SQL Server Audit feature in SQL Server 2008 is built at the top of the XEVENT (Extended Events).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: