March 27, 2014 at 7:18 am
In Profiler a StmtCompleted Event Class has identified a query to be:
SELECT TOP 1 * FROM [WINSVR2008R2].[001].DBO.[OECTLFIL_SQL] WHERE ( ( OE_CTL_KEY_1 = @P1 ) ) order by OE_CTL_KEY_1 asc
Is there any way to determine the value of @P1? If so which Event class and Column should I examine?
Thanks,
pat
March 27, 2014 at 7:51 am
I have found a partial answer. I am capturing, ShowXMLPlan, RPC:Completed, SP:StatementCompleted, SQL:BatchCompleted and SQL:BatchStarting. So far in the trace I am following, only Stored Procedures have been called. It appears that there is only one query in each stored procedure. In that case a SP:StatementCompleted precedes each RPC:Completed Event class. I am obtaining the query from the TextData of the SP:StatementCompleted and the Parameters used are found in the TextData of the RPC:Completed Event class.
Here is the example I am following.
SP:StatementCompleted
TextData = SELECT TOP 1 * FROM [WINSVR2008R2].[001].DBO.[OECTLFIL_SQL] WHERE ( ( OE_CTL_KEY_1 = @P1 ) ) order by OE_CTL_KEY_1 asc
Followed by RPC:Completed
TextData = exec sp_execute 16,1
In this example the value of @p1 is 1. When I run this query against the table with that value I get the record I expected. So part of my question is answered (If what I have explained is just a coincidence and is not correct, PLEASE correct me.).
However, I do not have an example of a query that is not in a Stored Procedure. How do I identify Parameters that are used in a query that is not contained in a Stored procedure?
Also a new question, how do I determine the name of the stored procedure referenced in: exec sp_execute 16,1?
March 27, 2014 at 3:21 pm
There should be a corresponding SQL:StmtStarting event for any query using parameters that first declares the value of the parameters.
For the sp_execute example, you should see a RPC:Starting event but the database context will be master so make sure your filters aren't blocking these events.
March 28, 2014 at 6:03 am
ShowPlan and StmtComplete are extremely resource intensive events to capture. Be very cautious doing this on a production 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
March 28, 2014 at 7:51 am
Grant,
I did not know that so thanks for the advice. In this instance I have a Server dedicated to testing. I am the only one using it. I turn the profiler on when I want to test and then back off.
pat
March 28, 2014 at 7:57 am
mpdillon (3/28/2014)
Grant,I did not know that so thanks for the advice. In this instance I have a Server dedicated to testing. I am the only one using it. I turn the profiler on when I want to test and then back off.
pat
That's fine then. Another thing to do to reduce the observer affect is to not use the Profiler GUI but instead generate a T-SQL script to create a server-side trace. You can output to a file (or files) and then use the GUI to look at them. It radically reduces the load on the machine.
"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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply