search

marcingminski / sqlwatch

SQL Server Performance Monitor
https://docs.sqlwatch.io
Other
428 stars 168 forks source link

Table [sqlwatch_logger_index_usage_stats] never gets populated #425

Open RJAF001 opened 2 years ago

RJAF001 commented 2 years ago

Table [sqlwatch_logger_index_usage_stats] never gets populated.

I am using the latest SQLWATCH version.

grischtian commented 2 years ago

I am facing the same problem with SQLWATCH 4.3 and cannot find a way to fix it. Please help

marcingminski commented 2 years ago

Is [dbo].[sqlwatch_meta_index] populated?

grischtian commented 2 years ago

Yes, all relevant sqlwatch tables have up-to-date data [dbo].[sqlwatch_meta_index] [dbo].[sqlwatch_meta_database] [dbo].[sqlwatch_meta_table]

PierreLetter commented 2 years ago

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.

marcingminski commented 2 years ago

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” :)

PierreLetter commented 2 years ago

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.