April 24, 2008 at 3:44 pm
here is the output from sys.dm_os_waiting_tasks while the problem is occuring
wait_type wait_duration_ms
------------------------------------------------------------ --------------------
LOGMGR_QUEUE 26859
REQUEST_FOR_DEADLOCK_SEARCH 2094
KSOURCE_WAKEUP 8733469
TRACEWRITE 1828
CHECKPOINT_QUEUE 343828
BROKER_TASK_STOP 3500
ONDEMAND_TASK_QUEUE 75078
BROKER_TRANSMITTER 8744375
SQLTRACE_BUFFER_FLUSH 500
BROKER_TRANSMITTER 8744375
BROKER_EVENTHANDLER 8743453
OLEDB 0
LAZYWRITER_SLEEP 625
April 24, 2008 at 8:55 pm
When you the procs run longer are the reads and writes the same as the short run?
April 25, 2008 at 5:39 am
Have you looked at the execution plans for the those specific queries? Scans, hash joins, cursors, anything like that in evidence?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 25, 2008 at 7:28 am
there does seem to be more reads going on when the problem occurs.
when we look at the plan, it doesx switch back and forth between 2 different plans. they are very similar, but one uses a hash join and the other uses a merge join. the strange part is that I have seen both plans perform well and both plans perform poorly.
April 25, 2008 at 7:46 am
What is the actual differences in reads between good and bad performance? Jumping between a merge and a hash would makes me think the plan is not scaling when certain criteria run.
If you have a calls that will and won't perform run them, each with the SET STATISTICS IO ON and post the results and include the row counts.
David
April 25, 2008 at 8:53 am
You know if you're getting different plans and different performance, you might have instances where one piece of data generates a plan that it can use and a seperate set of data can't use that plan but needs another. What about putting WITH_RECOMPILE on to the query to ensure that each different set of data gets the plan it needs. There is quite a huge difference between a MERGE and a HASH.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 25, 2008 at 8:58 am
we have thought about adding with recompile to that sproc. I'll talk to my team and see if we want to try that. Will post results. I have the results of all the plans we captured in a spreadsheet, but I can think of any easy way to post without loosing formatting
April 29, 2008 at 12:28 pm
OK, we have found the problem. I would like to thank everyone who posted on this topic, there were some really great suggestions and I learned a thing or two. Thanks everyone!
We tracked this down to a trace that was running. It was tracing for sp:starting and sp:competed. It was filtered to catch only 2 sprocs. We have run what I would consider a much more intensive trace on the regular on that server and have never had this problem before. We even managed to reproduce the issue and "fix" the issue in our test environment several times now, so there is no doubt that this trace was the issue. Why we were able to run traces in the past, and not currently is a new mystery. we have better hardware than we used to. Since we still have an open case with Microsoft, I will try and get an answer to post here. When I in9itially asked him, he basically hinted that we shouldn't do traces in production. We all know the value of running traces and that just seems unacceptable. I would understand on a high volume system, but when our system is only at about 10% capacity, I would not expect it to be an issue.
April 29, 2008 at 12:51 pm
Wild.
That's sure a surprise that you shouldn't run trace in production... Since I've got it running off & on on most production servers and I have yet to see a problem caused by it.
Can you post your trace definition without giving away trade secrets? I'm just curious to see if it's very different than mine.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2008 at 12:58 pm
me too, we run them constantly out here. I know how to save the trace definition as a template, but I don't think you can just attach that here - is there a way to export that in a readable, postable format?
April 29, 2008 at 12:59 pm
I should also say that they did recomend not running traces unless you have to or unless it's the default trace.
April 29, 2008 at 1:03 pm
Just run "File", "Export", "Script Trace Definition", "SQL Server 2005" and you can define the file from there. Attach it to a message (cause posting it would be ugly).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2008 at 1:11 pm
here it is, you will need to rename it to .tdf
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply