Viewing 15 posts - 1 through 15 (of 514 total)
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...
December 19, 2011 at 2:48 pm
GilaMonster (9/12/2011)
Frankly he should be on at least SP1, preferably SP2 regardless of problems....
I am on SP2 -CU3 for SQL 2008 I am referring to Windows 2008 R2 (SP1)
September 13, 2011 at 5:33 am
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...
September 12, 2011 at 1:38 pm
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...
September 12, 2011 at 11:12 am
counter occurrence value
optimizations 19737450 1
elapsed time 19736646 0.0063553188317777
final cost 19736647 45.9971011158534
trivial plan 7315545 1
tasks 12421103 556.496919637491
no plan 0 NULL
search 0 1402579 1
search 0 time 1414060 0.0132889099472444
search 0 tasks 1414060 1250.81451847871
search 1 11016250 1
search 1 time 11260882 0.003472970856102
search 1 tasks 11260882 449.037774305778
search 2 2274 1
search 2 time 6273 0.155596046548701
search 2 tasks 6273 13871.7344173442
gain stage 0 to stage 1 11229 0.147846452882183
gain stage 1 to stage 2 223 0.00432500828511371
timeout 567255 1
memory...
September 12, 2011 at 10:37 am
Bob Fazio (9/12/2011)
optimizations 69088 1
elapsed time 69088 0.00212443550254748
final cost 69088 7.09694068392164
trivial plan 51762 1
tasks 17326 623.287082996652
no plan 0 NULL
search 0 6310 1
search 0 time 6311 0.00500285216289019
search 0 tasks 6311 900.215496751703
search 1 11004 1
search 1 time 11016 0.00153567538126362
search 1 tasks 11016 464.412490922295
search 2 12 1
search 2 time 13 0.000307692307692308
search 2 tasks 13 141.846153846154
gain stage 0 to stage 1 1 0.00356762375769612
gain stage 1 to...
September 12, 2011 at 10:35 am
counter occurrence value
optimizations 69088 1
elapsed time 69088 0.00212443550254748
final cost 69088 7.09694068392164
trivial plan 51762 1
tasks 17326 623.287082996652
no plan 0 NULL
search 0 6310 1
search 0 time 6311 0.00500285216289019
search 0 tasks 6311 900.215496751703
search 1 11004 1
search 1 time 11016 0.00153567538126362
search 1 tasks 11016 464.412490922295
search 2 12 1
search 2 time 13 0.000307692307692308
search 2 tasks 13 141.846153846154
gain stage 0 to stage 1 1 0.00356762375769612
gain stage 1 to stage 2 0 NULL
timeout 27 1
memory...
September 12, 2011 at 10:33 am
FYI, for me it recommends 4, but as I said, I set to 6.
September 12, 2011 at 10:27 am
WaitType Wait_S AvgWait_MS Resource_S Signal_S WaitCount Percentage
CXPACKET 25955.36 3.82 22734.46 3220.90 6787719 38.08
OLEDB 17137.44 0.14 17137.44 0.00 125703719 25.14
PAGEIOLATCH_SH 8344.74 26.25 8298.34 46.40 317913 12.24
SOS_SCHEDULER_YIELD 4127.01 0.18 51.29 4075.72 22326984 6.05
BACKUPIO 2285.14 0.82 2273.73 11.41 2776238 3.35
WRITELOG 1565.00 0.74 1444.10 120.90 2101287 2.30
LATCH_EX 1354.82 0.16 1092.97 261.85 8306790 1.99
BACKUPTHREAD 1235.28 135.54 1235.01 0.27 9114 1.81
ASYNC_IO_COMPLETION 1017.53 4086.45 1017.52 0.01 249 1.49
BACKUPBUFFER 980.15 0.16 900.92 79.22 6306888 1.44
ASYNC_NETWORK_IO 936.73 0.30 899.38 37.36 3118273 1.37
SQLTRACE_LOCK 487.04 5.49 465.67 21.37 88736 0.71
PREEMPTIVE_OS_AUTHENTICATIONOPS 389.93 0.17 389.93 0.00 2284736 0.57
SLEEP_BPOOL_FLUSH 328.39 2.93 324.91 3.48 111984 0.48
September 12, 2011 at 10:23 am
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...
September 12, 2011 at 10:09 am
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...
September 12, 2011 at 9:03 am
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....
September 7, 2011 at 5:53 am
Sorry Carlos.. missed your post saying pretty much the same thing.
September 7, 2011 at 5:46 am
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...
September 7, 2011 at 5:40 am
Viewing 15 posts - 1 through 15 (of 514 total)