1

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>
Improve this question
6
  • "I am looking for options to reduce those Key Lookups." How are you sure that this (the Key Lookups) is your problem? I think it would be better if you posted the actual query with the execution plan and more info (how slow is it, what other tables are joined, how many rows are returned, etc.)
    – ypercubeᵀᴹ
    Commented May 24, 2016 at 8:57
  • How many times the lookup is looking up? Did you try, before restructuring the db, to use HASH hint in joins?
    – user_0
    Commented May 24, 2016 at 9:12
  • I could cry. The REAL query I relate to is part of a 350 lines query joining about 30 tables and completely messing up. I can't post this. Since I can't change it I am trying to go through it and optimize indexes of each table step by step. It has 25 Key Lookups that are notified as expensive. Table above is one of those. So I have no explicit small query available. As usual in my environment :(
    – Magier
    Commented May 24, 2016 at 9:37
  • Do you have any scope to add a column to this table? An INT would make a much better candidate for a clustered index.
    – Thomas Pullen
    Commented May 24, 2016 at 10:13
  • 1
    yes, it can have benefits, like ensuring all inserts are sequential at the end of the table. GUIDs (uniqueidentifiers) in general make terrible clustering keys. You can have a nonclustered Primary Key, don't forget. You could cluster on an identity and change the PK to be nonclustered, for instance.
    – Thomas Pullen
    Commented May 24, 2016 at 12:18

1 Answer 1

Reset to default
5

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.