Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Convert Non Clustered PKs to Clustered Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 2:36 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:33 PM
Points: 2,208, Visits: 3,323
Btw, it's always frustrated me that SQL Server can't seem to read only the non-leaf pages of a clustered index. If SQL needs only key column(s), why does it read all the leaf pages of the table as well?? That's an incredible waste of I/O.

UDB/DB2 can do it, why not SQL Server?


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1405019
Posted Wednesday, January 9, 2013 2:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 23, 2013 1:09 PM
Points: 9, Visits: 19


Additionally, the PK index is now larger (potentially much larger) because it contains all columns. So if you have queries that only needed the PK key columns, then it now has to read a lot more data. This particular scenario can be fixed by adding a replacement nonclustered index on those columns ... though I would evaluate indexing as a whole, because there may be 1 or 2 additional columns you could add that would make it suitable for even more queries.[/quote]

let me just get this straight theni think ive got what i need to do.

Column A PK

If my query needs this column then create an additional non clustered column A index . Then add more covering indexes on the non-clustered index to satisfy more queries when possible.

This will prevent sql using the large clustered pk, it will then use non clustered pk and satisfy multiple queries if possible.

This solution will get rid of the heap and prevent using the larger pk for queries.

Data willnow be ordered on disk get rid of fragmentation as thi will now be sorted by reindexing jobs
Post #1405025
Posted Wednesday, January 9, 2013 3:13 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:32 PM
Points: 1,618, Visits: 1,550
robert.nesta123 (1/9/2013)


let me just get this straight theni think ive got what i need to do.

Column A PK

If my query needs this column then create an additional non clustered column A index . Then add more covering indexes on the non-clustered index to satisfy more queries when possible.

This will prevent sql using the large clustered pk, it will then use non clustered pk and satisfy multiple queries if possible.


It depends on the queries. Don't automatically make that change. You have to evaluated whether it is the right decision or not.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1405040
Posted Wednesday, January 9, 2013 10:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
ScottPletcher (1/9/2013)
Don't automatically assume the PK makes the best, or even a good, clustered index, in particular if the PK is an identity column.


Considering that the IDENTITY column is frequently the only column that meets the best conditions of "narrow, unique, and ever increasing", I'm not sure why you'd say such a thing. At best, "It Depends".


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1405169
Posted Wednesday, January 9, 2013 11:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 40,208, Visits: 36,617
ScottPletcher (1/9/2013)
If SQL needs only key column(s), why does it read all the leaf pages of the table as well?? That's an incredible waste of I/O.


The only place that all the rows are stored is at the leaf level of an index. All that's at the non-leaf levels (any index, clustered or nonclustered) is the key columns for the first row on each page of the level below, hence there's no way to get at the key columns without reading the leaf level.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1405186
Posted Thursday, January 10, 2013 8:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:33 PM
Points: 2,208, Visits: 3,323
Jeff Moden (1/9/2013)
ScottPletcher (1/9/2013)
Don't automatically assume the PK makes the best, or even a good, clustered index, in particular if the PK is an identity column.


Considering that the IDENTITY column is frequently the only column that meets the best conditions of "narrow, unique, and ever increasing", I'm not sure why you'd say such a thing. At best, "It Depends".



Because I'm more concerned about overall performance than following a default, simplistic "rule".

The best clus key does indeed "depend", so it's malpractice to just assume an identity column automatically makes even a good clustering key. The vast majority of the time it does not make the best clus key.

For a PK, it's fine. And if you need it, a non-clus index on identity values works great to do single identity lookups.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1405492
Posted Monday, February 11, 2013 9:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 11:40 PM
Points: 4, Visits: 226
GilaMonster (1/9/2013)
ScottPletcher (1/9/2013)
If SQL needs only key column(s), why does it read all the leaf pages of the table as well?? That's an incredible waste of I/O.


The only place that all the rows are stored is at the leaf level of an index. All that's at the non-leaf levels (any index, clustered or nonclustered) is the key columns for the first row on each page of the level below, hence there's no way to get at the key columns without reading the leaf level.


Does it mean that the below two queries are roughly the same from performance point of view?
A. A SELECT query on the key columns of a clustered index.
B. A SELECT query on the key columns of a clustered index plus a couple more nonkey columns.

I might be totally wrong here, but couldnt resist asking this question.

Thanks,
Akshay.
Post #1418741
Posted Tuesday, February 12, 2013 8:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:33 PM
Points: 2,208, Visits: 3,323
akshay.pawar123 (2/11/2013)
GilaMonster (1/9/2013)
ScottPletcher (1/9/2013)
If SQL needs only key column(s), why does it read all the leaf pages of the table as well?? That's an incredible waste of I/O.


The only place that all the rows are stored is at the leaf level of an index. All that's at the non-leaf levels (any index, clustered or nonclustered) is the key columns for the first row on each page of the level below, hence there's no way to get at the key columns without reading the leaf level.


Does it mean that the below two queries are roughly the same from performance point of view?
A. A SELECT query on the key columns of a clustered index.
B. A SELECT query on the key columns of a clustered index plus a couple more nonkey columns.

I might be totally wrong here, but couldnt resist asking this question.

Thanks,
Akshay.



Yes, exactly: that is indeed the point. Unlike with an identity clus, where you have to design and build covering indexes for (almost) every (major) query, then when you add one column the covering index isn't, a cluster is by definition always covering, so once you supply a keyed lookup/range on the clus index, you can add column references w/o ever forcing a table scan.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1418991
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse