Just a quick comment on the original post: ApexSQL is developing a debugger for 2005/2008 that blows away the one MS provides with 2008. One of it's better...

1) Use dynamic sql to execute the EXACT string that you would type into QA with NO variables. This will ensure proper query plan and partition elimination.

set quoted_identifier off

declare...

jvamvas (8/19/2008)

---

Stage I

DBCC UPDATEUSAGE

UPDATE STATISTICS (with FULL scan) for all tables

exec sp_recompile for all objects

...

I think Jeffery has the prime suggestion.

If that doesn't address the issue, take a look at the query plans of the affected queries and see if they can...

[quote-0I think i have phrased my question wrongly, Actually i know about Clsutered and Non Clustered index, i just wanted to know the below part.

uniquefier (since it is non-unique -...

karthikeyan (8/13/2008)

---

SQLGuru,

Clustered index: order_id (assume int - 4 bytes), order_date (assume datetime - 8 bytes), uniquefier (since it is non-unique - 4 bytes). So 16 bytes for CI

non-clustered...

If you do test it out, please report on the total page counts for each index. Remember - more to it than just seek/scan speed/efficiency. More pages mean...

GilaMonster (8/11/2008)

---

I did a nice long calculation after someone asked me a very similar question in my TechEd session. Turned out, with a 100000 rows table, changing a nonclustered index...

Pay a pro to remote into your box and tune the query for you, and then teach you how they did it. You will be going back and forth...

Old Hand: you have been - as I like to put it - flopping-and-twitching on this issue for what, 5 days + now. :w00t: May I recommend...

Kewl! You just stumbled across the fasted IO possible - IO that isn't performed at all! :hehe:

In the is scenario you might as well have the CLustered Index be your PK and I don't think it would need a uniqueifier because order_id has to be unique...

Yep. And then we have this situation:

Clustered index: order_id (assume int - 4 bytes), order_date (assume datetime - 8 bytes), uniquefier...

That is ALMOST the 'most correct' answer. 🙂 I think the best answer is to have a CLUSTERED index on order_id, order_date. ...

To my knowledge 400TB would be far and away the largest SQL Server database in existence. I suspect that is a typo, but if it isn't, you will need...