I'm running the below SQL query. I know my database has over 500 tables but the query only returns stats on 288 tables. Why doesn't it return something for all the tables? Below is an example of what I'm running.
SELECT OBJECT_NAME(OBJECT_ID) AS [Table],
Last_user_update, Last_user_seek, Last_user_scan, Last_user_lookup
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('table name')
Hi,@Hammy0607-2379
Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)
Bert Zhou
When I ran that query in a database with 1800 tables on my server I did not get back a single row.
But that is not very strange - I have not executed any queries in this database since I restarted SQL Server.
So there you have it. Any table or index not listed in that query have not been read or written to since last time SQL Server was started. Or more precisely, since the database came online.
Hi，@Hammy0607-2379
Welcome to Microsoft T-SQL Q&A Forum!
The phenomenon of not returning may of course occur , the count of index operations returned by sys.dm_db_index_usage_stats and the time of the last execution.
So, you need to check if there is really index maintenance on these tables , secondly , whenever you restart your database , it will make the counter empty , you can use the column in sys.dm_os_sys_infosqlserver_start_time to find the last database engine start time .
Finally, please refer to the official documentation which is well documented for these claims.
Best regards,
Bert Zhou
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.
17 people are following this question.
