How to troubleshoot slow running query
stored procedure that
running slow.What are
the main steps to identify
the reason(s) and how
to change the query?
I assume you already have the query and I am not going to
talk about how to find the slow queries/stored procedures.
I am not going to talk about the CPU/Network/Memory
bottleneck and other fancy words that generally will not help
us with performance of single query or stored procedure.
What I want is to list the main steps I and my DBA team do
when we have a slow running query/SP.
1) Examine the Execution Plan:
Take a look at the query execution plan,
a. Search for Table Scan / Index Scan, these operators
significant if you access big tables.
Check if proper indexes already exist on the tables,
find out why SQL Server chose not to use them,
for example, usage of local variables, parameters sniffing,
outdated statistics,heavily fragmented indexes can cause
to Table/Index scan.
b. Check if you have any key lookups/bookmark lookups.
In case of large table / big result set this one can lead to
performance degradation. See if you can create covering index
or include the needed columns in a non clustered index.
c. See if you have Spool operators in the execution plan.
These operators are Blocking operators,meaning they need to
get all the data at once and not row by row. An excellent
deep dive into Spool operators you can find here.
Very briefly: A spool reads the data and saves it on TempDB.
This process is used whenever the Optimizer knows that the
density of the column is high and the intermediate result is very
complex to calculate.
d. If you see that there is index seek operator, check if the SQL
Server chose the right index. There are situations when Query
Optimizer can choose "wrong" index, this can happened when
you have more than one index in the table that SQL Server
can use to fetch the data.In such cases, check your query filters
in the Where clause.
e. Compare the Actual number of rows versus Estimated.
If you see big difference in these numbers, this is a sign
that you use wrong generated execution plan.
Check statistics, consider to use Option(recompile).
Check in the Execution Plan in XML view what
were the values of input paramters when the SP was
compiled. This can help you to undestand why
SQL Server built the execution plan you see.
f) For every operator in every query, you will see
the operator`s cost. By checking this cost, you
can easily the most expensive operators in the query.
One thing to note here:
Even in the actual execution plan, you can only see
the estimated costs, and the percentage values represent
the estimated relative costs.
Therefore, If there is a big difference between the
estimated and the actual number of rows, there is a good
chance that the relative costs are misleading.
2) Analyze the output of STATISTICS IO
By setting the SET STATISTICS IO ON,
SQL Server will display information regarding the amount of disk
activity generated by Transact-SQL statements:
Table 'ProductCostHistory'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
In the output you need to check the following:
Scan count - Number of index or table scans(access) performed.
Logical reads - Number of pages read from the data cache.
These counters are very handy when you compare the changes
you made in the query.
Almost in all cases the reason for slowness of a query is the high
and not really needed amount of 'pages read'/ Logical reads.
You need to check if the amount of 'page reads' is really what you
expect to be, if not , here is your main problem.
By looking at these counters you can see how many times SQL
Server accessed a table, how many pages were read.
Search for high number of Logical reads and on each change of
the query compare these values.
3) Try to rewrite the query / stored procedure
Check if you use DISTINCT / UNION,
not SARGable expressions (like != ),functions on columns
in the where clause, scalar functions,wrong Index/Join hints.
Use JOINs rather than subqueries.