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:
|•||System resource contention|
|•||Application design problems|
|•||Queries or stored procedures that have long execution times|
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):
|•||Blocker script output|
|•||SQL Profiler trace log|
|•||SQL Server Performance Monitor log|
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:
|•||Microsoft Windows NT System and Application Event logs|
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.