Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way I can find out the number of times a table is read, updated, added to or has had rows deleted during a trace?

I am creating the trace in SQL using sp_trace_setstatus, not using the Sql Profiler UI.

I am using sp_trace_setevent and ::fn_trace_gettable to get my current stats, but cannot find out how to get the stats regarding the table.

I would like to run this trace on both SQL 2000 and SQL 2008 R2.

share|improve this question
    
Why tags for both 2000 and 2008 R2? –  Martin Smith Jul 26 '13 at 12:04
    
@MartinSmith Updated my question, thanks. –  rhughes Jul 26 '13 at 12:23
add comment

1 Answer

In SQL Server Profiler, I tested this using SQL Server 2008. I don't know if the output will be the same in MSSQL 2000.

Using the template: TSQL_Replay

  1. Select the Events Selection Tab

  2. Check Show all events

  3. Expand Performance then check Showplan XML

  4. When you run your trace you will see the following events appear

enter image description here

When you select Showplan XML, you will see the Execution plan generated by the query

enter image description here

If you hover your mouse over the Delete event, as in my example you will get the number of rows and other information related to the query

Update 15:50

Here is the SQL exported by profiler for the showplan XML event.

exec sp_trace_setevent @TraceID, 122, 8, @on
exec sp_trace_setevent @TraceID, 122, 1, @on
exec sp_trace_setevent @TraceID, 122, 9, @on
exec sp_trace_setevent @TraceID, 122, 2, @on
exec sp_trace_setevent @TraceID, 122, 10, @on
exec sp_trace_setevent @TraceID, 122, 14, @on
exec sp_trace_setevent @TraceID, 122, 26, @on
exec sp_trace_setevent @TraceID, 122, 3, @on
exec sp_trace_setevent @TraceID, 122, 11, @on
exec sp_trace_setevent @TraceID, 122, 35, @on
exec sp_trace_setevent @TraceID, 122, 51, @on
exec sp_trace_setevent @TraceID, 122, 12, @on
exec sp_trace_setevent @TraceID, 122, 60, @on

122 is the event and then there is an id for each column selected in profiler for Showplan XML when I exported the trace to a file.

Here is the list of events you can trace from Microsoft: Trace Events

share|improve this answer
    
Thanks. Do you know the direct SQL needed for this? –  rhughes Jul 26 '13 at 13:27
    
@rhughes, In profiler, start your trace and then stop it. Next select File -> Export -> Script Trace Definition -> For SQL Server and the version that you want –  Craig Efrein Jul 26 '13 at 13:41
    
I am doing this trace in SQL rather than the profiler itself using sp_trace_setstatus. I have updated my question to make it a bit clearer –  rhughes Jul 26 '13 at 13:49
    
@rhughes I have updated my answer –  Craig Efrein Jul 26 '13 at 13:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.