After tuning nearly every query in our app (of course a good thing) we still couldn't explain why even at what appeared to be relatively low CPU loads, the system...

I am on SP2 -CU3 for SQL 2008 I am referring to Windows 2008 R2 (SP1)

There is also a patch we are strongly looking at. http://support.microsoft.com/default.aspx?scid=kb;EN-US;976700

The reason why we are leaning towards the patch, is because I think everyone agrees, one bad query shouldn't...

I appreciate the knowledge and the time. I will state that since the changes Friday, we have only 1 query that still runs in parallel and that query most...

Almost exclusive OLTP.

counteroccurrencevalue

optimizations197374501

elapsed time197366460.0063553188317777

final cost1973664745.9971011158534

trivial plan73155451

tasks12421103556.496919637491

no plan0NULL

search 014025791

search 0 time14140600.0132889099472444

search 0 tasks14140601250.81451847871

search 1110162501

search 1 time112608820.003472970856102

search 1 tasks11260882449.037774305778

search 222741

search 2 time62730.155596046548701

search 2 tasks627313871.7344173442

gain stage 0 to stage 1112290.147846452882183

gain stage 1 to stage 22230.00432500828511371

timeout5672551

memory...

Bob Fazio (9/12/2011)

---

counteroccurrencevalue

optimizations690881

elapsed time690880.00212443550254748

final cost690887.09694068392164

trivial plan517621

tasks17326623.287082996652

no plan0NULL

search 063101

search 0 time63110.00500285216289019

search 0 tasks6311900.215496751703

search 1110041

search 1 time110160.00153567538126362

search 1 tasks11016464.412490922295

search 2121

search 2 time130.000307692307692308

search 2 tasks13141.846153846154

gain stage 0 to stage 110.00356762375769612

gain stage 1 to...

counteroccurrencevalue

optimizations690881

elapsed time690880.00212443550254748

final cost690887.09694068392164

trivial plan517621

tasks17326623.287082996652

no plan0NULL

search 063101

search 0 time63110.00500285216289019

search 0 tasks6311900.215496751703

search 1110041

search 1 time110160.00153567538126362

search 1 tasks11016464.412490922295

search 2121

search 2 time130.000307692307692308

search 2 tasks13141.846153846154

gain stage 0 to stage 110.00356762375769612

gain stage 1 to stage 20NULL

timeout271

memory...

http://weblogs.sqlteam.com/tarad/archive/2010/11/08/max-degree-of-parallelism-server-side-setting.aspx

FYI, for me it recommends 4, but as I said, I set to 6.

WaitTypeWait_SAvgWait_MSResource_SSignal_SWaitCountPercentage

CXPACKET25955.363.8222734.463220.90678771938.08

OLEDB17137.440.1417137.440.0012570371925.14

PAGEIOLATCH_SH8344.7426.258298.3446.4031791312.24

SOS_SCHEDULER_YIELD4127.010.1851.294075.72223269846.05

BACKUPIO2285.140.822273.7311.4127762383.35

WRITELOG1565.000.741444.10120.9021012872.30

LATCH_EX1354.820.161092.97261.8583067901.99

BACKUPTHREAD1235.28135.541235.010.2791141.81

ASYNC_IO_COMPLETION1017.534086.451017.520.012491.49

BACKUPBUFFER980.150.16900.9279.2263068881.44

ASYNC_NETWORK_IO936.730.30899.3837.3631182731.37

SQLTRACE_LOCK487.045.49465.6721.37887360.71

PREEMPTIVE_OS_AUTHENTICATIONOPS389.930.17389.930.0022847360.57

SLEEP_BPOOL_FLUSH328.392.93324.913.481119840.48

The event's usually last between 5-10 minutes.

However, I should have clarified the 4th number. The other three were ignorable waits. In fact, all in the top...

Update.

SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms desc

showed that CXPACKET was in the top 10 (actually 4th highest) waits.

So we concentrated on parallel queries.

First thing I found out is that...

One instance I found where this should have been the course of action recently.

We have SQL instances that are part of a critical application that we don't manage....

Sorry Carlos.. missed your post saying pretty much the same thing.

Figured it was worth adding. I actually came across an instance where I found a valid use for a regular backup to the nul: device.

I have a database where...