Excessive plans generated by #MetricBeat

Elastic Stack Beats
1

Hi - I am monitoring queries generated by metric beat and can see that it iterates databases frequently, and then checks log usage on each database.
This is done in a way that causes plan cache pollution

USE CW-RefDb-Ent-AU-000025; SELECT database_id,total_log_size_mb,active_log_size_mb,log_backup_time,log_since_last_log_backup_mb,log_since_last_checkpoint_mb,log_recovery_size_mb FROM sys.dm_db_log_stats(633);

Given that 633 represents the database, there is no point 'USE'ing the database.
Also, failing to parameterise 633 means another plan is generated.

Finally, there is the ability to get call sys.dm_db_log_stats for all databases with a single query. This results in substantially less cpu/batches/plans being generated.

It would be great if the monitoring tools implemented best practices in SQL, rather than having to monitor the monitoring tools.

2

Hi @Brett_Shearer :slightly_smiling_face: Welcome to Elastic discuss forum!

We are happy to receive community contributed PR's in Github https://github.com/elastic/beats with enhancements and fixes. I encourage you to try or open an enhancement request :slightly_smiling_face:

3

Thanks Mario, coming from a MS SQL background, that is music to my ears!
Expect Beats to be tuned to the max :slight_smile:

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.