Re: SQL Fun

On 11 Jan 2007 I stormed the castle called alt.2600 and heard
PerfectReign cry out in news:50njmtF1grd76U1@xxxxxxxxxxxxxxxxxxxxx

On Thu, 11 Jan 2007 18:48:33 +0000, ThePsyko got out the hammer and
chisel and etched in the wall:

On 11 Jan 2007 I stormed the castle called alt.2600 and heard
PerfectReign cry out in news:50n988F1gn6nbU6@xxxxxxxxxxxxxxxxxxxxx

On Thu, 11 Jan 2007 16:48:16 +0000, ThePsyko got out the hammer and
chisel and etched in the wall:

On 11 Jan 2007 I stormed the castle called alt.2600 and heard
PerfectReign cry out in news:50n5ccF1gn6nbU2@xxxxxxxxxxxxxxxxxxxxx

In case anyone's curious, we just found something interesting.

SQL Server 2000 on a dual processor server with 20GB RAM.

Given a table with 138M rows, and a GUID being the PK...

If you add a clustered index to the GUID and another row (document
number being a YYYY####### format) then you search on a range of
numbers, the processor will execute between 3000 and 6000 logical
reads to get the results. Time to execute takes anywhere from 5
seconds to 30 seconds.

Now, on another identical server you change the clustered index on
the GUID to non-clustered and keep the index on the document
number, the same search will yeald results in 0.01 seconds
requiring ~200 logical reads.

Go figure!

Amazing what you find out.



Good to know.. thanks!!

np


(not that any of my tables are anywhere NEAR that large but
still....
:)


I thought that was about half the size of your pr0n collection
database.

Am I wrong?




lol.. umm... errr.... :)


heh!

Oh, and we just found out that roughly 270,000 of the 500,000 rolls of
labels we ordered from Dymo were manufactured incorrectly. The
spindle is about 1/4" too short, and the labels are not feeding
correctly.

Grrr....

...should have insisted on Zebra!




ouch... more than half????!?!

--
ThePsyko
Public Enemy #7



.