Re: SQL Fun

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!!

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

--
ThePsyko
Public Enemy #7



.