search

marcingminski / sqlwatch

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

Service broker based data collection not populating query plans for long running queries #342

Closed TArmstrong24 closed 3 years ago

TArmstrong24 commented 3 years ago

Did you check DOCS to make sure there is no workaround? yes

Describe the bug On SQL Server express with service broker based data collection, the query plans for long running queries (and I believe others) are not being populated

To Reproduce Steps to reproduce the behavior:

  1. Install SQL Express
  2. Install SQLWatch via dbatools method
  3. Enable queues using [dbo.usp_sqlwatch_internal_migrate_jobs_to_queues]
  4. Execute long running query
  5. Check sqlwatch_meta_query_plan table

Additional context When using agent jobs, the SQLWATCH-INTERNAL-CONFIG job executes the [usp_sqlwatch_internal_add_procedure] to populate the [sqlwatch_meta_procedure] table with a list of stored procedures. This table is crucial to the logging of query plans via procedure [usp_sqlwatch_logger_xes_long_queries] as it calls [usp_sqlwatch_internal_get_query_plans] which performs a inner join to the procedures table. This will filter out any plan details that would be logged if that procedure does not exist in the table. When using the service broker based approached to replace jobs, when the queue is activated, it calls the procedure [usp_sqlwatch_internal_exec_activated] which executes the collection procedures in a similar way as the agent jobs but the execution of the [usp_sqlwatch_internal_add_procedure] is missing. I plan to submit a pull request to add this in.

marcingminski commented 3 years ago

good catch. thanks!

marcingminski commented 3 years ago

Fixed in https://github.com/marcingminski/sqlwatch/pull/343