-Parameter-Sniffing-in-SQL-Server/ExecPlanProcLargeAmountDynamic.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
69
70
71
72
73
74
75
76
<?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="5" StatementEstRows="400000" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="150" StatementSubTreeCost="1.39543" StatementText="SELECT OrderID, OrderDate, Amount&#xD;&#xA;          FROM dbo.Orders&#xD;&#xA;          WHERE CustomerID = @CustomerID" StatementType="SELECT" QueryHash="0xC2EA7D3111A953C1" QueryPlanHash="0x5945165A725F77EA" RetrievedFromCache="true" 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="1" CompileCPU="1" CompileMemory="128">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1645522" EstimatedPagesCached="102845" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="143112" />
            <OptimizerStatsUsage>
              <StatisticsInfo Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Statistics="[IX_Orders_CustomerID]" ModificationCount="0" SamplingPercent="100" LastUpdate="2025-12-27T23:32:03.58" />
            </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>
            <WaitStats>
              <Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="2353" WaitCount="1309" />
            </WaitStats>
            <QueryTimeStats CpuTime="365" ElapsedTime="2708" />
            <RelOp AvgRowSize="23" EstimateCPU="0.440157" EstimateIO="0.955273" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="400000" EstimatedRowsRead="400000" LogicalOp="Index Seek" NodeId="0" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="1.39543" 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="400000" ActualRowsRead="400000" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="211" ActualCPUms="211" ActualScans="1" ActualLogicalReads="1292" 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="[@CustomerID]">
                            <Identifier>
                              <ColumnReference Column="@CustomerID" />
                            </Identifier>
                          </ScalarOperator>
                        </RangeExpressions>
                      </Prefix>
                    </SeekKeys>
                  </SeekPredicateNew>
                </SeekPredicates>
              </IndexScan>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@CustomerID" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>