SQL Server 2008 R2 – Setting Up Filtering for Server-Side Tracing

sql-server-2008-r2

I'm trying to set up a server-side trace in SQL Server 2008 R2. While I have no trouble selecting events and data columns, the filtering seems a bit odd…

I was trying to set up a filtering on both the duration of the SQL batch or stored procedure (filter anything >= 1000 ms), but I also wanted to filter on the database name involved.

When I export the trace definition from SQL Profiler to a *.sql file, I however don't seem to find any filtering on the database name:

-- lots of other stuff up here - setting up trace events and so on...

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 100479e4-6eeb-4608-af17-64141f1631ec'
set @bigintfilter = 1000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

....

So what's up with that? Why can I specify a database name to filter on – but the trace definition doesn't contain it, in the end?

Does that mean the server-side trace is always only checking the database it's been creating in?
But then why can I specify a filter by database name?
Does this also mean there's no way to do cross-database server-wide tracing on e.g. deadlocks?

Update: after some more toying around, I came to the following conclusion:

the filter on the database name was not applied to the Create Trace script in my case since I ended up selecting only the Locks / Deadlock Graph event – and that event doesn't expose a Database Name as an event column
if I pick other events, like RPC completed that do expose the Database Name as an event column, I can filter on the Database Name and that filter is also properly added to the Create Trace script.

I can't figure out how I was able to actually define a filter on Database Name, since normally, if an event doesn't expose a column, that column is also not available in the Column filter dialog….

Anyway – for whatever reasons that are beyond me (for now, at least), Deadlock Graph doesn't expose the Database Name and thus I cannot filter on that property, either….

Thanks all that helped and provided insightful comments!

Best Answer

Does that mean the server-side trace is always only checking the database it's been creating in?

No, the server-side trace has the scope of just that: the server. When I create (what I think is) the exact trace definition and export it, I get the following snippet:

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @bigintfilter = 1000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

exec sp_trace_setfilter @TraceID, 35, 0, 6, N'AdventureWorks'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

I am using SQL Server Profiler 2008 R2 as well. If you do not filter on the database name, then you will be capturing trace events for all of the instance databases. As to why your export didn't seem to include that portion of the filter, I can't say for sure. I know this goes without saying, but I'd check your trace definition within Profiler before you export to ensure the database name is in fact included as a filter (again, I know you've probably double and tripple checked this, but I had to say it).

Related Solutions

Sql-server – Sql profiler replay table

Are you using one of the default traces in Profiler or are you creating it manually?

If I were in the same situation, I would just save the trace to a regular text file and then import that file into a SQL Server table.

USE mydb 
GO 

SELECT * INTO mytracetable FROM ::fn_trace_gettable('c:\my_trace.trc', default)
GO

At least that way you are certain to get the same columns everytime. As to the BULK INSERT statements, you could try this filter.

Column Filter for TextData with the Not Like set to %BULK INSERT%.

SQL Server – Interpreting Columns in Profile Trace

The object ID is actually a partition id. This question over on SO provides a simple query to retrieve the needed information.

A side note, since you are using SQL Server 2008 R2 I would highly suggest taking a look into the System Health session that contains the recent deadlocks that have occurred, among other things. Depending on the activity of your instance you may need to pull this information out on a scheduled basis since it is buffer memory so does not stay there long. It is much more efficient to pull this information out than trying to setup a trace and waiting for it to occur again.

I actually wrote up a blog post on this area that includes a script to parse out the information where it is a bit more readable. The blog post is here, the script is below:


