Some other ideas:

SELECT *

FROM (

SELECT *,

...

Jeff Moden (8/13/2010)

---

Paul White NZ (8/12/2010)

---

What sort of hint did you have in mind? Index hints don't work with table variables, and you can't give the PRIMARY KEY or...

Hey Arjun,

There are a few important things here that determine the fastest method:

First, you must avoid an explicit sort operation if possible. In your first script (where the clustered...

I think we'll just have to agree to disagree on this one 🙂

dan.nutley (8/12/2010)

---

I don't think it matters very much...the CPU (assuming you've got any Xeon made within the last 5 years) is rarely the bottleneck on such systems. Take a...

Then we...

If you're running Enterprise Edition, consider setting up an archiving scheme based on partitioning. It's much easier, faster, and better!

http://www.sqlskills.com/resources/whitepapers/partitioning%20in%20sql%20server%202005%20beta%20ii.htm

Nah, the Rugby Tri-Nations is about halfway through already 🙂

SSC answers appear to use RAID 1 :laugh:

Well that shows that SQL Server is using just over 11GB, and is prepared to use up to 20GB.

Where exactly are you reading the Task Manager figure from, and what...

You can also use a DMV:

select [object_name],

counter_name,

cntr_value,

...

You probably will want...

No - the AWE setting is ignored by x64 SQL Server. What about Michael's suggestion?...

Michael Valentine Jones (8/13/2010)

---

OP might just have SQL Server configured to not use more that 4 GB of memory.

Or the databases are small enough that SQL Server doesn't need more...

It's all too easily done -the install media doesn't exactly shout...