March 10, 2016 at 11:07 am
I want to use extended events. Has someone tried before? I only want activity against a particular table.
If someone has the TSQL snippet for this, I will appreciate if you can share it 🙂
March 10, 2016 at 11:44 am
This is easy and difficult at the same time. Tracking an individual table, I assume you want READ/UPDATE/DELETE/INSERT is not something normally done. It's very easy with a batch operation:
CREATE EVENT SESSION [QueryPerformance] ON SERVER
ADD EVENT sqlserver.sql_batch_completed (SET collect_batch_text = (1)
ACTION (sqlserver.sql_text)
WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],
N'AdventureWorks2014')
AND [sqlserver].[like_i_sql_unicode_string]([batch_text],
N'%Production.Document%')))
ADD TARGET package0.event_file (SET filename = N'QueryPerformance');
I use the LIKE operator and it's just like querying the data. However, the issue comes from the rpc_completed event. You can't peer into the text in the same manner. There, you just need to know which procedures access that table and then list them out by OBJECT_ID in the WHERE clause of the SESSION using OR. I would certainly filter by database first in order to reduce the overhead of additional filters.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2016 at 11:54 am
Grant Fritchey (3/10/2016)
This is easy and difficult at the same time. Tracking an individual table, I assume you want READ/UPDATE/DELETE/INSERT is not something normally done. It's very easy with a batch operation:
CREATE EVENT SESSION [QueryPerformance] ON SERVERADD EVENT sqlserver.sql_batch_completed (SET collect_batch_text = (1)
ACTION (sqlserver.sql_text)
WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],
N'AdventureWorks2014')
AND [sqlserver].[like_i_sql_unicode_string]([batch_text],
N'%Production.Document%')))
ADD TARGET package0.event_file (SET filename = N'QueryPerformance');
I use the LIKE operator and it's just like querying the data. However, the issue comes from the rpc_completed event. You can't peer into the text in the same manner. There, you just need to know which procedures access that table and then list them out by OBJECT_ID in the WHERE clause of the SESSION using OR. I would certainly filter by database first in order to reduce the overhead of additional filters.
Awesome, Thanks Grant
Yes, I only need the basic, reads and inserts. I don't think we update that table.
So... will above allows me that? I did not understand your last bart about knowing the store procedure names. I wanted to see all the sprocs that touch that table live.
March 10, 2016 at 12:47 pm
Grant,
I just tried that Extended Event session on my Dev box and it's not capturing anything 🙁 I tried a simple store procedure reading from a simple table and a pure SELECT statement. Nothing came out.
:unsure:
March 10, 2016 at 1:04 pm
sql-lover (3/10/2016)
Grant Fritchey (3/10/2016)
This is easy and difficult at the same time. Tracking an individual table, I assume you want READ/UPDATE/DELETE/INSERT is not something normally done. It's very easy with a batch operation:
CREATE EVENT SESSION [QueryPerformance] ON SERVERADD EVENT sqlserver.sql_batch_completed (SET collect_batch_text = (1)
ACTION (sqlserver.sql_text)
WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],
N'AdventureWorks2014')
AND [sqlserver].[like_i_sql_unicode_string]([batch_text],
N'%Production.Document%')))
ADD TARGET package0.event_file (SET filename = N'QueryPerformance');
I use the LIKE operator and it's just like querying the data. However, the issue comes from the rpc_completed event. You can't peer into the text in the same manner. There, you just need to know which procedures access that table and then list them out by OBJECT_ID in the WHERE clause of the SESSION using OR. I would certainly filter by database first in order to reduce the overhead of additional filters.
Awesome, Thanks Grant
Yes, I only need the basic, reads and inserts. I don't think we update that table.
So... will above allows me that? I did not understand your last bart about knowing the store procedure names. I wanted to see all the sprocs that touch that table live.
If you are hoping to capture stored procedure calls using this is, you won't be able to do the same LIKE command. You'll have to have each object_id for each procedure that accesses the table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2016 at 1:05 pm
sql-lover (3/10/2016)
Grant,I just tried that Extended Event session on my Dev box and it's not capturing anything 🙁 I tried a simple store procedure reading from a simple table and a pure SELECT statement. Nothing came out.
:unsure:
Be patient? It works. I tested it several times before I posted it. Make sure you have the correct spelling, the right wild cards, the correct database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2016 at 1:11 pm
Grant Fritchey (3/10/2016)
sql-lover (3/10/2016)
Grant,I just tried that Extended Event session on my Dev box and it's not capturing anything 🙁 I tried a simple store procedure reading from a simple table and a pure SELECT statement. Nothing came out.
:unsure:
Be patient? It works. I tested it several times before I posted it. Make sure you have the correct spelling, the right wild cards, the correct database.
Patient is not one of my virtues, lol...
But let me see if I understand correctly. The Extended Event above, should display any sproc or AdHoc activity that try to touch that table. Is that correct?
What I am trying to do is see when an specific table on one of my databases, has zero activity. Is not too big but important. I need to change a non clustered Index there with the less possible impact, less amount of blocks. If I am able to deploy the Index just in that timeframe, nobody will notice it and won't have to wait for Maint. window. Hence why I am trying to see activity real time with Extended Events, to see when is "quiet", for that table.
March 10, 2016 at 3:10 pm
sql-lover (3/10/2016)
Grant Fritchey (3/10/2016)
sql-lover (3/10/2016)
Grant,I just tried that Extended Event session on my Dev box and it's not capturing anything 🙁 I tried a simple store procedure reading from a simple table and a pure SELECT statement. Nothing came out.
:unsure:
Be patient? It works. I tested it several times before I posted it. Make sure you have the correct spelling, the right wild cards, the correct database.
Patient is not one of my virtues, lol...
But let me see if I understand correctly. The Extended Event above, should display any sproc or AdHoc activity that try to touch that table. Is that correct?
No. That is only for the ad hoc queries. Not for stored procs.
Stored procs need a different event, rpc_complete, and, you have to filter the procs by object_id. You can't filter them by a table name. You have to identify the procedures that reference that table, and then put their object_ids into the filter using OR conditions.
What I am trying to do is see when an specific table on one of my databases, has zero activity. Is not too big but important. I need to change a non clustered Index there with the less possible impact, less amount of blocks. If I am able to deploy the Index just in that timeframe, nobody will notice it and won't have to wait for Maint. window. Hence why I am trying to see activity real time with Extended Events, to see when is "quiet", for that table.
As I said, this does work. I validated that it captured ad hoc events on my system. It will work.
Another way to just check access of a system is to look at the DMV sys.dm_db_index_usage_stats. That'll show what access has been done to a table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2016 at 10:33 pm
check out the following link maybe this will help you out: http://serverfault.com/questions/111510/how-to-monitor-activity-on-a-single-table-using-sql-server-profiler
March 11, 2016 at 6:01 am
johnwalker10 (3/10/2016)
check out the following link maybe this will help you out: http://serverfault.com/questions/111510/how-to-monitor-activity-on-a-single-table-using-sql-server-profiler
Two points on that answer. One, it's doing the same thing with trace that I outlined with extended events above. Two, trace is on the deprecation list. If you're working in SQL Server 2012 or greater, it's time to start the move to extended events. Any release now, trace will no longer be there. Plus, all the new functionality in 2008R2 and better is only serviced by extended events. They haven't added a single trace event since then.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2016 at 7:57 am
johnwalker10 (3/10/2016)
check out the following link maybe this will help you out: http://serverfault.com/questions/111510/how-to-monitor-activity-on-a-single-table-using-sql-server-profiler
Thanks for reply.
But agree with Grant. Extended Events is more lightweight and I am trying to force myself (even my coworkers) to stay away from Profiler.
The only thing I miss from Profiler is that I was able to combine Perf Monitor with traces. On the top, the perf. monitor graph. Bottom, the Profiler info. MS removed that option with Extended Events and I think it was a bad decision. It allows you to see Os metrics combined with MSSQL metrics, same window.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply