Archive

Archive for December, 2009

Using the missing index feature of SQL 2008

December 16, 2009 Leave a comment
I spotted a new handy feature of the SQL 2008 Execution execution plan tool, called "Missing Indexes".

I had a complaint from an affiliate that basically boiled down to a SQL statement taking more than 30 seconds to complete, passing
it’s time-out and returning garbage to the screen.

When a statement ‘sometimes’ takes longer than usual, this points to SQL’s memory cache being empty prior to a command execution,
which you can forcibly re-create using dbcc dropcleanbuffers.

So, here was the statement:

             select count(*) as PremiumSMS from triggers t             
             join
             (
                select distinct originator from ReceivedTextMessages
                where MessageText not like ‘REPORT%’
                and originator<>”
             ) rtm on rtm.originator=substring(t.recipient,3,20)
             and AffiliateID=xxx

True, quite alot of slow string comparisons, but I can’t fundamentally change the database information, just work with what I’ve got.

Sure enough it runs in 35 seconds, after a cache purge, enough to pass the timeout. I tried interchanging the last "and" for a "where", but
only got a marginal increase. So, I used the Execution plan, and saw this (top image).

Note the green text "Missing Index", Right clicking on this, then click "show missing index details" creates the index creation statement:

USE [ReceivedTextMessages]
GO
CREATE NONCLUSTERED INDEX [idx_Originator]
ON [dbo].[receivedTextMessages] ([Originator])
INCLUDE ([MessageText])
GO

And, again, clicking Execution plan, then missing index again, I got:

USE [Triggers]
GO
CREATE NONCLUSTERED INDEX [idx_AffiliateID_Recipient]
ON [dbo].[triggers] ([affiliateID])
INCLUDE ([recipient])
GO

I ran the query again after a SQL purge and got 28 seconds on first run, and 6 seconds on subsequent runs.

So, under the 30 second deadline, but only just!.


This is the full details of the execution plan for those interested:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot; xmlns:xsd="http://www.w3.org/2001/XMLSchema&quot; Version="1.0" Build="9.00.2047.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"&gt;
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="32.2463" StatementText="select count(*) as PremiumSMS from triggers t                            join              (                 select distinct originator from ReceivedTextMessages                 where MessageText not like ‘REPORT%’                 and originator&lt;&gt;”              ) rtm on rtm.originator=substring(t.recipient,3,20)              and AffiliateID=826" StatementType="SELECT">
          <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
          <QueryPlan CachedPlanSize="48">
            <MissingIndexes>
              <MissingIndexGroup Impact="26.7997">
                <MissingIndex Database="[ReceivedTextMessages]" Schema="[dbo]" Table="[receivedTextMessages]">
                  <ColumnGroup Usage="INEQUALITY">
                    <Column Name="[Originator]" ColumnId="10" />
                  </ColumnGroup>
                  <ColumnGroup Usage="INCLUDE">
                    <Column Name="[MessageText]" ColumnId="12" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
              <MissingIndexGroup Impact="51.5466">
                <MissingIndex Database="[Triggers]" Schema="[dbo]" Table="[triggers]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[affiliateID]" ColumnId="3" />
                  </ColumnGroup>
                  <ColumnGroup Usage="INCLUDE">
                    <Column Name="[recipient]" ColumnId="8" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
              <MissingIndexGroup Impact="34.7633">
                <MissingIndex Database="[ReceivedTextMessages]" Schema="[dbo]" Table="[receivedTextMessages]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[Originator]" ColumnId="10" />
                  </ColumnGroup>
                  <ColumnGroup Usage="INCLUDE">
                    <Column Name="[MessageText]" ColumnId="12" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
            </MissingIndexes>
            <RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="32.2463">
              <OutputList>
                <ColumnReference Column="Expr1006" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1006" />
                    <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[globalagg1009],0)">
                      <Convert DataType="int" Style="0" Implicit="true">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="globalagg1009" />
                          </Identifier>
                        </ScalarOperator>
                      </Convert>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="15" EstimateCPU="0.000478573" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="32.2463">
                  <OutputList>
                    <ColumnReference Column="globalagg1009" />
                  </OutputList>
                  <StreamAggregate>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="globalagg1009" />
                        <ScalarOperator ScalarString="SUM([partialagg1008])">
                          <Aggregate AggType="SUM" Distinct="false">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Column="partialagg1008" />
                              </Identifier>
                            </ScalarOperator>
                          </Aggregate>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="15" EstimateCPU="0.177508" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="796.789" LogicalOp="Aggregate" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="32.2458">
                      <OutputList>
                        <ColumnReference Column="partialagg1008" />
                      </OutputList>
                      <MemoryFractions Input="0" Output="0" />
                      <Hash>
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Column="partialagg1008" />
                            <ScalarOperator ScalarString="ANY([partialagg1008])">
                              <Aggregate AggType="ANY" Distinct="false">
                                <ScalarOperator>
                                  <Identifier>
                                    <ColumnReference Column="partialagg1008" />
                                  </Identifier>
                                </ScalarOperator>
                              </Aggregate>
                            </ScalarOperator>
                          </DefinedValue>
                        </DefinedValues>
                        <HashKeysBuild>
                          <ColumnReference Column="Expr1007" />
                        </HashKeysBuild>
                        <BuildResidual>
                          <ScalarOperator ScalarString="[Expr1007] = [Expr1007]">
                            <Compare CompareOp="IS">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Column="Expr1007" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Column="Expr1007" />
                                </Identifier>
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </BuildResidual>
                        <RelOp AvgRowSize="29" EstimateCPU="4.44944" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="21302.6" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="32.0683">
                          <OutputList>
                            <ColumnReference Column="Expr1007" />
                            <ColumnReference Column="partialagg1008" />
                          </OutputList>
                          <MemoryFractions Input="1" Output="1" />
                          <Hash>
                            <DefinedValues />
                            <HashKeysBuild>
                              <ColumnReference Column="Expr1007" />
                            </HashKeysBuild>
                            <HashKeysProbe>
                              <ColumnReference Database="[ReceivedTextMessages]" Schema="[dbo]" Table="[receivedTextMessages]" Column="Originator" />
                            </HashKeysProbe>
                            <ProbeResidual>
                              <ScalarOperator ScalarString="[ReceivedTextMessages].[dbo].[receivedTextMessages].[Originator]=[Expr1007]">
                                <Compare CompareOp="EQ">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[ReceivedTextMessages]" Schema="[dbo]" Table="[receivedTextMessages]" Column="Originator" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="Expr1007" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </ProbeResidual>
                            <RelOp AvgRowSize="29" EstimateCPU="1.09401" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="796.789" LogicalOp="Aggregate" NodeId="5" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="17.7783">
                              <OutputList>
                                <ColumnReference Column="Expr1007" />
                                <ColumnReference Column="partialagg1008" />
                              </OutputList>
                              <MemoryFractions Input="0" Output="0" />
                              <Hash>
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="partialagg1008" />
                                    <ScalarOperator ScalarString="COUNT(*)">
                                      <Aggregate AggType="COUNT*" Distinct="false" />
                                    </ScalarOperator>
                                  </DefinedValue>
                                </DefinedValues>
                                <HashKeysBuild>
                                  <ColumnReference Column="Expr1007" />
                                </HashKeysBuild>
                                <BuildResidual>
                                  <ScalarOperator ScalarString="[Expr1007] = [Expr1007]">
                                    <Compare CompareOp="IS">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Column="Expr1007" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Column="Expr1007" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </BuildResidual>
                                <RelOp AvgRowSize="25" EstimateCPU="0.191283" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="158718" LogicalOp="Compute Scalar" NodeId="6" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="15.7661">
                                  <OutputList>
                                    <ColumnReference Column="Expr1007" />
                                  </OutputList>
                                  <ComputeScalar>
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Column="Expr1007" />
                                        <ScalarOperator ScalarString="substring([Triggers].[dbo].[triggers].[recipient],(3),(20))">
                                          <Intrinsic FunctionName="substring">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Database="[Triggers]" Schema="[dbo]" Table="[triggers]" Column="recipient" />
                                              </Identifier>
                                            </ScalarOperator>
                                            <ScalarOperator>
                                              <Const ConstValue="(3)" />
                                            </ScalarOperator>
                                            <ScalarOperator>
                                              <Const ConstValue="(20)" />
                                            </ScalarOperator>
                                          </Intrinsic>
                                        </ScalarOperator>
                                      </DefinedValue>
                                    </DefinedValues>
                                    <RelOp AvgRowSize="28" EstimateCPU="2.10427" EstimateIO="13.4705" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="158718" LogicalOp="Clustered Index Scan" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="15.5748">
                                      <OutputList>
                                        <ColumnReference Database="[Triggers]" Schema="[dbo]" Table="[triggers]" Column="recipient" />
                                      </OutputList>
                                   

Categories: Uncategorized