2

I just inherited some SQL Server 2008 R2 databases and have reason to be reviewing the indexes on some tables. For a table with two columns such as:

CREATE TABLE My_Table ( Obj_Id int NOT NULL,
    Related_GUID uniqueidentifier NOT NULL )  
ALTER TABLE My_Table ADD CONSTRAINT
    PK_My_Table PRIMARY KEY CLUSTERED (Obj_Id,Related_GUID)

When populated, the fragmentation tab of the index properties for PK_My_Table lists the average row size as 27.

Also on this table are some non-clustered indexes:

Create Index Ix_My_Table_1 on My_Table (Related_GUID)
Create Index Ix_My_Table_2 on My_Table (Obj_Id, Related_GUID)

Average row sizes for both of those is reported as 22, which is what I find really strange. Assuming we're talking about row data only and not the B-tree, and neglecting the issue of row overhead, then if the leaf node of the non-clustered indexes contains the index columns and the associated clustered index key, then shouldn't the lengths of the non-clustered index rows be at least:

Ix_My_Table_1 = Related_GUID (16) + Obj_Id (4) + Related_GUID (16) = 36
Ix_My_Table_2 = ( Related_GUID (16) + Obj_Id (4) ) X 2 = 40

And here's the kicker...

User Seeks:

PK_My_Table = 59,339
Ix_My_Table_1 = 68,182
Ix_My_Table_2 = 1,381,777

User Scans:

PK_My_Table = 0
Ix_My_Table_1 = 41,114
Ix_My_Table_2 = 1,343

My instinct is to drop the non-clustered indexes, but the evidence suggests they are better (more rows per page) and are obviously preferred by the optimizer over the clustered index. If the row size as reported by Management Studio is correct, then the usage of the non-clustered indexes makes sense, but how is this possible?

  • Did you account for the intermediate levels in your calculation? Did you account for fill factor? As for the second part of your question, since those two columns appear in the clustering key, the length of both NC indexes will be the same (~20 bytes plus some minor overhead), and probably can be dropped. – Randolph West Aug 8 '16 at 7:02
  • I did not take into account intermediate levels, fill factor, or even the amount of fragmentation. I guess a simpler version of my question would be "How is it possible for any non-clustered index on a table that consists only of a clustering key to have a smaller row size than that of the clustering key?" – Bill Roberts Aug 8 '16 at 12:23
  • Without looking at the pages directly, that's not easy to tell, especially in your examples. – Randolph West Aug 8 '16 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.