Sql-server – Should I remove this clustered index

clustered-indexindexnonclustered-indexsql serversql-server-2008-r2

I have lots of tables designed similar as the following (slightly simplified) sample.
The original table on different servers has between 50,000 and 200,000 rows (around 12,000 – 150,000 pages).

CREATE TABLE [dbo].[PWS] (
    [ID]             UNIQUEIDENTIFIER NOT NULL,
    [PWFID]     UNIQUEIDENTIFIER NOT NULL,
    [SuID]         UNIQUEIDENTIFIER NULL,
    [Title]         varchar(500),
    [SITime]     DATETIME         NULL,
    [Status]      INT,
    [ActionUserID]                UNIQUEIDENTIFIER NULL,
 --   […]
    CONSTRAINT [PK_PWS] PRIMARY KEY NONCLUSTERED ([ID] ASC) WITH (FILLFACTOR = 90),
    CONSTRAINT [FK_PWS_PW] FOREIGN KEY ([PWFID]) REFERENCES [dbo].[PW] ([ID]) ON DELETE CASCADE NOT FOR REPLICATION,
    CONSTRAINT [FK_PWS_Status] FOREIGN KEY ([Status]) REFERENCES [dbo].[Status] ([Status]) NOT FOR REPLICATION
);

CREATE CLUSTERED INDEX [IX_Clustered]
    ON [dbo].[PWS]([PWFID] ASC) WITH (FILLFACTOR = 90);

So those tables use a uniqueidentifier as primary key on a nonclustered index and another uniqueidentifier that is not unique as a clustered index.
JOINS usually happen on this clustered key.

Unfortunately most of the queries are not satisfied with joining but also include more of the columns into the output byselecting them.

The sampe I actually look at does:

  • Join on: PWFID
  • Select: Title,SITime

This leads to using the clustering index followed by Key Lookups. Now during the process of reviewing all those queries and trying to optimize them I am looking for options to reduce those Key Lookups. So right I am in the middle of the discussion: clustering index or not.

Since having the primary key next to the clustered index, I already have two indexes on this table that are more or less "useless", since the query now joining and getting data can not be covered – and also I can't even adjust the indexes already in place to cover them. Instead I need another additional nonclustered index on this table to cover the query.

This makes me wonder if getting rid of the clustered index and make this table a heap makes sense or not, because I can just "save" the clustered index and free some space for having a covering nonclustered index?

enter image description here

