December 8, 2015 at 7:55 am
I am running server-side trace to catch a certain stored procedure and its parameter values. I am tracing event SP:Completed with a filter for ObjectName column.
In TextData, the call is displayed like "EXEC MyStoredProc @Id = @Id" . Is there a way to get an actual value?
Thanks.
December 8, 2015 at 11:15 am
Looks like that's being called from within another procedure. Unless you also capture that procedure, I don't know of a way to do this using trace.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 8, 2015 at 10:54 pm
I don't think it's possible with SQL Profiler. But if this is just for debugging then you can save the values into a table to check after the fact, but that's not a very good production solution unless you really want that kind of extensive logging.
December 9, 2015 at 7:09 am
Yes, it's for debugging. If it's impossible with trace, what other means can I use? Will extended events or reading transaction log give me these values?
December 9, 2015 at 7:33 am
SQL Guy 1 (12/9/2015)
Yes, it's for debugging. If it's impossible with trace, what other means can I use? Will extended events or reading transaction log give me these values?
It's the method that you're trying capture that's the issue. It's called from another procedure, so there's either a local variable or a parameter being passed instead of a value. I'm pretty sure it'll look the same in extended events. Let me set up a test and get back to you.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 9, 2015 at 8:51 am
I also trace host name and login name. Most of the calls come from one of the two app servers, and some calls from a job scheduler (not SQL Server agent). I don't see any calls from the same server or another SQL Server. But regardless of the source, I need to get an input parameter value.
Here is the reason why. It usually executes between 0 and 5 sec. This is normal. Sometimes, however, it reaches over 10 sec. I need to debug these cases. I also trace RowCounts column. It is not proportional to duration. It can retrieve 16k records in 1 sec, and 6k records in 17 sec. I am going to debug this S.P. on another, test server.
Of course, there are many other factors that affect performance, but nevertheless I need to get at least some values to start with.
December 9, 2015 at 8:56 am
That's a no-go here too. I set up the test like this:
CREATE PROC dbo.ExEventTest (@ID INT)
AS
SELECT *
FROM dbo.Test1 AS t
WHERE t.ID = @ID;
GO
CREATE PROC dbo.ExEventTestWrapper (@WrapperID INT)
AS
EXEC dbo.ExEventTest
@ID = @WrapperID;
GO
EXEC dbo.ExEventTestWrapper
@WrapperID = 42;
The output from module_complete was:
EXEC dbo.ExEventTest @ID = @WrapperID;
To get the value that was given to @WrapperID in this case, I'd need to capture the calling procedure. Then I'd see it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 9, 2015 at 11:19 am
SQL Guy 1 (12/8/2015)
I am running server-side trace to catch a certain stored procedure and its parameter values. I am tracing event SP:Completed with a filter for ObjectName column.In TextData, the call is displayed like "EXEC MyStoredProc @Id = @Id" . Is there a way to get an actual value?
Thanks.
Yes. Use RPC:Completed using the same filter on the ObjectName column. It will show the entire call to the stored procedure if it's called from the front end.
--Jeff Moden
December 11, 2015 at 8:00 am
By some reason RPC:Completed does not work for me, probably I am doing something wrong.
I need to filter the trace. Without it, my trace will be flooded with millions of records in a few minutes. If I apply filter to SP:Completed, it filters in only my proc. If I apply same filter to RPC:Completed, it filters out everything, nothing is displayed in my trace-selecting function fn_trace_gettable.
Here is how I script a filter:
declare @sp nvarchar(256) = 'MyStoredProc'
exec sp_trace_setfilter
@TraceID = @TraceID,
@columnId = 34, -- Object
@logical_operator = 0, -- AND
@comparison_operator = 6, -- Like
@value = @sp
And here how I set the trace:
-- SP:Completed:
exec sp_trace_setevent @TraceID, 43, 14, @on -- Start time
exec sp_trace_setevent @TraceID, 43, 15, @on -- End time
exec sp_trace_setevent @TraceID, 43, 13, @on -- Duration
exec sp_trace_setevent @TraceID, 43, 35, @on -- Database name,
exec sp_trace_setevent @TraceID, 43, 1, @on -- Text
exec sp_trace_setevent @TraceID, 43, 16, @on -- Reads
exec sp_trace_setevent @TraceID, 43, 17, @on -- Writes
exec sp_trace_setevent @TraceID, 43, 18, @on -- CPU
exec sp_trace_setevent @TraceID, 43, 48, @on -- RowCounts
exec sp_trace_setevent @TraceID, 43, 31, @on -- Error number
exec sp_trace_setevent @TraceID, 43, 8, @on -- HostName
exec sp_trace_setevent @TraceID, 43, 26, @on -- Server name
exec sp_trace_setevent @TraceID, 43, 10, @on -- Application Name
exec sp_trace_setevent @TraceID, 43, 11, @on -- Login name
exec sp_trace_setevent @TraceID, 43, 34, @on -- Object name
exec sp_trace_setevent @TraceID, 43, 12, @on -- ClientProcessID
exec sp_trace_setevent @TraceID, 43, 43, @on -- SPID
To change to RPC:Completed, I change all 43 to 10.
December 11, 2015 at 9:12 am
If it's for a single, specific stored proc, could you just add code to that proc to save the parameter values that came in? You could even have an optional parameter control whether to capture params or not.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 12, 2015 at 6:52 pm
SQL Guy 1 (12/11/2015)
By some reason RPC:Completed does not work for me, probably I am doing something wrong.I need to filter the trace. Without it, my trace will be flooded with millions of records in a few minutes. If I apply filter to SP:Completed, it filters in only my proc. If I apply same filter to RPC:Completed, it filters out everything, nothing is displayed in my trace-selecting function fn_trace_gettable.
Here is how I script a filter:
declare @sp nvarchar(256) = 'MyStoredProc'
exec sp_trace_setfilter
@TraceID = @TraceID,
@columnId = 34, -- Object
@logical_operator = 0, -- AND
@comparison_operator = 6, -- Like
@value = @sp
And here how I set the trace:
-- SP:Completed:
exec sp_trace_setevent @TraceID, 43, 14, @on -- Start time
exec sp_trace_setevent @TraceID, 43, 15, @on -- End time
exec sp_trace_setevent @TraceID, 43, 13, @on -- Duration
exec sp_trace_setevent @TraceID, 43, 35, @on -- Database name,
exec sp_trace_setevent @TraceID, 43, 1, @on -- Text
exec sp_trace_setevent @TraceID, 43, 16, @on -- Reads
exec sp_trace_setevent @TraceID, 43, 17, @on -- Writes
exec sp_trace_setevent @TraceID, 43, 18, @on -- CPU
exec sp_trace_setevent @TraceID, 43, 48, @on -- RowCounts
exec sp_trace_setevent @TraceID, 43, 31, @on -- Error number
exec sp_trace_setevent @TraceID, 43, 8, @on -- HostName
exec sp_trace_setevent @TraceID, 43, 26, @on -- Server name
exec sp_trace_setevent @TraceID, 43, 10, @on -- Application Name
exec sp_trace_setevent @TraceID, 43, 11, @on -- Login name
exec sp_trace_setevent @TraceID, 43, 34, @on -- Object name
exec sp_trace_setevent @TraceID, 43, 12, @on -- ClientProcessID
exec sp_trace_setevent @TraceID, 43, 43, @on -- SPID
To change to RPC:Completed, I change all 43 to 10.
Try changing this...
declare @sp nvarchar(256) = 'MyStoredProc'
... to this...
declare @sp nvarchar(256) = '%MyStoredProc%'
You have to remember that there will be parameters either before or after or both when you use RPC:Completed.
--Jeff Moden
December 14, 2015 at 2:44 pm
You may add something like this into your procedure:
DECLARE @ProcName sysname, @Param INT
SELECT @ProcName = OBJECT_NAME(@@PROCID), @Param = 1000
IF HOST_NAME() = 'My Test Host'
RAISERROR ('Started procedure %s, Parameter supplied: @Param=%d', 0,1,
@ProcName, @Param) WITH LOG
The message will be displayed during execution and also recorded in the Application Event Log on the server.
Severity "0" won't affect procedure execution.
_____________
Code for TallyGenerator
December 14, 2015 at 3:05 pm
Sergiy (12/14/2015)
You may add something like this into your procedure:
DECLARE @ProcName sysname, @Param INTSELECT @ProcName = OBJECT_NAME(@@PROCID), @Param = 1000
IF HOST_NAME() = 'My Test Host'
RAISERROR ('Started procedure %s, Parameter supplied: @Param=%d', 0,1,
@ProcName, @Param) WITH LOG
The message will be displayed during execution and also recorded in the Application Event Log on the server.
Severity "0" won't affect procedure execution.
1) I wouldn't advise writing that type of info into error logs.
2) Using "WITH LOG" requires a very high level of authority, hopefully your normal app logins don't have that high a level.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 14, 2015 at 3:26 pm
ScottPletcher (12/14/2015)
hopefully your normal app logins don't have that high a level.
On a test host?
_____________
Code for TallyGenerator
December 14, 2015 at 3:34 pm
ScottPletcher (12/14/2015)
1) I wouldn't advise writing that type of info into error logs.
Why?
What do you think Application Event Log is for?
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply