April 27, 2011 at 10:30 am
I'm supporting an application that is big about using sp_prepare and sp_execute. I need to reverse-engineer some queries for reports and I'm having a hard time since I can't see them :w00t:.
The best I've come up with so far is http://dbaspot.com/ms-sqlserver/141465-how-can-i-find-out-what-command-sp_execute-running-without-using-profiler.html
I get this query to work, but I don't know how to find the latest plan (option #3).
select text
from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(plan_handle)
--where session_id = 52
--Column doesn't exist in 2005 (assuming it does after)
select * FROM sys.dm_exec_sql_text(NULL)
from sys.dm_exec_sql_text(most_recent_sql_handle)
--where session_id = 52
April 27, 2011 at 1:56 pm
Would the following script help?
SELECT DEST.TEXT
FROM sys.[dm_exec_connections] SDEC
CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST
--WHERE SDEC.[most_recent_session_id] = 57
April 27, 2011 at 2:13 pm
Looks right on the nose. I'll test it out when I'm back in the office.
Tx.
April 27, 2011 at 2:26 pm
April 27, 2011 at 2:55 pm
While we're at it... is there any way (other than a trace) to see all the queries run by a session between 2 dates?
Or plan A.2 Is there a way to see all the plans queries that are prepared and ready for sp_execute (not sp_executesql)?
April 27, 2011 at 3:28 pm
Ninja's_RGR'us (4/27/2011)
While we're at it... is there any way (other than a trace) to see all the queries run by a session between 2 dates?Or plan A.2 Is there a way to see all the plans queries that are prepared and ready for sp_execute (not sp_executesql)?
Regarding question 1: Nope (or at least not that I know of), since SQL Server is designed to release execution plans from memory if no longer needed in order to free memory. There's a concept involved considering the complexity of the query plan itself as well as the number of times the query being called "recently" (using a decreasing tick counter). So, the more you query towards the past and the less complex ther query plan is/was and the less frequent the query is used the higher are the chances for an execution plan being removed from the cache.
If the app uses a DBCC freeproccache you'd miss al previous statements, too...
Regarding plan A.2: I guess that's a question for Paul White (aka SQLKiwi)... It's way over my head.
April 28, 2011 at 5:42 am
Ninja's_RGR'us (4/27/2011)
While we're at it... is there any way (other than a trace) to see all the queries run by a session between 2 dates?
Not by a trace? The only thing I can think of is to set up (in advance) some other sort of logging, but I'm not sure if that will capture session specific information.
April 28, 2011 at 5:59 am
Brandie Tarvin (4/28/2011)
Ninja's_RGR'us (4/27/2011)
While we're at it... is there any way (other than a trace) to see all the queries run by a session between 2 dates?Not by a trace? The only thing I can think of is to set up (in advance) some other sort of logging, but I'm not sure if that will capture session specific information.
Ok maybe I'm removing a valid option.
When I look at the trace I have nothing in the sp_prepare event that shows me the query that is being prepared. Is this information available?
April 28, 2011 at 6:07 am
My other challenge here is that when I run a report or whatever so that I can see what's going on. The statements seem to already have been prepared (unless I'm blind and missing them in the trace).
So back to 1 of my previous requests... any way to see all the prepared statement queries or plans.... anything I can use to figure out what I got to do?
April 28, 2011 at 6:31 am
Have you looked into Event Notifications? I'm not an expert and they aren't as robust as Extended Events in 2008, but they might do something, I don't know if the needed events are available.
http://www.sqlservercentral.com/articles/Event+Notifications/68831/ - Intro to Event Notifications
http://msdn.microsoft.com/en-us/library/ms190655(v=SQL.90).aspx - Events available in Event Notifications
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
April 28, 2011 at 6:37 am
I don't see anything usefull in there in the events list. The closest thing might be cache_insert.
Anything else would scare the life out of me... getting millions of rows in the trace in only minutes. That seems a little extreme for a first SB project :w00t:.
April 28, 2011 at 6:40 am
Yeah. But you could get SHOWPLAN_XML and I think you can do some pretty complex filtering. Might be worth looking at.
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
April 28, 2011 at 6:44 am
Jack Corbett (4/28/2011)
Yeah. But you could get SHOWPLAN_XML and I think you can do some pretty complex filtering. Might be worth looking at.
Similar issue here... prod server with 10K - 100K queries for a report. I don't want to kill the server while tracing this!
That's also why I was hoping for a way to see the prepared queries :w00t:. Then it's just logging and getting the distinct prepared plans used by 1 connection and I've got all I need to move on.
April 28, 2011 at 6:57 am
Yeah, there might be too much load, but I thought it might be an option and more lightweight than a trace.
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
April 28, 2011 at 7:02 am
Jack Corbett (4/28/2011)
Yeah, there might be too much load, but I thought it might be an option and more lightweight than a trace.
I've tested that option in another project and saving the plans for each queries adds a noticable load on the query / server... and that was running a couple 1000 queries / hour. Not millions in minutes :w00t:.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply