question

NeophyteSQL avatar image
0 Votes"
NeophyteSQL asked AmeliaGu-msft commented

identify if heaps are being used by applications

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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NeophyteSQL,
Are there any updates on this issue?
Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.
Best Regards,
Amelia

0 Votes 0 ·
Shashank-Singh avatar image
0 Votes"
Shashank-Singh answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.