-Parameter-Sniffing-in-SQL-Server/ExecPlanProcSmallAmountRecompile.sqlplan at main · Bahar-sqlserver/-Parameter-Sniffing-in-SQL-Server · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.539" Build="15.0.2000.5" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="3" StatementEstRows="116.859" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.00341054" StatementText="SELECT OrderID, OrderDate, Amount&#xD;&#xA;    FROM dbo.Orders&#xD;&#xA;    WHERE CustomerID = @CustomerID&#xD;&#xA;    OPTION (RECOMPILE)" StatementType="SELECT" QueryHash="0xDF09DB78D4AF022E" QueryPlanHash="0x5945165A725F77EA" RetrievedFromCache="false" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="MaxDOPSetToOne" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="128">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1645522" EstimatedPagesCached="102845" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="5901592" />
            <OptimizerStatsUsage>
              <StatisticsInfo Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Statistics="[IX_Orders_CustomerID]" ModificationCount="0" SamplingPercent="100" LastUpdate="2025-12-27T23:25:13.28" />
            </OptimizerStatsUsage>
            <TraceFlags IsCompileTime="true">
              <TraceFlag Value="1117" Scope="Global" />
              <TraceFlag Value="1118" Scope="Global" />
            </TraceFlags>
            <TraceFlags IsCompileTime="false">
              <TraceFlag Value="1117" Scope="Global" />
              <TraceFlag Value="1118" Scope="Global" />
            </TraceFlags>
            <QueryTimeStats CpuTime="0" ElapsedTime="0" />
            <RelOp AvgRowSize="23" EstimateCPU="0.000285545" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="116.859" EstimatedRowsRead="116.859" LogicalOp="Index Seek" NodeId="0" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00341054" TableCardinality="1000000">
              <OutputList>
                <ColumnReference Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Column="OrderID" />
                <ColumnReference Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Column="OrderDate" />
                <ColumnReference Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Column="Amount" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="115" ActualRowsRead="115" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
              </RunTimeInformation>
              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Column="OrderID" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Column="OrderDate" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Column="Amount" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Index="[IX_Orders_CustomerID]" IndexKind="NonClustered" Storage="RowStore" />
                <SeekPredicates>
                  <SeekPredicateNew>
                    <SeekKeys>
                      <Prefix ScanType="EQ">
                        <RangeColumns>
                          <ColumnReference Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Column="CustomerID" />
                        </RangeColumns>
                        <RangeExpressions>
                          <ScalarOperator ScalarString="(895)">
                            <Const ConstValue="(895)" />
                          </ScalarOperator>
                        </RangeExpressions>
                      </Prefix>
                    </SeekKeys>
                  </SeekPredicateNew>
                </SeekPredicates>
              </IndexScan>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>