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?
