identify if heaps are being used by applications

NeophyteSQL 241 Reputation points
2021-03-16T14:14:48.45+00:00

any table with atleast one index can be queried for index stats usage to identify if the table is being actively used or not

how to identify if heaps are being actively used by applications or not

we have thousands of heaps and most of them are just temporarily created , how we we know which ones are real and are being used by applications if no index is there

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,757 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Shashank Singh 6,246 Reputation points
    2021-03-16T14:23:34.377+00:00

    The DMV sys.dm_db_index_usage_stats will give information both about indexes and heap tables. See below blog

    How to find out the last access date and time of a table or view

    0 comments
  2. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-03-17T03:09:12.033+00:00

    Hi @NeophyteSQL ,

    You can check user_seeks, user_scans, user_lookups, user_updates column in the sys.dm_db_index_usage_stats for heaps.
    For example: 

    SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],   
       I.[NAME] AS [INDEX NAME],   
       USER_SEEKS,   
       USER_SCANS,   
       USER_LOOKUPS,   
       USER_UPDATES   
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S   
       INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID   
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1  
       And S.INDEX_ID=0

    Please refer to sys.dm_db_index_usage_stats which might help.
    Best Regards,
    Amelia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

    0 comments