May 25, 2010 at 10:01 am
Hi. I have a report that fails to run (times out after 40 minutes). It calls a stored procedure.
Can I monitor/isolate the Stored Procedure (trace file). Is it possible to run profiler to obtain stats about a single stored procedure? If no what is the best available option?
I would like to provide a third party developer with information to assist in finding a resolution.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 25, 2010 at 10:07 am
You can set up a filter on object_name or object_id. That should be enough.
You could start from the "tuning" template and script it out as server-side trace.
Hope this helps
-- Gianluca Sartori
May 25, 2010 at 11:18 am
Thanks, I will give that a whirl.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 26, 2010 at 6:25 am
Hi ,
Just in profiler go to the column filter and set your objectID , name ,or host whatever is required . you can even create a template if required in profiler and run that template accordlinly .
Regards
May 26, 2010 at 7:02 am
Since you're working in 2005, I'd suggest, especially for targeted information like this, to use the dynamic management views to pull the execution plans and execution information directly from the cache. Selecting from sys.dm_exec_requests while the query is executing, or sys.dm_exec_query_stats if it completes successfully can both be combined with queries against sys.dm_exec_query_plan to get the information you want.
If you do go with setting up a server side trace, be very careful to apply filters when capturing execution plans there. Execution plans captured through trace events are very expensive for the system. Also, while you can use Profiler to set up the trace script, don't use the Profiler gui to run it against your production system. The Profiler gui accesses the data in a different way than a server side trace and can negatively impact the system.
"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
May 26, 2010 at 7:23 am
Another approach
SET statistics profile on
exec SP
SET statistics profile off
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 26, 2010 at 12:49 pm
Thanks guys, really appreciate your input.
Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply