“user_scans” of table “sys.dm_db_index_usage_stats”

  • I am trying to find answer to the below.

    What exactly the scan means for the field "user_scans" being with the table "sys.dm_db_index_usage_stats" ?

    Like, there are tables in our prod which aren't used for sure as the module is shutdown 4 years back but the DB holds all the tables related to that module. So, was trying to list out the untouched tables against the DB prior to upgrading our SQL. But found that user_scans for those untouched tables are getting updated on certain occasions. But unable to trace back the reasons.

    So need some more info on what scenarios does these user_scans gets updated? Anyone? - Thanks in advance!

  • Have you checked the official docs:


    From there you can see what User Scans is showing you - Number of scans by user queries that did not use 'seek' predicate. So while you may think nobody is using the data, someone is running a query against it. Could be a report that shouldn't be needed anymore but someone is running it, could be a data sync operation from some tools (Tableau, Power BI, etc), could be someone made a live excel and forgot to turn that data connection off, could be an application doing some sanity check on the table, could be someone intentionally querying the table to watch for data changes, could be a reporting solution set to watch for table changes, could be a lot of things, but someone is requesting data from the table. Even if the module is shut down, are you 100% certain that nothing is using the table?

    I would look at the column last_user_scan column as that'll tell you when the last time a scan happened on that index. If that value is today (or recent) then someone is definitely using it. If you want to find out what, there are multiple approaches to this. There is the "safe but patient" approach - set up an extended events trace and watch for when that table gets hit and you will know who did it and the query being run. There is the "safe but hopeful" approach - search through all of your stored procedure text to see if one of those is calling that table. There is the "watch the world burn" approach - disable access to the table.

    My preference is the "safe but hopeful" approach if you are confident that all applications (including reports and live excels) accessing the data are using a stored procedure. It helps me determine what is using the data and helps me work with the business to correct it. IF that isn't an option, then the "safe but patient" approach is the next safest way as you can see who ran it and the query they ran so you can try to work with the user/business to correct the behavior. The "watch the world burn" is my last resort approach as best case scenario, nothing breaks because the query being run was from some automated system that isn't in use. Worst case scenario, upper management has their report fail to load during a shareholder meeting and you get an angry phone call.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Thank you Brian! Thanks for the hints.

    And yes, I read the doc, but can't think beyond.

  • Turn on query monitoring (Extended Events first, Query Store second, DMVs third, ignore everything else) and see what queries are being run on the system. Done.

    "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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply