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:
- SQL Server Profiler (client and SQL Trace).
- SQL Server Error Logs.
- System objects like sp_who, sp_sho2, sp_lock, sys.processes, sysperfinfo etc.
- Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs).
- Execution Plan Analysis via Query Analyzer or SSMS (via various SET commands).
- Index Tuning Wizard.
- Default Trace.
- Black Box Traces.
- Trace Flags.
- Windows Application and System Logs.
- DBCC commands.
- Scripts from SQLCAT and PSS group:
- Waits and Queues Scripts (also has RDL files).
- Blocked Processes Script and RDL file.
- CDC (Change Data Capture) – only in SQL Server 2008.
- Audit features – either built in features like SQL Server Audit (SQL 2008) or using T-SQL code.
- 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).