What to do if a specific query is slow or erratic

If a stored procedure is erratic,
see if it recompiles,
look into parameter sniffing,
others have already talked about it (with good links),
I will provide links later

What to do if a specific query is slow, do the steps below.

If your specific query is not slow on another system,
do these steps on both slow and not slow systems

1. Duration, CPU & IO info
it is essential to provide CPU as well duration
if you complain only about duration,
we cannot tell whether it is an expensive execution plan
or blocking, which has completely different resolution paths.
Do the following from Query Analyzer or SQL Management Studio

SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

Your SQL query or stored procedure call
GO

SET STATISTICS TIME OFF
GO
SET STATISTICS IO OFF
GO

the output should look like:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(1104 row(s) affected)

Table 'M1'. Scan count 2, logical reads 12, physical reads 0, read-ahead reads 1.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 5 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.


2a. Execution Plan, if on SQL Server 2000
do the following to generate the estimated execution plan
any temp tables used must be created first in order to generate the estimated plan

CREATE TABLE #xx (if there are any temp tables)
GO
SET SHOWPLAN_ALL ON
GO
Your SQL query or stored procedure call
GO
SET SHOWPLAN_ALL OFF
GO
DROP TABLE #xx (if required above)

copy the results into a MS Excel spreadsheet
then save the file as XML
open the XML with notepad,
paste the contents in its own reply box
example in next reply box

it might also help to compare estimated plans with empty and populated temp tables

get the actual plan, ie, actual row counts
SET STATISTICS PROFILE ON
GO
Your SQL query or stored procedure call
GO
SET STATISTICS PROFILE OFF
GO

2b. Execution Plan, if on SQL Server 2005
generate the Displayed Estimated Execution Plan (ctrl-L)
from SQL Server Management Studio -> Query -> Display ..

save the execution plan as XML (*.sqlplan extension)
right click on the graphical plan, 1st option (Save Execution Plan as)

open the XML plan (*.sqlplan file) with Notepad
paste the contents in its own reply box

generate the actual execution plan,
Include Actual Execution Plan (Ctrl-M ?)
save as XML
open with notepad
paste

SQL 2000 - Excel XML and SQL 2005 XML plans
below is a simple SQL 2000 SHOWPLAN_ALL copied first to Excel,
then saved as XML file
it can be copied back into a XML file,
that we can open in Excel
and examine it without difficulty that a direct copy to here involves

next, after Luis, is a SQL 2005 plan saved as .sqlplan
then opened with notepad, and pasted here
we can put the contents back into a .sqlplan
and actually examine the graphical plan
if you want us to help you
help us see what your problem is

special handling for large Excel plan files
as I just found out in the SQL 2000 forum section
if the Excel XML or sqlplan file is very large
it is not practical to post it here

so email the person helping you, and send it compressed
(for Excel, keep it in native .xls format,
not need for XML)

3. SQL Clone and statistics-only database
for more extensive remote performance troubleshooting work
my site www.qdpma.com has a utility to
create a statistics-only database for SQL Server 2000

for SQL Server 2005
right click the database from Management Studio
-> Tasks -> Generate Scripts
after selecting the database
-> Choose Script Options
-> General -> Script Statistics

i think the option is statistics with histogram
you could just provide the DDL for the tables involved, with indexes

look into the following command example
DBCC SHOW_STATISTICS(N'[AdventureWorks].[Sales].[Store]', N'AK_Store_rowguid')
WITH STATS_STREAM

the Option: STATS_STREAM sends the raw binary of the statistics
the command below applies the statistics to a newly generated table

UPDATE STATISTICS [HumanResources].[Employee]([AK_Employee_LoginID])
WITH STATS_STREAM = long binary value,
ROWCOUNT = 290, PAGECOUNT = 3

&lt;?xml version="1.0"?&gt;<br />&lt;?mso-application progid="Excel.Sheet"?&gt;<br />&lt;Workbook xmlns="urn<img src='/community/emoticons/emotion-7.gif' alt=':s' />chemas-microsoft-com<img src='/community/emoticons/emotion-3.gif' alt='' />ffice<img src='/community/emoticons/emotion-7.gif' alt=':s' />preadsheet"<br /> xmlns<img src='/community/emoticons/emotion-3.gif' alt='' />="urn<img src='/community/emoticons/emotion-7.gif' alt=':s' />chemas-microsoft-com<img src='/community/emoticons/emotion-3.gif' alt='' />ffice<img src='/community/emoticons/emotion-3.gif' alt='' />ffice"<br /> xmlns<img src='/community/emoticons/emotion-12.gif' alt=':x' />="urn<img src='/community/emoticons/emotion-7.gif' alt=':s' />chemas-microsoft-com<img src='/community/emoticons/emotion-3.gif' alt='' />ffice:excel"<br /> xmlns<img src='/community/emoticons/emotion-7.gif' alt=':s' />s="urn<img src='/community/emoticons/emotion-7.gif' alt=':s' />chemas-microsoft-com<img src='/community/emoticons/emotion-3.gif' alt='' />ffice<img src='/community/emoticons/emotion-7.gif' alt=':s' />preadsheet"<br /> xmlns:html="http://www.w3.org/TR/REC-html40"&gt;<br /> &lt;DocumentProperties xmlns="urn<img src='/community/emoticons/emotion-7.gif' alt=':s' />chemas-microsoft-com<img src='/community/emoticons/emotion-3.gif' alt='' />ffice<img src='/community/emoticons/emotion-3.gif' alt='' />ffice"&gt;<br /> &lt;Author&gt;Joe Chang&lt;/Author&gt;<br /> &lt;LastAuthor&gt;Joe Chang&lt;/LastAuthor&gt;<br /> &lt;Created&gt;2007-02-17T03:08:50Z&lt;/Created&gt;<br /> &lt;Company&gt;QDPMA&lt;/Company&gt;<br /> &lt;Version&gt;11.8122&lt;/Version&gt;<br /> &lt;/DocumentProperties&gt;<br /> &lt;ExcelWorkbook xmlns="urn<img src='/community/emoticons/emotion-7.gif' alt=':s' />chemas-microsoft-com<img src='/community/emoticons/emotion-3.gif' alt='' />ffice:excel"&gt;<br /> &lt;WindowHeight&gt;9720&lt;/WindowHeight&gt;<br /> &lt;WindowWidth&gt;5595&lt;/WindowWidth&gt;<br /> &lt;WindowTopX&gt;480&lt;/WindowTopX&gt;<br /> &lt;WindowTopY&gt;15&lt;/WindowTopY&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />rotectStructure&gt;False&lt;/ProtectStructure&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />rotectWindows&gt;False&lt;/ProtectWindows&gt;<br /> &lt;/ExcelWorkbook&gt;<br /> &lt;Styles&gt;<br /> &lt;Style ss:ID="Default" ss:Name="Normal"&gt;<br /> &lt;Alignment ss:Vertical="Bottom"/&gt;<br /> &lt;Borders/&gt;<br /> &lt;Font/&gt;<br /> &lt;Interior/&gt;<br /> &lt;NumberFormat/&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />rotection/&gt;<br /> &lt;/Style&gt;<br /> &lt;Style ss:ID="s21"&gt;<br /> &lt;NumberFormat ss:Format="Scientific"/&gt;<br /> &lt;/Style&gt;<br /> &lt;/Styles&gt;<br /> &lt;Worksheet ss:Name="Sheet1"&gt;<br /> &lt;Table ss:ExpandedColumnCount="18" ss:ExpandedRowCount="2" x:FullColumns="1"<br /> x:FullRows="1"&gt;<br /> &lt;Row&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt;SELECT * FROM M1&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="Number"&gt;1&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="Number"&gt;1&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="Number"&gt;0&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt;NULL&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt;NULL&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="Number"&gt;1&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt;NULL&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="Number"&gt;1104&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt;NULL&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt;NULL&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt;NULL&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell ss<img src='/community/emoticons/emotion-7.gif' alt=':S' />tyleID="s21"&gt;&lt;Data ss:Type="Number"&gt;4.2575101999999997E-2&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt;NULL&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt;NULL&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt;SELECT&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="Number"&gt;0&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt;NULL&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;/Row&gt;<br /> &lt;Row&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt; |#45;-Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[MetArt].[dbo].[M1]))&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="Number"&gt;1&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="Number"&gt;3&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="Number"&gt;1&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt;Table Scan&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt;Table Scan&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt;OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[MetArt].[dbo].[M1])&lt;/Data&gt;&lt;/Cell&gt;<br /> &lt;Cell&gt;&lt;Data ss:Type="String"&gt;[M1].

Code:

&lt;/Data&gt;&lt;/Cell&gt;<br />    &lt;Cell&gt;&lt;Data ss:Type="Number"&gt;1104&lt;/Data&gt;&lt;/Cell&gt;<br />    &lt;Cell ss<img src='/community/emoticons/emotion-7.gif' alt=':S' />tyleID="s21"&gt;&lt;Data ss:Type="Number"&gt;4.1282203000000003E-2&lt;/Data&gt;&lt;/Cell&gt;<br />    &lt;Cell ss<img src='/community/emoticons/emotion-7.gif' alt=':S' />tyleID="s21"&gt;&lt;Data ss:Type="Number"&gt;1.2928999E-3&lt;/Data&gt;&lt;/Cell&gt;<br />    &lt;Cell&gt;&lt;Data ss:Type="Number"&gt;266&lt;/Data&gt;&lt;/Cell&gt;<br />    &lt;Cell ss<img src='/community/emoticons/emotion-7.gif' alt=':S' />tyleID="s21"&gt;&lt;Data ss:Type="Number"&gt;4.2575101999999997E-2&lt;/Data&gt;&lt;/Cell&gt;<br />    &lt;Cell&gt;&lt;Data ss:Type="String"&gt;[M1].[Code]&lt;/Data&gt;&lt;/Cell&gt;<br />    &lt;Cell&gt;&lt;Data ss:Type="String"&gt;NULL&lt;/Data&gt;&lt;/Cell&gt;<br />    &lt;Cell&gt;&lt;Data ss:Type="String"&gt<img src='/community/emoticons/emotion-4.gif' alt=';P' />LAN_ROW&lt;/Data&gt;&lt;/Cell&gt;<br />    &lt;Cell&gt;&lt;Data ss:Type="Number"&gt;0&lt;/Data&gt;&lt;/Cell&gt;<br />    &lt;Cell&gt;&lt;Data ss:Type="Number"&gt;1&lt;/Data&gt;&lt;/Cell&gt;<br />   &lt;/Row&gt;<br />  &lt;/Table&gt;<br />  &lt;WorksheetOptions xmlns="urn<img src='/community/emoticons/emotion-7.gif' alt=':s' />chemas-microsoft-com<img src='/community/emoticons/emotion-3.gif' alt=':o' />ffice:excel"&gt;<br />   &lt;Selected/&gt;<br />   &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />anes&gt;<br />    &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />ane&gt;<br />     &lt;Number&gt;3&lt;/Number&gt;<br />     &lt;ActiveRow&gt;8&lt;/ActiveRow&gt;<br />     &lt;ActiveCol&gt;13&lt;/ActiveCol&gt;<br />    &lt;/Pane&gt;<br />   &lt;/Panes&gt;<br />   &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />rotectObjects&gt;False&lt;/ProtectObjects&gt;<br />   &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />rotectScenarios&gt;False&lt;/ProtectScenarios&gt;<br />  &lt;/WorksheetOptions&gt;<br /> &lt;/Worksheet&gt;<br /> &lt;Worksheet ss:Name="Sheet2"&gt;<br />  &lt;WorksheetOptions xmlns="urn<img src='/community/emoticons/emotion-7.gif' alt=':s' />chemas-microsoft-com<img src='/community/emoticons/emotion-3.gif' alt=':o' />ffice:excel"&gt;<br />   &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />rotectObjects&gt;False&lt;/ProtectObjects&gt;<br />   &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />rotectScenarios&gt;False&lt;/ProtectScenarios&gt;<br />  &lt;/WorksheetOptions&gt;<br /> &lt;/Worksheet&gt;<br /> &lt;Worksheet ss:Name="Sheet3"&gt;<br />  &lt;WorksheetOptions xmlns="urn<img src='/community/emoticons/emotion-7.gif' alt=':s' />chemas-microsoft-com<img src='/community/emoticons/emotion-3.gif' alt=':o' />ffice:excel"&gt;<br />   &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />rotectObjects&gt;False&lt;/ProtectObjects&gt;<br />   &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />rotectScenarios&gt;False&lt;/ProtectScenarios&gt;<br />  &lt;/WorksheetOptions&gt;<br /> &lt;/Worksheet&gt;<br />&lt;/Workbook&gt;<br />

&lt;?xml version="1.0" encoding="utf-16"?&gt;<br />&lt;ShowPlanXML xmlns<img src='/community/emoticons/emotion-12.gif' alt=':x' />si="http://www.w3.org/2001/XMLSchema-instance" xmlns<img src='/community/emoticons/emotion-12.gif' alt=':x' />sd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.2153.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"&gt;<br /> &lt;BatchSequence&gt;<br /> &lt;Batch&gt;<br /> &lt;Statements&gt;<br /> &lt;StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.0032831" StatementText="SELECT ID2 FROM C2 WHERE ID1 = 1#xD;#xA;#xD;#xA;" StatementType="SELECT"&gt;<br /> &lt;StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" /&gt;<br /> &lt;QueryPlan CachedPlanSize="9"&gt;<br /> &lt;RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Test2]" Schema="[dbo]" Table="[C2]" Column="ID2" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Test2]" Schema="[dbo]" Table="[C2]" Column="ID2" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Test2]" Schema="[dbo]" Table="[C2]" Index="[PK_C2]" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Test2]" Schema="[dbo]" Table="[C2]" Column="ID1" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[@1],0)"&gt;<br /> &lt;Convert DataType="int" Style="0" Implicit="true"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Column="@1" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Convert&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />arameterList&gt;<br /> &lt;ColumnReference Column="@1" ParameterCompiledValue="(1)" /&gt;<br /> &lt;/ParameterList&gt;<br /> &lt;/QueryPlan&gt;<br /> &lt;/StmtSimple&gt;<br /> &lt;/Statements&gt;<br /> &lt;/Batch&gt;<br /> &lt;/BatchSequence&gt;<br />&lt;/ShowPlanXML&gt;