system_health configuration
-
I'm on SQL Server 2016 (SP2-CU11-GDR) (KB4535706) - 13.0.5622.0 (X64) Dec 15 2019 08:03:11 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
Is there a way to change the lock timeout in System_Health GUI? In the u_table.sql it looks like it's seto to 30 seconds now. Our jdbc connections are set to 30. I tried to modfiy and rerun in dev, but it causes issues with spt_values. So, it seems that it has to executed in a dedicated connection. For production that is a bit of a problem.
I guess we could change our lock timeout to 29, but that would also be another day of waiting.
-
I'm not sure I understand what system_health GUI you're referring to? Can you clarify a bit? There is a system_health Extended Events session. You can stop that, and modify it, then start it again. However, the next time Microsoft updates it and you update your SQL Server instance, it'll get changed to whatever Microsoft wants it to be.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Right mouse click on System_Health and go to Properties. It will load this GUI screen:
-
Oh, right. That's the Extended Events GUI, not system_health. Sorry. I misunderstood.
System_health is just a monitoring tool. It shouldn't be messing with your systems in any way. You can set your timeout what you want. System_health isn't going to change it or affect it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
So system health is not reporting anything because the timeout is set to 30 and in the code of u_tables.sql at line 187 (at least on mine):
(duration > 30000 -- Waits for locks that have exceeded 30 secs.
and wait_type <= N'LCK_M_RX_X' -- all lock waits
I think that controls system health and says only report if it's over 30 seconds.
With our jdbc is set to time out at equal to 30, it never gets reported by system_health as it has to be > 30 seconds.
-
OK, I'm a little confused as to what it is that you're looking to get here.
You're describing lock waits, which system_health captures. Locks that exceed 30 seconds get reported. It's a great way to see long running queries after the fact if you don't have your monitoring enabled. What does that have to do with connection timeouts?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
"Is there a way to change the lock timeout in System_Health GUI? In the u_table.sql it looks like it's seto to 30 seconds now. Our jdbc connections are set to 30."
After connections should have read "connection lock time out" as in the properties of the configuration / connection string.
Anyhow, I found how to change the timeout in an Extended Event https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/blocked-process-threshold-server-configuration-option?view=sql-server-ver16
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply