marcingminski
Table [sqlwatch_logger_index_usage_stats] never gets populated
Table [sqlwatch_logger_index_usage_stats] never gets populated.
I am using the latest SQLWATCH version.
I am facing the same problem with SQLWATCH 4.3 and cannot find a way to fix it. Please help
Yes, all relevant sqlwatch tables have up-to-date data [dbo].[sqlwatch_meta_index] [dbo].[sqlwatch_meta_database] [dbo].[sqlwatch_meta_table]
Just looked into this and something is weird.
The procedure creates ##sqlwatch_index_usage_stats_collector_1546356805384099A7534C851E48C6D1 and uses sqlwatch_logger_index_usage_stats to populate it. But when starting fresh, this table is empty. Then to populate sqlwatch_logger_index_usage_stats, if the index_usage_age is not negative, there is a join on that temp table. So if the index_usage_age parameter is not changed to something negative, it won't ever get populated.
I changed "Index Usage Age Hours" to -1 in the config to not go into that part of the procedure and it works.
Yeah, I think what’s happening is if this is set to > 0, it tries to figure out what stats to collect but on the very first run, it won’t find anything yet so it will just quit.
line 16 https://github.com/marcingminski/sqlwatch/blob/main/SqlWatch.Monitor/Project.SqlWatch.Database/dbo/Procedures/usp_sqlwatch_logger_index_usage_stats.sql
when I developed this functionality, I must have had data in the table already so “it worked on my PC” :)
Setting the age to -1 makes it work. Also, in the code, you set an offset in minutes, but in the settings you state "hours" and a value of 24. I'd recommend commenting out on your own install this whole section for now for those who don't need it.