April 23, 2010 at 4:18 am
We have a third party product plugged into one of our sql servers. There is a certain process one of our employees runs that shoudl take an hour but as the data has increased it now runs forever (literally).
The memory in the machine was upgraded because it was very low.
However I am running a profiler againsts the spid for this process and it is doing nothing more than line after line of SP:Cacheremove.
To me that would suggest it is trying to make space in the cache for what it is doing - but it isn't then actually doing anything!
Any ideas? Shoudl I flush the procedure cache to give it a fresh start?
April 23, 2010 at 6:02 am
Which events did you select for the trace you're running?
April 23, 2010 at 7:09 am
It sounds like you must have the trace events set up wrong.
BTW, you shouldn't run Profiler directly against production systems, especially if it's a system that is already experiencing performance issues. The Profiler GUI can cause performance issues all on it's own. Script out the trace you want and run it as a server-side trace, no GUI involved.
"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 23, 2010 at 7:40 am
Follow Grant's good advice.
Select the events and other trace properties in the GUI, start the trace, immediately stop it and select File ---> Export. This way you'll obtain the scripted trace either for SQL 2000 or for SQL 2005 / 2008. Use sp_trace_setstatus to start / stop the trace. Use sys.fn_trace_getinfo(0) to see the current trace status.
April 23, 2010 at 7:45 am
dmoldovan (4/23/2010)
Follow Grant's good advice.Select the events and other trace properties in the GUI, start the trace, immediately stop it and select File ---> Export. This way you'll obtain the scripted trace either for SQL 2000 or for SQL 2005 / 2008. Use sp_trace_setstatus to start / stop the trace. Use sys.fn_trace_getinfo(0) to see the current trace status.
Excellent summary.
"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 23, 2010 at 8:32 am
Grant Fritchey (4/23/2010)
dmoldovan (4/23/2010)
Follow Grant's good advice.Select the events and other trace properties in the GUI, start the trace, immediately stop it and select File ---> Export. This way you'll obtain the scripted trace either for SQL 2000 or for SQL 2005 / 2008. Use sp_trace_setstatus to start / stop the trace. Use sys.fn_trace_getinfo(0) to see the current trace status.
Excellent summary.
Thank you 🙂
April 23, 2010 at 9:00 am
I've got all the usual suspects - StmtCompleted, BatchCompleted. The started ones, Execution plans. Its throwing up nothing at all for this spid other than cacheremoves.
Good call on the file trace - my bad!
April 23, 2010 at 9:23 am
Shark Energy (4/23/2010)
I've got all the usual suspects - StmtCompleted, BatchCompleted. The started ones, Execution plans. Its throwing up nothing at all for this spid other than cacheremoves.Good call on the file trace - my bad!
Whoa! Execution plans is hyper-expensive to run through a trace. You should only do that on a very targeted basis. I'd take away that event now, regardless of what else is happenning.
Still sounds odd. I mean, trace events works, so something is probably up with your configuration. Hard to know what since we're not there.
"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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply