Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Running SQL Profiler Trace on a specific table Expand / Collapse
Author
Message
Posted Tuesday, March 26, 2013 11:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:52 PM
Points: 75, Visits: 285
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.
Post #1435630
Posted Tuesday, March 26, 2013 12:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1435654
Posted Tuesday, March 26, 2013 4:37 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:52 PM
Points: 75, Visits: 285
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.
Post #1435710
Posted Tuesday, March 26, 2013 7:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1435728
Posted Wednesday, March 27, 2013 6:54 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 15,501, Visits: 27,887
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1435874
Posted Wednesday, March 27, 2013 7:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1435895
Posted Wednesday, March 27, 2013 9:09 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 15,501, Visits: 27,887
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1435972
Posted Wednesday, March 27, 2013 9:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1436006
Posted Friday, April 12, 2013 1:03 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:52 PM
Points: 75, Visits: 285
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.
Post #1441864
Posted Friday, April 12, 2013 1:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:11 PM
Points: 12,876, Visits: 31,789
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1441912
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse