You’re experiencing a sudden slow down in SQL Server performance. Users are complaining. Many reasons could be causing this sudden slowdown.

For SQL Server performance problems, gathering and analyzing data is not enough. A lot of time can be wasted analyzing data not relevant to the problem.

Before you contact me to report a problem, spend some time preparing the information to assist in solving the problem. This assists me to work with you quicker in solving the SQL Server performance issue.

Some suggestions to solve the problem faster are:

1. Provide a report in plain English of a simple specific example of the problem. Focus on the facts. If you have a theory about the cause of the problem, present it as a separate statement. Avoid using statements such as "the database server is very slow", they require investigation to determine what you mean by “very slow”, and defining acceptable performance.

   Focus on these questions:

   a) Can you recreate the problem? If not, outline the simplest example of the SQL Server performance problem
   b) Is the problem persistent? Or random? What time of the day is it occurring?
   c) Is everything slow or just some activity? Which activity is slow?
   d) When did the problem start? Was it when migrating into Production? Have changes occurred recently, such as more users or large data import?
   e) If multi-tier, can the problem be recreated locally?
   f) What other applications are on the database server?
   g) What is the impact of the performance problem on the users?
2. Provide information about all changes on the system in the days before the performance problem started. Providing as much information as possible , allows me to investigate various options. Allow me to eliminate unrelated causes.
3. Use the correct server to supply information. It is easy to accidentally collect the data on the incorrect server, especially on very large production sites. This makes it very hard to investigate the problem.

Once you’ve completed the above steps, contact me to work with you directly. The normal procedure is I will send you some data gathering scripts about database server information and database usage. The nature of the scripts is based on the information you send me.

Republished from http://www.sqlserver-dba.com.