SQLWATCH 4.5 XES "SQLWATCH_query_problems" slows performance of database creation

[monteroman]

Did you check DOCS to make sure there is no workaround?
https://sqlwatch.io/docs/

Describe the bug
This was something we noticed when creating a new SharePoint database on our SP2019 environment which runs many queries to create the database. I also so this on one of our monitoring servers during an upgrade. When the SQLWATCH_query_problems Extended Event session is running, it can take about an hour to create a new content database for SharePoint. When I stop the XES, and go back and create another database, it takes 2 minutes.

To Reproduce
Steps to reproduce the behavior:

1. Go to SSMS --> Management --> Extended Events --> Session
2. Turn off SQLWATCH_query_problems
3. In SharePoint, create a new content database through the PowerShell command "New-SPContentDatabase" - time is about 2 minutes
4. Turn on SQLWATCH_query_problems
5. In SharePoint, create a new content database through the PowerShell command "New-SPContentDatabase" - time to complete is about 55 minutes.

Expected behavior
The process should only take a couple of minutes to complete.

Screenshots

Windows Server (please complete the following information):

• Win2019 Server, latest OS Patches through 11/2022

SQL Server (please complete the following information):

• SQL Version: 2019 (15.0.4261.1)
• SQL Edition: Standard Edition

SQL Server Management Studio (SSMS -> about -> copy info):
SQL Server Management Studio 15.0.18424.0
SQL Server Management Objects (SMO) 16.100.47021.0+7eef34a564af48c5b0cf0d617a65fd77f06c3eb1
Microsoft Analysis Services Client Tools 15.0.19750.0
Microsoft Data Access Components (MDAC) 10.0.17763.3650
Microsoft MSXML 3.0 6.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.17763

SQLWATCH version (from DACPAC or from sysinstances)

• 4.5.0.534

Additional context
I had opened a case with Microsoft to help with trying to determine if it was a SQL issue or a SharePoint issue, but they were able to prove it wasn't SharePoint and they could not determine why SQL was causing the problem. They ran PSS Diag's on SQL and tried to find something in the log files, but were unable to. We were able to come up with the same slowness results in both our dev environment and prod environment. In both instances, when I shut off this XES, the issue disappears.

[marcingminski]

Was this session enabled by default after installation? I don’t think this should be enabled all the time but I can’t find any guidance where it states that.

[monteroman]

Yes it was. The all were enabled except for the query_health one.

…

On Dec 21, 2022, at 11:26 AM, Marcin Gminski ***@***.***> wrote: Was this session enabled by default after installation? I don’t think this should be enabled all the time but I can’t find any guidance where it states that. — Reply to this email directly, view it on GitHub <#466 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABJS2IT67MPH32FGN2OHMNTWOMVU5ANCNFSM6AAAAAATFXOPGQ>. You are receiving this because you authored the thread.

[marcingminski]

Thank you for this. I’ll revise it.

[monteroman]

Should any of them be enabled upon DACPAC being installed?

…

On Dec 21, 2022, at 11:29 AM, Marcin Gminski ***@***.***> wrote: Thank you for this. I’ll revise it. — Reply to this email directly, view it on GitHub <#466 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABJS2IWPA3BIOEN65GWAYO3WOMV6XANCNFSM6AAAAAATFXOPGQ>. You are receiving this because you authored the thread.

[marcingminski]

• Blockers enable for sure.
• Waits - enable as it captures queries that causes excessive waits (if you have lots of queries with waits this session could cause trouble)
• Long queries - disable and I’d expect people to tweak this to their needs (you know - how long query is a long query)
• Health - disable, it causes problems
• Query problems - (can’t find the guidance but I was expecting this to be turned on on demand for a specific need)

[monteroman]

Thanks for the guidance. I will go through and make some adjustments in my environment based on this info. I really appreciate it!

…

On Dec 21, 2022, at 11:36 AM, Marcin Gminski ***@***.***> wrote: Blockers enable for sure. Waits - enable as it captures queries that causes excessive waits (if you have lots of queries with waits this session could cause trouble) Long queries - disable and I’d expect people to tweak this to their needs (you know - how long query is a long query) Health - disable, it causes problems Query problems - (can’t find the guidance but I was expecting this to be turned on on demand for a specific need) — Reply to this email directly, view it on GitHub <#466 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABJS2IXQKFM7NQCZ4HA4NTTWOMWXPANCNFSM6AAAAAATFXOPGQ>. You are receiving this because you authored the thread.

[marcingminski]

You’re welcome and I really appreciate you raising this as otherwise it would have never been captured.

[monteroman]

In future releases of the DACPAC, can you set those XES to stopped by default and only leave the Block one running?

…

On Dec 21, 2022, at 11:42 AM, Marcin Gminski ***@***.***> wrote: You’re welcome and I really appreciate you raising this as otherwise it would have never been captured. — Reply to this email directly, view it on GitHub <#466 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABJS2IUOUC546GJRW32SB23WOMXPZANCNFSM6AAAAAATFXOPGQ>. You are receiving this because you authored the thread.

[marcingminski]

Yes that’s the plan (and I was under the impression that this was already done this way)

[monteroman]

Excellent. Thank you for the follow-up. I love this tool! I’m monitoring about 80 SQL servers with it along with Grafana as the web UI.

…

On Dec 21, 2022, at 11:45 AM, Marcin Gminski ***@***.***> wrote: Yes that’s the plan (and I was under the impression that

[monteroman]

You can go ahead and close this issue since now, after turning off the XES, my performance issues were resolved immediately.

[marcingminski]

Thanks. I’ll keep it open until I fix the XES status during deployment.