;WITH xDeadlock (Contents)
AS
(
select CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) as DeadlockGraph
FROM
    (select CAST(target_data as xml) as TargetData
    from sys.dm_xe_session_targets st
    join sys.dm_xe_sessions s on s.address = st.event_session_address
    where name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
), Victims AS
(
SELECT    ID = Victims.List.value('@id', 'varchar(50)')
FROM xDeadlock
CROSS APPLY xDeadlock.Contents.nodes('//deadlock/victim-list/victimProcess') AS Victims (List)
), Locks AS
(
SELECT  --xDeadlock.DeadlockID,
        MainLock.Process.value('@id', 'varchar(100)') AS LockID,
        OwnerList.Owner.value('@id', 'varchar(200)') AS LockProcessId,
        REPLACE(MainLock.Process.value('local-name(.)', 'varchar(100)'), 'lock', '') AS LockEvent,
        MainLock.Process.value('@objectname', 'sysname') AS ObjectName,
        OwnerList.Owner.value('@mode', 'varchar(10)') AS LockMode,
        MainLock.Process.value('@dbid', 'INTEGER') AS Database_id,
        MainLock.Process.value('@associatedObjectId', 'BIGINT') AS AssociatedObjectId,
        MainLock.Process.value('@WaitType', 'varchar(100)') AS WaitType,
        WaiterList.Owner.value('@id', 'varchar(200)') AS WaitProcessId,
        WaiterList.Owner.value('@mode', 'varchar(10)') AS WaitMode
FROM    xDeadlock
        CROSS APPLY xDeadlock.Contents.nodes('//deadlock/resource-list') AS Locks (list)
        CROSS APPLY Locks.List.nodes('*') AS MainLock (Process)
        CROSS APPLY MainLock.Process.nodes('owner-list/owner') AS OwnerList (Owner)
        CROSS APPLY MainLock.Process.nodes('waiter-list/waiter') AS WaiterList (Owner)
), Process AS 
(
-- get the data from the process node
SELECT  --xDeadlock.DeadlockID,
        [Victim] = CONVERT(BIT, CASE WHEN Deadlock.Process.value('@id', 'varchar(50)') = ISNULL(Deadlock.Process.value('../../@victim', 'varchar(50)'), v.ID) 
                                     THEN 1
                                     ELSE 0
                                END),
        [LockMode] = Deadlock.Process.value('@lockMode', 'varchar(10)'), -- how is this different from in the resource-list section?
        [ProcessID] = Process.ID, --Deadlock.Process.value('@id', 'varchar(50)'),
        [KPID] = Deadlock.Process.value('@kpid', 'int'), -- kernel-process id / thread ID number
        [SPID] = Deadlock.Process.value('@spid', 'int'), -- system process id (connection to sql)
        [SBID] = Deadlock.Process.value('@sbid', 'int'), -- system batch id / request_id (a query that a SPID is running)
        [ECID] = Deadlock.Process.value('@ecid', 'int'), -- execution context ID (a worker thread running part of a query)
        [IsolationLevel] = Deadlock.Process.value('@isolationlevel', 'varchar(200)'),
        [WaitResource] = Deadlock.Process.value('@waitresource', 'varchar(200)'),
        [LogUsed] = Deadlock.Process.value('@logused', 'int'),
        [ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
        [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
        [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
        [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
        [BatchStarted] = Deadlock.Process.value('@lastbatchstarted', 'datetime'),
        [BatchCompleted] = Deadlock.Process.value('@lastbatchcompleted', 'datetime'),
        [InputBuffer] = Input.Buffer.query('.'),
        xDeadlock.[Contents],
        [QueryStatement] = Execution.Frame.value('.', 'varchar(max)'),
        TranCount = Deadlock.Process.value('@trancount', 'int')
FROM    xDeadlock
        CROSS APPLY xDeadlock.Contents.nodes('//deadlock/process-list/process') AS Deadlock (Process)
        CROSS APPLY (SELECT Deadlock.Process.value('@id', 'varchar(50)') ) AS Process (ID)
        LEFT JOIN Victims v ON Process.ID = v.ID
        CROSS APPLY Deadlock.Process.nodes('inputbuf') AS Input (Buffer)
        CROSS APPLY Deadlock.Process.nodes('executionStack') AS Execution (Frame)
)
-- get the columns in the desired order
SELECT  p.Victim,
        p.LockMode,
        LockedObject = NULLIF(l.ObjectName, ''),
        l.database_id,
        l.AssociatedObjectId,
        LockProcess = p.ProcessID,
        p.KPID,
        p.SPID,
        p.SBID,
        p.ECID,
        p.TranCount,
        l.LockEvent,
        LockedMode = l.LockMode,
        l.WaitProcessID,
        l.WaitMode,
        p.WaitResource,
        l.WaitType,
        p.IsolationLevel,
        p.LogUsed,
        p.ClientApp,
        p.HostName,
        p.LoginName,
        p.TransactionTime,
        p.BatchStarted,
        p.BatchCompleted,
        p.InputBuffer
FROM    
    Locks l
    JOIN Process p ON p.ProcessID = l.LockProcessID
--WHERE p.TransactionTime > '2013-10-01'
ORDER BY p.Victim DESC,
        p.ProcessId;

Best Answer

Related Solutions

Sql-server – Sql profiler replay table

SQL Server – Interpreting Columns in Profile Trace

Related Question