|
|
Posted by Erland Sommarskog on 09/29/80 11:30
(gilles27@talk21.com) writes:
> I'm sure Gert-Jan is correct in what he states, however because the
> most likely filtering on OrderLines is by Ledger and OrderNumber, those
> two should come before OrderLineNumber in the primary key.
>
> I could ask the customer to demonstrate their findings but my pride
> prevents me from admitting to them that I don't know what they mean by
> "reduced index reads by a factor of 5". :-)
You don't? Good, then we are two!
But OK, let's say you have this query:
SELECT * FROM tbl WHERE Ledger = @ledger AND OrderNumber = @nbr
The key reorder certainly gave a drastic relief on the poor index. Rather
than wearing thing on the poor index pages, you get a table scan instead.
Or at least scan for a Ledger.
Anyway, what it's important in the end is wallclock time. The total number
of reads is a good indication, when wallclock time varies due to other
queries, blocking etc. But index reads alone is not of interest.
Ah, there is of course the interesting issue of what happens at INSERT.
Now, with the proper PK, and if this key is clustered, thanks to the Ledger
column, there will be six hot spots in this table, and there will be
plenty of page splits. If you change the order, the picture will be
different, and INSERTs will spread all over. Both will lead to
fragmentation. If you want to avoid this, best is probably to keep the
PK non-clustered and cluster on OrderDate, or even on an IDENTITY column.
Then again, if there are proper maintenance windows for frequent reindexing,
this is not an issue.
> I need some of way checking that my changes to primary keys are
> actually having a positive effect, if I could compare the number of
> index reads before and after modifying the table, that would help.
Rather take typical queries like
SELECT * FROM tbl WHERE Ledger = @ledger AND OrderNumber = @nbr
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
[Back to original message]
|