There's no simple or right answer here, as the answers are based on your environment and requirements.

4 things need to be taken into account when considering best practice and the...

An index scan isn't the same as a table scan, unless you are looking at the cluster index, in which case they could be the same, since the cluster index...

Since the SQL Server Binaries etc are not on shared disk, you will need to install SQL 2005 and SP3 on the 2nd node. But you would be doing this...

You can also download MAP 5.0. This is the Microsoft Assesment and Planning Toolkit. It's free and does a comprehensive investigation of your environment. You pretty much point it at...

There are so many possibilities here, and it may not be possible for the forum to help you as they can't get enough detail and aren't hands on. You may...

Not true, Cost Threshold will play a role if MAXDOP is NOT EQUAL TO 1.

This is true...

chris.lawrie (8/11/2010)

---

user query times in the diary are about 20secs and the query time using sql profiler was only about 2 secs.

On looking into the Task Manager on the Server...

A neat trick is to always...

Cost Threshold is instance wide and doesn't have a query hint option, you can see the value if you do an sp_configure.

I would assume if you set MAXDOP = 0...

Have a read here: http://technet.microsoft.com/en-us/library/ms188603.aspx

Leo

GilaMonster (8/10/2010)

---

Just one comment - cost threshold is not measured in...

MAXDOP is not a flag, it's a parameter that can be set from 0 - 64, reperesenting the number of threads that can be created in a parallel query.

MAXDOP =...

When you say you have MAXDOP enabled, I assume you mean you have it set to MAXDOP = 0. This is the default and it means SQL will use as...

Tara-1044200 (8/8/2010)

---

I am using the following code to run the update in batched so that it wont take full log space,please verify if this is really doing transactions in batches...

Have you tried running the load with PerfMon running? Check disk IO activity, waits, read/writes per second, CPU usage, Memory Usage, Buffer Cache hit ration.

Loading 4 million rows at 400k...