March 6, 2009 at 7:19 am
Hello Everyone. Does anyone know of a way to obtain the details of a SPID in a Profiler trace? I know you can get the details of any given SPID in SQL Server 2005 in the Activity Monitor by right clicking on a specific SPID and selecting 'Details'. I would like to get this info in a trace, though, so that I can associate a specific event with its SPID details - I am trying to track down specific transact-sql statements that are initiating table scans.
Please let me know if you have any suggestions.
Thanks!
Vic
March 6, 2009 at 9:38 am
What are the details you want to get about the SPID? Do you want to filter by SPID or do you want details for each SPID?
What events are you tracing?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 6, 2009 at 11:04 am
Hi Jack,
I am tracing the 'Scan:Started' and 'Scan:Stopped' events. I would like to know the details associated with each SPID for each indivual event. So for instance, when a Scan:Started event occurs, there is a SPID in the Profile Trace row for that event at, say, 10:00:53 pm. I want the details for the SPID at that particular instant. I know you can attain this info using DBCC InputBuffer(SPID) or by checking the SPID details in the SQL Server Activity Monitor. However, if the Scan:started event occurred at 10:00:53 pm, I'd need to run either of those almost instantaneously to get the corresponding details. If I even execute those a couple seconds later, the details of the SPID will likely have changed.
The details I am trying to acquire are transact-sql statements. I suspect that a DB performance issue we are having is related to a table scan on the table I am tracing and I am trying to isolate exactly what statement(s) from our .Net Client application are causing the scans.
Thanks,
Vic
March 6, 2009 at 11:14 am
Vic,
Thanks for answering my questions. Unfortunately there is no way that I am aware of to tell Profiler or SQLTrace to run DBCC INPUTBUFFER or any other query based on an event that it captures.
On the other hand I believe you can determine what queries are causing the scans by adding the RPC:Starting, SP:Starting, SP:Completed, SQL:StmtStarting, and SQL:StmtCompleted Events. Make sure to include the SPID and TextData columns.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 3, 2009 at 7:40 am
I am looking at the Can:started/Stopped. I have the TSQL option and the SP options in my trace. I have nothing in my trace results but lock acsuired released and the scan:stopped/Started. No other infomation cam into the trace. Any inpout you might have would be helpful. This trace to me is useless even thought I am looking at every conceivable event.:w00t:
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
June 3, 2009 at 7:59 am
David,
What are you trying to find out with your trace? Knowing what you are tracing is great, but without knowing what you are trying to accomplish it is impossible to even know if you are tracing the correct events.
You need to be very careful when collecting the Locks:Acquired and Released events as that will create a very busy trace and will likely cause some performance issues.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply