search

marcingminski / sqlwatch

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

SQLWATCH 4.5 XES "SQLWATCH_query_problems" slows performance of database creation #466

Open monteroman opened 1 year ago

monteroman commented 1 year ago

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 image

Windows Server (please complete the following information):

SQL Server (please complete the following information):

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)

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 commented 1 year ago

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 commented 1 year ago

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 https://github.com/marcingminski/sqlwatch/issues/466#issuecomment-1361619699, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABJS2IT67MPH32FGN2OHMNTWOMVU5ANCNFSM6AAAAAATFXOPGQ. You are receiving this because you authored the thread.

marcingminski commented 1 year ago

Thank you for this. I’ll revise it.

monteroman commented 1 year ago

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 https://github.com/marcingminski/sqlwatch/issues/466#issuecomment-1361624526, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABJS2IWPA3BIOEN65GWAYO3WOMV6XANCNFSM6AAAAAATFXOPGQ. You are receiving this because you authored the thread.

marcingminski commented 1 year ago
monteroman commented 1 year ago

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 https://github.com/marcingminski/sqlwatch/issues/466#issuecomment-1361634763, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABJS2IXQKFM7NQCZ4HA4NTTWOMWXPANCNFSM6AAAAAATFXOPGQ. You are receiving this because you authored the thread.

marcingminski commented 1 year ago

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

monteroman commented 1 year ago

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 https://github.com/marcingminski/sqlwatch/issues/466#issuecomment-1361653159, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABJS2IUOUC546GJRW32SB23WOMXPZANCNFSM6AAAAAATFXOPGQ. You are receiving this because you authored the thread.

marcingminski commented 1 year ago

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

monteroman commented 1 year ago

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 commented 1 year ago

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

marcingminski commented 1 year ago

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