August 25, 2016 at 8:13 am
Hi Guys,
I am currently setting up a server side trace that will run constantly on a server for one particular database. The data is then being loaded back into a table.
When looking back on some of the data that has been loaded in I am really confused as there appears to be quite a lot of dynamic sql being ran under events 10 and 12 (batch completed and rpc completed) meaning that the users query count is being massively inflated.
The person who supposedly ran this dynamic sql said that they don't know anything about it, leading me to believe that it may be some sort of background job that I should maybe filter out. The trouble is that I don't know how to filter it out and it.
Here is the code for my trace definition...
ALTER PROCEDURE [dbo].[sp_StartTrace] -- sp_StartTrace 'CoreDW'
-- Add the parameters for the stored procedure here
@database nvarchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @traceoptions int
declare @starttime datetime
declare @tracepath nvarchar(100)
declare @tracename nvarchar(256)
declare @datetime nvarchar(30)
set @traceoptions = 0
set @maxfilesize = 10
set @starttime = getdate()
set @datetime = CONVERT(VARCHAR(10),@starttime,112) + REPLACE(CONVERT(VARCHAR(10),@starttime,108),':','')
set @tracepath = N'P:\Traces\MyTrace_'
set @tracename = @tracepath + @datetime
-- close off any current traces
if exists (select *
from sys.traces
where is_default = 0
and path like @tracepath + '%')
begin
declare @sqlstop VARCHAR(2000)
declare @sqldelete VARCHAR(2000)
-- stop the trace
set @sqlstop = (SELECT STUFF('; ' + 'exec sp_trace_setstatus ' + (
SELECT cast(id AS NVARCHAR(3)) id
FROM sys.traces
WHERE id = t.id
) + ', 0',1,1,'')
FROM sys.traces t
WHERE is_default = 0 AND id = t.id AND path LIKE @tracepath + '%'
FOR XML path(''))
--print @sqlstop
exec (@sqlstop)
-- delete the trace
set @sqldelete = (SELECT STUFF('; ' + 'exec sp_trace_setstatus ' + (
SELECT cast(id AS NVARCHAR(3)) id
FROM sys.traces
WHERE id = t.id
) + ', 2',1,1,'')
FROM sys.traces t
WHERE is_default = 0 AND id = t.id AND path LIKE @tracepath + '%'
FOR XML path(''))
--print @sqldelete
exec (@sqldelete)
end
-- Create the trace with the name of the output file - .trc extension is added to filename
exec @rc = sp_trace_create @TraceID output, 0, @tracename, @maxfilesize, null
if (@rc != 0) goto error
-- Set the events
declare @on bit
set @on = 1
-- Audit Login events
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 35, @on
exec sp_trace_setevent @TraceID, 14, 26, @on
exec sp_trace_setevent @TraceID, 14, 8, @on
-- Audit Logout events
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 35, @on
exec sp_trace_setevent @TraceID, 15, 26, @on
exec sp_trace_setevent @TraceID, 15, 8, @on
-- ExistingConnection events
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 35, @on
exec sp_trace_setevent @TraceID, 17, 26, @on
exec sp_trace_setevent @TraceID, 17, 8, @on
-- RPC:Completed events
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 26, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
-- SQL:BatchCompleted events
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 26, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
-- SQL:BatchStarting events
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
-- SP:Completed events
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 8, @on
exec sp_trace_setevent @TraceID, 43, 9, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 26, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 35, @on
-- Filter out the SQL Profiler events
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- filter on database name
exec sp_trace_setfilter @TraceID, 35, 0, 0, @database
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
END
finish:
Regarding the unwanted rows that are coming back from the trace, I have attached the TextData from three example rows that will show you the queries being ran.
As you can see I am already applying a few filters to the trace to filter on database name etc. if anyone could tell me how to filter the unwanted rows away that would be great.
** edit **
Sorry I should have mentioned, this is happening for all users and not just the one user I mentioned.
August 25, 2016 at 9:18 am
Folks, i'm really sorry for wasting your time. I just had a closer look at the data and it looks like these are all IntelliSense statements, it should be easy enough for me to filter these.
Jim
August 25, 2016 at 9:20 am
I think you want to look at the application name column that executes those, not just the text data. Filtering out based on what you see in the entire row instead of just the text data.
Sue
August 25, 2016 at 9:27 am
Sue_H (8/25/2016)
I think you want to look at the application name column that executes those, not just the text data. Filtering out based on what you see in the entire row instead of just the text data.Sue
Hi Sue, yep you are spot on although I did realise my own mistake. The application name column lists these rows as being related to IntelliSense so easy enough for me to filter these away.
Thanks again.
August 26, 2016 at 8:39 am
I am back with another question relating to this server side trace and more unwanted rows that I am struggling to filter.
Let me give you an example of my problem... when a user right clicks a stored procedure and selects 'modify', a whole load of stored procedures run in the background under his name in order to generate the alter 'proc statement', I don't want these showing on the trace.
These are running under EventID 10 which is RPC: Completed, so I can't filter based on this or I will also filter other stored procedures I want to capture. The ApplicationName for the unwanted rows is 'Microsoft SQL Server Management Studio - Query', but I can't filter on this either or I will lose other valid queries.
I need to find a way to only capture user queries on a trace and none of the stuff that is running in the background.
Hopefully this makes sense, please let me know if you need any other details.
August 26, 2016 at 11:17 am
Jim-S (8/26/2016)
I am back with another question relating to this server side trace and more unwanted rows that I am struggling to filter.Let me give you an example of my problem... when a user right clicks a stored procedure and selects 'modify', a whole load of stored procedures run in the background under his name in order to generate the alter 'proc statement', I don't want these showing on the trace.
These are running under EventID 10 which is RPC: Completed, so I can't filter based on this or I will also filter other stored procedures I want to capture. The ApplicationName for the unwanted rows is 'Microsoft SQL Server Management Studio - Query', but I can't filter on this either or I will lose other valid queries.
I need to find a way to only capture user queries on a trace and none of the stuff that is running in the background.
Hopefully this makes sense, please let me know if you need any other details.
When they click around in SSMS or do the right click, modify on a stored procedure, most of the noise you see should be coming from the Intellisense as you already found or Microsoft SQL Server Management Studio (does not have - Query on the end).
You will likely have some noise you can't filter out but you can reduce it significantly.
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply