SQL Server Performance

What to do if a specific query is slow or erratic

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by joechang, Feb 16, 2007.

  1. joechang New Member

    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


  2. joechang New Member

    &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=':eek:' />ffice<img src='/community/emoticons/emotion-7.gif' alt=':s' />preadsheet"<br /> xmlns<img src='/community/emoticons/emotion-3.gif' alt=':eek:' />="urn<img src='/community/emoticons/emotion-7.gif' alt=':s' />chemas-microsoft-com<img src='/community/emoticons/emotion-3.gif' alt=':eek:' />ffice<img src='/community/emoticons/emotion-3.gif' alt=':eek:' />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=':eek:' />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=':eek:' />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=':eek:' />ffice<img src='/community/emoticons/emotion-3.gif' alt=':eek:' />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=':eek:' />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 />
  3. Luis Martin Moderator

    I changed this to Sticky.

    If moderators don't think so, let me know.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  4. joechang New Member

    &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;

Share This Page