0

I tried browsing this across lot of links but cant get much of help so i am posting this question which sound bit similar.

We have quite some tables which are being planned from our app team that they will stop sending data in next couple of months. So these tables would be good contender for clean up later some time.

However how can i find , if there is way within SQL server, some additional user which we are not aware might be using that table for XYZ operation.

Is there a way natively available or will i need XE to track this? This is an OLTP database server highly active throughout, so running any audit would be something to test.

What should be the approach and how to practically deal with this situation? Please suggest.

Thank you!

3
  • 1
    If you want to tie to a specific user then an XE session would be your best bet. But you can test IF a table is being used by tracking the counters in sys.dm_db_index_usage_stats Feb 18 at 23:29
  • @JonathanFite: Thanks , I will wait for few other if they have some ideas. Also is there a specific XE i can run to gather it?
    – Newbie-DBA
    Feb 19 at 0:15
  • + 1 on XE and sys.dm_db_index_usage_stats. Although I would start with a search to see if those tables are still used in any SP, function or view (I've had my fare share of surprises) and then I would enable a XE session to see if there are any incoming queries at all using those tables (there is some performance impact, I know in my environment I can get away with it, you might want to check that for your server) and I would double that with checking the index & statistics usage. You might want to make sure you have a backup available when you delete them. You know, just in case.
    – LadyBug1
    Feb 19 at 10:47
0

I think your best approach would be to start with just tracking the counters in sys.dm_db_index_usage_stats. Just store the results off to a table (and have that run on a schedule) for as long as you feel necessary. Then look at the counters to see if there is significant movement on them. There will always be a little, especially if you have maintenance going.

If you think they are done, then rename the table and see if anyone screams. You can quickly rename it back if you need to.

Finally, if you want an extended events session to track, the below might work for you. As you are aware, it has an impact, but it's not going to be as bad as you might think, especially for a table that you think is not being used. Still, when you first enable it, test it to make sure it catches what you want, as well as that it's impact isn't hindering your users. However, it's the only sure way I know to catch this type of thing.

The below is what I typically use when I want to catch usage of specific stored procedures or queries that contain certain text strings. I send it to a ring buffer because I typically only run it until I've got what I'm looking for. But you can alter the target to whatever you want. It creates the XE turned off, so you can go back in and edit it before starting it up.

CREATE EVENT SESSION [SpecificSQLWatch] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%<my string here>%')))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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