How to troubleshoot slow running query

So you have a query or
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.