SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running SQL Profiler Trace on a specific table


Running SQL Profiler Trace on a specific table

Author
Message
DVSQL
DVSQL
Right there with Babe
Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)

Group: General Forum Members
Points: 733 Visits: 349
Is there a way to run a SQL Trace on a specific table? I've got a table in the production environment, which keeps changing values in certain column, and I'm trying to find what is causing the change.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40178 Visits: 14413
I probably would not use Trace for this one. Consider adding a DML trigger temporarily to capture the inserted or deleted tables and the user making the changes to an audit table.

If you're on Enterprise Edition you could also consider temporarily leveraging a Database-level Audit or Change Data Capture.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
DVSQL
DVSQL
Right there with Babe
Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)

Group: General Forum Members
Points: 733 Visits: 349
Never ran those before, but it's worth a try I guess. Would tracing a single table be that much of a load onto the server? This table barely gets used, and is very small in size.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40178 Visits: 14413
DVSQL (3/26/2013)
Never ran those before, but it's worth a try I guess. Would tracing a single table be that much of a load onto the server? This table barely gets used, and is very small in size.

The thing with Trace, and where the other technologies I mentioned outshine it, is that you cannot key onto a specific table. You key onto Events, and then you filter from there. In your case you would likely need to Trace the SQL:StatementCompleted and SP:StatementCompleted Events and then add a filter to essentially say WHERE Database = 'YourDatabase' AND SQLText LIKE '%YourTableName%'. Finding accesses to your table this way would be horribly inefficient and could add significant load to your system. The reason it would add load is because each Event has to be evaluated and then the filter is applied. With the two Events I mentioned, that means every single SQL statement entering your system has to be evaluated to see if it was destined for your database, and then whether the SQL text contained your table name.

With Audit and Change Data Capture (CDC) you actually do key onto a specific table so these methods are cleaner and have much less impact on your system

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99201 Visits: 33014
Actually, if you use extended events you can filter by batch_text in sql_batch_completed or by statement in rpc_completed and then use the like_i_sql_unicode_string to do a like statement looking for that particular table or even that particular column in reference in the statement. And, since this type of filtering in extended events is done at a lower level within process, it should have very low impact.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40178 Visits: 14413
Grant Fritchey (3/27/2013)
Actually, if you use extended events you can filter by batch_text in sql_batch_completed or by statement in rpc_completed and then use the like_i_sql_unicode_string to do a like statement looking for that particular table or even that particular column in reference in the statement. And, since this type of filtering in extended events is done at a lower level within process, it should have very low impact.

I tried a task like this on a Database Snapshot not too long but found it to be impossible with EE in 2008. The predicates you mentioned look to be 2012, or did I mess something?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99201 Visits: 33014
opc.three (3/27/2013)
Grant Fritchey (3/27/2013)
Actually, if you use extended events you can filter by batch_text in sql_batch_completed or by statement in rpc_completed and then use the like_i_sql_unicode_string to do a like statement looking for that particular table or even that particular column in reference in the statement. And, since this type of filtering in extended events is done at a lower level within process, it should have very low impact.

I tried a task like this on a Database Snapshot not too long but found it to be impossible with EE in 2008. The predicates you mentioned look to be 2012, or did I mess something?


Oops, yeah, for 2008 you don't have those predicates. Jonathan has a suggestion here how you can still use ExEvents to get the job done though.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40178 Visits: 14413
Grant Fritchey (3/27/2013)
opc.three (3/27/2013)
Grant Fritchey (3/27/2013)
Actually, if you use extended events you can filter by batch_text in sql_batch_completed or by statement in rpc_completed and then use the like_i_sql_unicode_string to do a like statement looking for that particular table or even that particular column in reference in the statement. And, since this type of filtering in extended events is done at a lower level within process, it should have very low impact.

I tried a task like this on a Database Snapshot not too long but found it to be impossible with EE in 2008. The predicates you mentioned look to be 2012, or did I mess something?


Oops, yeah, for 2008 you don't have those predicates. Jonathan has a suggestion here how you can still use ExEvents to get the job done though.

It's funny how Jonathan's name comes up when looking for help with EE. I ran into that same thread when tracking my aforementioned Snapshot-auditing issue: http://www.sqlservercentral.com/Forums/Topic1395766-1550-1.aspx

I ended up giving up on EE for adding auditing to my snapshot and ended up going with Trace. I was seeing some erratic beahvior with the sqlserver.lock_acquired event plus the solution Jonathan proposed where we would use the sqlserver.sql_text and sqlserver.tsql_stack actions was a little on the heavy side for the instance I would have been adding it too.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
DVSQL
DVSQL
Right there with Babe
Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)

Group: General Forum Members
Points: 733 Visits: 349
So, I've looked into DML Triggers, and it seems like the way to go, but the only thing I need is to capture actual syntax or commands being executed on this table, a bit like the SQL Profiler does.
Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72781 Visits: 40951
inside the trigger, you can capture the first 4000 chars of the command text:

inside a trigger, you can do this, (DBCC INPUTUFFER can be used by users agasint their own spid)


--################################################################################################
--because dbcc inputbuffer is limited to 4000 chars, you may need to combine this witha DML trace
--################################################################################################
DECLARE @SQLBuffer nvarchar(4000)

DECLARE @buffer TABLE (
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000)
)
INSERT @buffer
EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'

SELECT @LASTCOMMAND = EventInfo
FROM @buffer



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search