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

What I want to know is: Why do you need to "clean TempDB?" TempDB is just that, temporary! Objects created there disappear when their creating SPID disconnects. If this isn't...

An odd bit of behaviour I've noted with dropping databases is that, if the database is OffLine when you drop it, the physical files are not deleted.

Does anyone know why...

Besides the fact that the code is really very inefficiently (dare I say badly) written, the first obvious thing that could be causing the deadlock is the fact that there...