|
In this article we will share some of the common reasons for slow-running queries and what your approach should be for identifying and fixing them. The best and the quickest way to address any slow running query should be by first preparing a Troubleshooting Checklist or Troubleshooting Guide and than by using it during slow running query situations. This way it can help any developer or DBA to easily and quickly narrow down the issue. (This troubleshooting guide or checklist should be posted on a central location from where all the DBA's / Developers of your team can access and update when required)
Please note: If you experience something new which is not listed in the below list than you may update your TSG (Troubleshooting Guide) appropriately.
Queries or updates that take more than the expected time to execute can be caused by a variety of reasons. Slow-running queries can be caused by performance problems related to your network or the computer where SQL Server is running. Slow-running queries can also be caused by problems with your physical database design and SQL Server configuration.
Some of the most common reasons for slow-running queries and updates are:
1) CPU could be causing the bottleneck
2) Slow network communication (high latency).
3) Insufficient memory in the server computer, or the memory is not properly configured for SQL Server instance.
4) Insufficient useful statistics on indexed columns.
5) Outdated statistics on indexed columns.
6) Insufficient useful indexes.
7) Lack of useful indexed views.
8) Lack of useful data striping.
9) Improper partitioning of data.
Troubleshooting Checklist (covering most common scenarios)
1) CPU could be causing the bottleneck
|
What to do?
One of the most common complaints I get is that SQL Server CPU started spiking excessively all of a sudden. A CPU bottleneck that happens suddenly and unexpectedly, without any changes or load on the server may be caused due to several reasons but some of the common reasons are:
1) Non-optimal query plan,
2) A poor SQL Server configuration,
3) Improper Application/Database design,
4) Insufficient hardware resources.
Read more about “How to detect if SQL Server has bottleneck or not?”
|
2) Could it be slow network communication (high latency).
|
What to do?
Investigate if the performance problem is related to a component other than queries? For example, is there a problem with network performance, do you see any latency issue, try copying the files across to measure the performance of your network. Also, try to identify if there are any other components that might be causing or contributing to performance degradation?
What tool to use?
The Windows System Monitor can be used to monitor the performance of SQL Server and non-SQL Server related components.
Where to find more info?:
http://msdn2.microsoft.com/en-us/library/ms191246.aspx
|
3) Could it be Insufficient memory on the server computer, or the memory is not properly configured
for SQL Server instance?
4) Could it be due to Insufficient useful statistics on indexed columns.
|
What to do?
Investigate if the query was optimized with useful statistics or not? In SQL Server, statistics on the distribution of values in a column are created automatically on the indexed columns. They can also be created on non-indexed columns either manually using SQL Server Management Studio or CREATE STATISTICS statement, or automatically, if the AUTO_CREATE_STATISTICS database option is set to TRUE. These statistics can be used by the query processor to determine the optimal strategy for evaluating a query. Maintaining additional statistics on non-indexed columns involved in join operations can improve query performance.
Alternatively, you can also monitor the query using SQL Server Profiler or the graphical execution plan in SQL Server Management Studio to determine if the query has enough statistics.
What tools to use?
Ø You can use Create Statistics, AUTO_CREATE_STATISTICS for creating and updating indexes. (Look at the below section for resource links)
Ø You can also use SQL Server Profiler to determine if query has enough statistics
Ø You can also use Graphical Execution plan from SQL Server Management studio to determine if query has enough statistics or not.
Where to find more info?
Create Statistics, Auto_Create_Statistics
Details about Statistics and Indexes by Kalen Delaney
Script to get the facts whether STATISTICS are required or not by Saleem Hakani
|
5) Could it be due to outdated statistics on indexed columns.
|
What to do?
Check if the query statistics are up to date? Are the statistics automatically updated?
SQL Server would automatically create and update query statistics on indexed columns (as long as automatic query statistic updating is not disabled). Additionally, statistics can be updated on non-indexed columns either manually, using SQL Server Management Studio or the UPDATE STATISTICS statement, or automatically, if the AUTO_UPDATE_STATISTICS database option is set to TRUE.
Tip: Up-to-date statistics are not dependent upon date or time data. If no UPDATE operations have taken place, the query statistics are still up-to-date.
What tool to use?
You may use Auto_Create_Statistics property of the database to enable or disable the creation of statistics automatically.
Where to find more info?
You may use ALTER DATABASE option to enable AUTO_CREATE_STATISTICS on a database.
|
6) Could it be due to Insufficient useful indexes.
7) Could it be the lack of useful indexed views.
8) Could it be due to lack of useful data striping.
9) Could it be due to improper partitioning of data.
|
What to do?
Check if you have a large volume of data and if you do then you need to partition that data to achieve maximum performance from your queries. Data manageability is the main benefit of partitioning, but if your tables and indexes on them are partitioned similarly, partitioning can also improve query performance greatly.
Where to find more info?
Understanding SQL Server 2005 Data Partitioning
Tuning the physical database design
|
Once you perform all the above steps you should be able to resolve the performance issues for SQL Server queries.
We will be adding more data to this article as it becomes available.
|