JSI Tip 10138. How to troubleshoot SQL Server performance issues?

Microsoft Knowledge Base Article 298475 contains the following summary:

To troubleshoot performance issues, you must complete a series of steps to isolate and determine the cause of the problem. Possible causes include:

Identifying these causes is typically very time consuming, and you may spend several days evaluating the collected information. The complexity of the performance analysis is not specific to a particular database product or operating system. All applications are subject to performance constraints because of resource, design, or usage factors.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

224587 How to troubleshoot application performance with SQL Server

To help identify and troubleshoot the problem, collect the following information at the same time and have the output readily available before you contact Microsoft Product Support Services (PSS):

Note If you do not collect this information when the performance problem occurs, you may have to gather all the information again. This can delay the troubleshooting process.

After the problem occurs, collect the following information and have it available:

In most scenarios, PSS requires this information to understand the environment and the nature of the performance issue. If any part of this information is not available, the troubleshooting process may be prolonged and identifying the performance issue may be delayed.

Even if you are currently not experiencing any performance issues, Microsoft recommends that you implement this process. If a performance issue occurs, you can capture the required information as soon as possible. Additionally, if you capture a baseline SQL Profiler log, SQL Server Performance Monitor log, and blocker script when the application is performing as expected, you can use that information for comparison when the application does not perform as expected.

If you gather this information in a high traffic SQL Server environment, you may experience some performance degradation. However, you must have this information to identify the cause of the problem and for troubleshooting purposes. The SQL Profiler trace has the most impact on performance. If the performance is severely degraded, you can customize the SQL Profiler trace by reducing the types of events that it captures. Limiting the SQL Profiler trace should provide some improvement. If you have questions or problems setting up and collecting the information, contact PSS.