[inputs.sqlserver] Set deadlock priority to LOW

[OsirisDBA]

Relevant telegraf.conf:

[[inputs.sqlserver]]
servers = [
"Server=localhost;Port=1433;User Id=####;Password=####&;app name=telegraf;log=1;",
]
query_version = 2

System info:

Telegraf 1.8.2 (git: HEAD 996a91f)
OS: Windows 6.3.9600

Steps to reproduce:

When detaching a database via DBCC DETACHDB at the same time as metrics are gathering a deadlock is possible.

Expected behavior:

telegraf yields to other operations

Actual behavior:

If both have the same deadlock priority, telegraf can block the other activity.

Additional info:

Sanitized data from deadlock SQL log.

deadlock-list
deadlock victim=process1f2bd3c28
process-list
process id=process1f2bd3c28 taskpriority=0 logused=1148 waitresource=KEY: 1:281474978152448 (a8ac32f52a91) waittime=2588 ownerId=997251658 transactionname=DetachDB lasttranstarted=2018-11-14T01:32:06.173 XDES=0x1adfd23b0 lockMode=X schedulerid=6 kpid=3484 status=suspended spid=55 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2018-11-14T01:32:06.183 lastbatchcompleted=2018-11-14T01:32:06.173 lastattention=2018-11-14T01:32:06.057 clientapp=dbatools PowerShell module - dbatools.io hostname=##### hostpid=1620 loginname=##### isolationlevel=read committed (2) xactid=997251658 currentdb=1 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtend=56 sqlhandle=0x01000100440b182500a786b90100000000000000000000000000000000000000000000000000000000000000
DBCC DETACHDB ([NewDBName]
frame procname=mssqlsystemresource.sys.sp_detach_db line=83 stmtstart=3554 stmtend=3592 sqlhandle=0x0300ff7f708ec8e719d0e1008ea8000001000000000000000000000000000000000000000000000000000000
EXEC (@exec_stmt)
frame procname=adhoc line=1 sqlhandle=0x010001000f131e2c10a886b90100000000000000000000000000000000000000000000000000000000000000
EXEC master.dbo.sp_detach_db @dbname = N'NewDBName'
inputbuf
EXEC master.dbo.sp_detach_db @dbname = N'NewDBName'
process id=process1f0b75088 taskpriority=0 logused=10000 waitresource=DATABASE: 25:0 waittime=625 schedulerid=3 kpid=1032 status=suspended spid=62 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2018-11-14T01:32:06.063 lastbatchcompleted=2018-11-14T01:32:06.067 lastattention=1900-01-01T00:00:00.067 clientapp=telegraf hostname=##### hostpid=0 loginname=##### isolationlevel=read committed (2) xactid=997250650 currentdb=1 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=34 stmtstart=2176 stmtend=2966 sqlhandle=0x02000000331a4e3b15fd1444b9964d73edf47df97893c3410000000000000000000000000000000000000000
unknown
inputbuf
DECLARE @sys_info TABLE ( cpu_count INT, server_memory BIGINT, sku NVARCHAR(64), engine_edition SMALLINT, hardware_type VARCHAR(16), total_storage_mb BIGINT, available_storage_mb BIGINT, uptime INT ) IF OBJECT_ID('master.sys.dm_os_sys_info') IS NOT NULL BEGIN IF SERVERPROPERTY('EngineEdition') = 8 -- Managed Instance INSERT INTO @sys_info ( cpu_count, server_memory, sku, engine_edition, hardware_type, total_storage_mb, available_storage_mb, uptime ) SELECT TOP(1) virtual_core_count AS cpu_count, (SELECT process_memory_limit_mb FROM sys.dm_os_job_object) AS server_memory, sku, cast(SERVERPROPERTY('EngineEdition') as smallint) AS engine_edition, hardware_generation AS hardware_type, reserved_storage_mb AS total_storage_mb, (reserved_storage_mb - storage_space_used_mb) AS available_storage_mb, (select DATEDIFF(MINUTE,sqlserver_start_time,GETDATE()) from sys.dm_os_sys_info) as uptime FROM sys.server_resource_stats ORDER BY start_time DESC ELSE BEGIN DECLA
resource-list
keylock hobtid=281474978152448 dbid=1 objectname=master.sys.sysbrickfiles indexname=clst id=lockaff27000 mode=U associatedObjectId=281474978152448
owner-list
owner id=process1f0b75088 mode=S
waiter-list
waiter id=process1f2bd3c28 mode=X requestType=convert
databaselock subresource=FULL dbid=25 dbname=NewDBName lockPartition=0 id=lock68844f980 mode=X
owner-list
owner id=process1f2bd3c28 mode=X
waiter-list
waiter id=process1f0b75088 mode=S requestType=wait

[danielnelson]

Thanks for the report, I'm not familiar with how this works in SQLServer, is this something that would require a change to the query?

[OsirisDBA]

Correct, it's just a prefix of the queries with "SET DEADLOCK_PRIORITY -10;" so that they run with the lowest possible priority level instead of running as level 0 ( aka NORMAL ). I'd do it myself but I'm not a contributor and cannot yet sign the agreement.

[danielnelson]

@m82labs Can you take a look at this one?

[m82labs]

Seems like a good change. I am getting ready to go out of town but can implement this change when I get back. I recently found an issue with that same query on systems with large numbers of databases and/or files and intend to fix that. I can roll this change in as well.

…

On Fri, Nov 16, 2018 at 14:09 Daniel Nelson ***@***.***> wrote: @m82labs <https://github.com/m82labs> Can you take a look at this one? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#4988 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AIS1dPv_ITACEg5J1K07ihRFQSnRa33fks5uvw1UgaJpZM4YeJDg> .