<?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.1" Build="10.50.6000.34" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="20" StatementEstRows="1.00059" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0131459" StatementText="SELECT&#xD;&#xA;Object1.Column1 ,&#xD;&#xA;Object1.Column2,&#xD;&#xA;Object2.Column3,&#xD;&#xA; Object2.Column4 ,&#xD;&#xA; Object2.Column5 &#xD;&#xA;From Object1 &#xD;&#xA;LEFT JOIN&#xD;&#xA;Object2 ON Object2.Column2 = (SELECT TOP ? Column2 FROM Object2 WHERE Column6=Object1.Column2 AND Column7=Variable1)&#xD;&#xA;&#xD;&#xA;WHERE &#xD;&#xA;Object1.Column1  = ?&#xD;&#xA;AND Object1.Column8=Variable2 AND Object1.Column7=Variable1&#xD;&#xA;order by Object1.Column1 " StatementType="SELECT" QueryHash="0x48743CD92ACB6E34" QueryPlanHash="0xC3C3C48F42E94CC5">
          <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="1" CachedPlanSize="40" CompileTime="4" CompileCPU="4" CompileMemory="544">
            <RelOp AvgRowSize="72" EstimateCPU="4.18248E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.00059" LogicalOp="Left Outer Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0131459">
              <OutputList>
                <ColumnReference Database="Database1" Schema="Schema1" Table="Object1" Column="Column2" />
                <ColumnReference Database="Database1" Schema="Schema1" Table="Object1" Column="Column1" />
                <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column5" />
                <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column4" />
                <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column3" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <NestedLoops Optimized="false">
                <OuterReferences>
                  <ColumnReference Database="Database1" Schema="Schema1" Table="Object1" Column="Column2" />
                </OuterReferences>
                <RelOp AvgRowSize="39" EstimateCPU="0.000158101" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.00059" LogicalOp="Index Seek" NodeId="1" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="527433">
                  <OutputList>
                    <ColumnReference Database="Database1" Schema="Schema1" Table="Object1" Column="Column2" />
                    <ColumnReference Database="Database1" Schema="Schema1" Table="Object1" Column="Column1" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="Database1" Schema="Schema1" Table="Object1" Column="Column2" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="Database1" Schema="Schema1" Table="Object1" Column="Column1" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="Database1" Schema="Schema1" Table="Object1" Index="Index1" IndexKind="NonClustered" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Database="Database1" Schema="Schema1" Table="Object1" Column="Column7" />
                              <ColumnReference Database="Database1" Schema="Schema1" Table="Object1" Column="Column8" />
                              <ColumnReference Database="Database1" Schema="Schema1" Table="Object1" Column="Column1" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="ScalarString1">
                                <Identifier>
                                  <ColumnReference Column="Column9" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator ScalarString="ScalarString2">
                                <Identifier>
                                  <ColumnReference Column="Column10" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator ScalarString="ScalarString3">
                                <Const ConstValue="Value3" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
                <RelOp AvgRowSize="40" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0.000594258" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00985859">
                  <OutputList>
                    <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column5" />
                    <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column4" />
                    <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column3" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <NestedLoops Optimized="false">
                    <OuterReferences>
                      <ColumnReference Column="Column11" />
                      <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column6" />
                    </OuterReferences>
                    <RelOp AvgRowSize="27" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0.000594258" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657121">
                      <OutputList>
                        <ColumnReference Column="Column11" />
                        <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column6" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <NestedLoops Optimized="false">
                        <OuterReferences>
                          <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column2" />
                        </OuterReferences>
                        <RelOp AvgRowSize="23" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0.000594258" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="4" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00328382">
                          <OutputList>
                            <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column2" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
                          </RunTimeInformation>
                          <Top RowCount="false" IsPercent="false" WithTies="false">
                            <TopExpression>
                              <ScalarOperator ScalarString="ScalarString4">
                                <Const ConstValue="Value4" />
                              </ScalarOperator>
                            </TopExpression>
                            <RelOp AvgRowSize="27" EstimateCPU="0.000158127" EstimateIO="0.003125" EstimateRebinds="0.000594258" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00328323" TableCardinality="837182">
                              <OutputList>
                                <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column2" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
                              </RunTimeInformation>
                              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column2" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="Database1" Schema="Schema1" Table="Object2" Index="Index2" TableReferenceId="2" IndexKind="Clustered" />
                                <SeekPredicates>
                                  <SeekPredicateNew>
                                    <SeekKeys>
                                      <Prefix ScanType="EQ">
                                        <RangeColumns>
                                          <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column6" />
                                        </RangeColumns>
                                        <RangeExpressions>
                                          <ScalarOperator ScalarString="ScalarString5">
                                            <Identifier>
                                              <ColumnReference Database="Database1" Schema="Schema1" Table="Object1" Column="Column2" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </RangeExpressions>
                                      </Prefix>
                                    </SeekKeys>
                                  </SeekPredicateNew>
                                </SeekPredicates>
                                <Predicate>
                                  <ScalarOperator ScalarString="ScalarString6">
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column7" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Column="Column9" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </Predicate>
                              </IndexScan>
                            </RelOp>
                          </Top>
                        </RelOp>
                        <RelOp AvgRowSize="27" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0.000594258" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="7" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032832" TableCardinality="837182">
                          <OutputList>
                            <ColumnReference Column="Column11" />
                            <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column6" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                          </RunTimeInformation>
                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Column11" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column6" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="Database1" Schema="Schema1" Table="Object2" Index="Index3" TableReferenceId="1" IndexKind="NonClustered" />
                            <SeekPredicates>
                              <SeekPredicateNew>
                                <SeekKeys>
                                  <Prefix ScanType="EQ">
                                    <RangeColumns>
                                      <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column2" />
                                    </RangeColumns>
                                    <RangeExpressions>
                                      <ScalarOperator ScalarString="ScalarString7">
                                        <Identifier>
                                          <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column2" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </RangeExpressions>
                                  </Prefix>
                                </SeekKeys>
                              </SeekPredicateNew>
                            </SeekPredicates>
                          </IndexScan>
                        </RelOp>
                      </NestedLoops>
                    </RelOp>
                    <RelOp AvgRowSize="40" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0.000594258" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032832" TableCardinality="837182">
                      <OutputList>
                        <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column5" />
                        <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column4" />
                        <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column3" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                      </RunTimeInformation>
                      <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column5" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column4" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column3" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="Database1" Schema="Schema1" Table="Object2" Index="Index2" TableReferenceId="-1" IndexKind="Clustered" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column6" />
                                  <ColumnReference Column="Column11" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="ScalarString8">
                                    <Identifier>
                                      <ColumnReference Database="Database1" Schema="Schema1" Table="Object2" Column="Column6" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="ScalarString9">
                                    <Identifier>
                                      <ColumnReference Column="Column11" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </NestedLoops>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="Column10" ParameterRuntimeValue="Value1" />
              <ColumnReference Column="Column9" ParameterRuntimeValue="Value2" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Best Answer

Without further info, this is more of speculation but judging on what we have:

  • a table that is quite wide (1.3 to 4.0 rows per page on average)
  • the query that is slow is using:
    • only PWFID on the join condition,
    • two columns Title, SITime on the select list and
    • no other column anywhere (WHERE, HAVING etc.)

Then a covering non-clustered index on (PWFID) INCLUDE (SITime, Title) will probably improve the efficiency of the query as it will need to read a narrower index (and no lookups to the table, whether it's clustered or a heap). No idea how much improvement it would be, as the query involves joining of 30 something tables - and the index will not be that narrow either, with the included 500 character column.

About converting the table to a heap:

This makes me wonder if getting rid of the clustered index and make this table a heap makes sense or not, because I can just "save" the clustered index and free some space for having a covering nonclustered index?

This is irrelevant I think, at least for this and similar queries. It might change/improve the behaviour of insert queries (as no clustered index will have to be maintained) but it may also degrade performance for other queries that depend on finding more columns from the clustered index.

And you won't be saving any (or much) space. The data has to be stored somewhere, whether the table is a heap or clustered.

Adding a NC index is much less drastic change and I wouldn't expect any side effects - apart from the wanted use of it in the query - but still needs to be tested.

Removing the clustered index and converting a table to a heap is effectively changing the structure of all NC indexes and removing of (the clustered) one, so it may have several and more serious effects on many operations/queries performed and would need much more testing.

Related Question