CompositeIndex_Selectivity_Optimization/EXECPLAN_IX_WRONG.sqlplan at main · Bahar-sqlserver/CompositeIndex_Selectivity_Optimization · 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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
<?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="1" StatementEstRows="4466" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="150" StatementSubTreeCost="4.50106" StatementText="SELECT * FROM [dbo].[Orders] WHERE [ShipCountry]=@1 AND [EmployeeID]=@2" StatementType="SELECT" QueryHash="0xA4CA05F8CDE95DCC" QueryPlanHash="0x23E16BD0EC37F9A0" 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="24" CompileTime="937" CompileCPU="930" CompileMemory="152">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1645522" EstimatedPagesCached="102845" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="6002168" />
            <OptimizerStatsUsage>
              <StatisticsInfo Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Statistics="[_WA_Sys_00000002_3C69FB99]" ModificationCount="0" SamplingPercent="25.4406" LastUpdate="2025-12-27T00:54:09.48" />
              <StatisticsInfo Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Statistics="[_WA_Sys_00000003_3C69FB99]" ModificationCount="0" SamplingPercent="25.4406" LastUpdate="2025-12-27T00:54:09.02" />
            </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="54" WaitCount="1" />
              <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="1" WaitCount="2618" />
            </WaitStats>
            <QueryTimeStats CpuTime="115" ElapsedTime="170" />
            <RelOp AvgRowSize="35" EstimateCPU="1.10016" EstimateIO="3.4009" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="4466" EstimatedRowsRead="1000000" LogicalOp="Clustered Index Scan" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="4.50106" TableCardinality="1000000">
              <OutputList>
                <ColumnReference Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Column="OrderID" />
                <ColumnReference Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Column="ShipCountry" />
                <ColumnReference Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Column="EmployeeID" />
                <ColumnReference Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Column="OrderDate" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="1007" ActualRowsRead="1000000" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="115" ActualCPUms="114" ActualScans="1" ActualLogicalReads="4606" ActualPhysicalReads="2" ActualReadAheads="3116" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
              </RunTimeInformation>
              <IndexScan Ordered="false" ForcedIndex="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="ShipCountry" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Column="EmployeeID" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Column="OrderDate" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Index="[PK__Orders__C3905BAFDE13B3FC]" IndexKind="Clustered" Storage="RowStore" />
                <Predicate>
                  <ScalarOperator ScalarString="[IndexLab].[dbo].[Orders].[EmployeeID]=CONVERT_IMPLICIT(int,[@2],0) AND [IndexLab].[dbo].[Orders].[ShipCountry]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)">
                    <Logical Operation="AND">
                      <ScalarOperator>
                        <Compare CompareOp="EQ">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Column="EmployeeID" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Column="ConstExpr1003">
                                <ScalarOperator>
                                  <Convert DataType="int" Style="0" Implicit="true">
                                    <ScalarOperator>
                                      <Identifier>
                                        <ColumnReference Column="@2" />
                                      </Identifier>
                                    </ScalarOperator>
                                  </Convert>
                                </ScalarOperator>
                              </ColumnReference>
                            </Identifier>
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                      <ScalarOperator>
                        <Compare CompareOp="EQ">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[IndexLab]" Schema="[dbo]" Table="[Orders]" Column="ShipCountry" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Column="ConstExpr1002">
                                <ScalarOperator>
                                  <Convert DataType="nvarchar" Length="8000" Style="0" Implicit="true">
                                    <ScalarOperator>
                                      <Identifier>
                                        <ColumnReference Column="@1" />
                                      </Identifier>
                                    </ScalarOperator>
                                  </Convert>
                                </ScalarOperator>
                              </ColumnReference>
                            </Identifier>
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </Logical>
                  </ScalarOperator>
                </Predicate>
              </IndexScan>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@2" ParameterDataType="tinyint" ParameterCompiledValue="(5)" ParameterRuntimeValue="(5)" />
              <ColumnReference Column="@1" ParameterDataType="varchar(8000)" ParameterCompiledValue="'USA'" ParameterRuntimeValue="'USA'" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>