How do you troubleshoot a SQL Server performance problem?

Answer 1

*   Review management software for the hardware, SAN, NAS, tape library, etc. for any hardware related failures
*   Review System Monitor for CPU, Memory, IO, etc issues
*   Coordinate with the DBAs on tools like Profiler, DMVs, etc.

Answer 2

sqldiag utility:
------------------
The sqldiag utility is provided with SQL Server. It collects valuable information about the configuration of the computer running SQL Server, the operating system, and the information that is reported to the SQL Server error logs.

Answer 3

Microsoft Windows NT System and Application Event logs:
------------------------------------------------------------------------
You can use the Windows NT system and application event logs to identify issues that you cannot see in other data. These logs help provide a complete view of server activity and provide a more complete understanding of the environment.

Answer 4

SQL Server Performance Monitor log
SQL Server is typically affected by the following bottlenecks:
*  CPU
*  Memory
*  File I/O
*  Locking, blocking, or deadlocking

You can use SQL Server Performance Monitor to identify how these potential bottlenecks may affect SQL Server. Additionally, you can use this log to identify when an external process is heavily using the computer running SQL Server and negatively impacting SQL Server performance.

Before you start SQL Server Performance Monitor, make sure that the disk counters are on. To do so, run diskperf from a command prompt. If the disk counters are not on, run diskperf -y and then restart the computer.

When you create a SQL Server Performance Monitor log, collect the following information:
*  Paging file
*  Process
*  Processor
*  All SQL Server counters
*  Memory
*  Threads
*  Logical disk
*  Physical disk
*  System

Note: The default interval of 15 seconds should enough time to monitor the server; however, for some timing issues, you may have to reduce the time interval for collecting data.

Answer 5

SQL Profiler trace log:

The SQL Profiler trace captures the activity on the computer running SQL Server. You can use this information to identify slow running queries and non-optimal execution plans. Additionally, SQL Profiler documents the series of events that occur before the performance problem and helps to identify its cause.