November 3, 2020 at 12:36 pm
All,
I would appreciate some advice on identifying an issue. The error I received is:
"The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time"
I was monitoring sys.dm_tran_locks and I saw the query is taking a lot of page locks. I'm therefore thinking that the issue is either:
a) The lock allocation memory became full
b) It tried to convert to a table lock and could not do so.
I'm trying to figure out how I prove what the issue is. I set up an extended event on lock_timeout and the output it as follows:
<event name="sp_server_diagnostics_component_result" package="sqlserver" timestamp="2020-11-03T11:34:46.104Z">
<data name="component">
<type name="sp_server_diagnostics_component" package="sqlserver" />
<value>2</value>
<text>QUERY_PROCESSING</text>
</data>
<data name="state">
<type name="sp_server_diagnostics_state" package="sqlserver" />
<value>1</value>
<text>CLEAN</text>
</data>
<data name="data">
<type name="xml" package="package0" />
<value>
<queryProcessing maxWorkers="576" workersCreated="80" workersIdle="36" tasksCompletedWithinInterval="52316" pendingTasks="0" oldestPendingTaskWaitingTime="0" hasUnresolvableDeadlockOccurred="0" hasDeadlockedSchedulersOccurred="0" trackingNonYieldingScheduler="0x0">
<topWaits>
<nonPreemptive>
<byCount>
<wait waitType="MEMORY_ALLOCATION_EXT" waits="15119358" averageWaitTime="0" maxWaitTime="31" />
<wait waitType="ASYNC_NETWORK_IO" waits="622911" averageWaitTime="0" maxWaitTime="969" />
<wait waitType="RESERVED_MEMORY_ALLOCATION_EXT" waits="482525" averageWaitTime="0" maxWaitTime="8" />
<wait waitType="PAGEIOLATCH_SH" waits="165708" averageWaitTime="48" maxWaitTime="5140" />
<wait waitType="PAGEIOLATCH_EX" waits="84418" averageWaitTime="38" maxWaitTime="3879" />
<wait waitType="CXCONSUMER" waits="66918" averageWaitTime="4" maxWaitTime="37543" />
<wait waitType="CXPACKET" waits="44615" averageWaitTime="52" maxWaitTime="1424602" />
<wait waitType="PARALLEL_REDO_WORKER_WAIT_WORK" waits="8304" averageWaitTime="13" maxWaitTime="1053" />
<wait waitType="HADR_FILESTREAM_IOMGR_IOCOMPLETION" waits="7739" averageWaitTime="502" maxWaitTime="1699" />
<wait waitType="IO_COMPLETION" waits="1007" averageWaitTime="31" maxWaitTime="652" />
</byCount>
<byDuration>
<wait waitType="PAGEIOLATCH_SH" waits="165708" averageWaitTime="48" maxWaitTime="5140" />
<wait waitType="CLR_AUTO_EVENT" waits="334" averageWaitTime="23346" maxWaitTime="1181415" />
<wait waitType="HADR_FILESTREAM_IOMGR_IOCOMPLETION" waits="7739" averageWaitTime="502" maxWaitTime="1699" />
<wait waitType="PAGEIOLATCH_EX" waits="84418" averageWaitTime="38" maxWaitTime="3879" />
<wait waitType="CXPACKET" waits="44615" averageWaitTime="52" maxWaitTime="1424602" />
<wait waitType="RESOURCE_SEMAPHORE" waits="13" averageWaitTime="43272" maxWaitTime="322806" />
<wait waitType="CXCONSUMER" waits="66918" averageWaitTime="4" maxWaitTime="37543" />
<wait waitType="LCK_M_S" waits="19" averageWaitTime="6820" maxWaitTime="10930" />
<wait waitType="ASYNC_NETWORK_IO" waits="622911" averageWaitTime="0" maxWaitTime="969" />
<wait waitType="PARALLEL_REDO_WORKER_WAIT_WORK" waits="8304" averageWaitTime="13" maxWaitTime="1053" />
</byDuration>
</nonPreemptive>
<preemptive>
<byCount>
<wait waitType="PREEMPTIVE_XE_CALLBACKEXECUTE" waits="4921392" averageWaitTime="0" maxWaitTime="16" />
<wait waitType="PREEMPTIVE_OS_QUERYREGISTRY" waits="1249" averageWaitTime="0" maxWaitTime="12" />
<wait waitType="PREEMPTIVE_XE_SESSIONCOMMIT" waits="533" averageWaitTime="0" maxWaitTime="5" />
<wait waitType="PREEMPTIVE_OS_AUTHENTICATIONOPS" waits="478" averageWaitTime="0" maxWaitTime="44" />
<wait waitType="PREEMPTIVE_OS_FILEOPS" waits="354" averageWaitTime="117" maxWaitTime="2852" />
<wait waitType="PREEMPTIVE_OS_CRYPTOPS" waits="294" averageWaitTime="2" maxWaitTime="685" />
<wait waitType="PREEMPTIVE_OS_AUTHORIZATIONOPS" waits="186" averageWaitTime="0" maxWaitTime="115" />
<wait waitType="PREEMPTIVE_XE_TARGETINIT" waits="181" averageWaitTime="0" maxWaitTime="18" />
<wait waitType="PREEMPTIVE_XE_TARGETFINALIZE" waits="175" averageWaitTime="0" maxWaitTime="0" />
<wait waitType="PREEMPTIVE_XE_GETTARGETSTATE" waits="164" averageWaitTime="11" maxWaitTime="754" />
</byCount>
<byDuration>
<wait waitType="PREEMPTIVE_OS_FILEOPS" waits="354" averageWaitTime="117" maxWaitTime="2852" />
<wait waitType="PREEMPTIVE_OS_WRITEFILEGATHER" waits="32" averageWaitTime="998" maxWaitTime="5410" />
<wait waitType="PREEMPTIVE_OS_CREATEFILE" waits="149" averageWaitTime="35" maxWaitTime="777" />
<wait waitType="PREEMPTIVE_XE_CALLBACKEXECUTE" waits="4921392" averageWaitTime="0" maxWaitTime="16" />
<wait waitType="PREEMPTIVE_OS_GETDISKFREESPACE" waits="16" averageWaitTime="266" maxWaitTime="678" />
<wait waitType="PREEMPTIVE_OS_WRITEFILE" waits="47" averageWaitTime="49" maxWaitTime="885" />
<wait waitType="PREEMPTIVE_XE_GETTARGETSTATE" waits="164" averageWaitTime="11" maxWaitTime="754" />
<wait waitType="PREEMPTIVE_OS_SETFILEVALIDDATA" waits="16" averageWaitTime="56" maxWaitTime="158" />
<wait waitType="PREEMPTIVE_OS_CRYPTOPS" waits="294" averageWaitTime="2" maxWaitTime="685" />
<wait waitType="PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY" waits="97" averageWaitTime="3" maxWaitTime="217" />
</byDuration>
</preemptive>
</topWaits>
<cpuIntensiveRequests />
<pendingTasks />
<blockingTasks>
<blocked-process-report monitorLoop="0">
<blocked-process>
<process id="process25ac17a9088" waitresource="11:4:8267184" waittime="40" schedulerid="1" kpid="3756" status="suspended" spid="59" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2020-11-03T11:09:51.323" lastbatchcompleted="2020-11-03T11:09:51.327" lastattention="1900-01-01T00:00:00.327" clientapp="SSIS-Package-{E9C2149B-86E6-4AED-BCE2-4B4D07C750EB}localhost.StackOverflow.sa" hostname="DESKTOP-H6S8IJF" hostpid="11504" isolationlevel="read committed (2)" xactid="75727" currentdb="11" currentdbname="StackOverflow" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame line="3" stmtstart="60" stmtend="1236" sqlhandle="0x02000000d4e2a814199ee296c804b4a13216e6eca6babce60000000000000000000000000000000000000000" />
</executionStack>
<inputbuf>
set identity_insert Posts on
insert into Posts(id,AcceptedAnswerId,AnswerCount,body,ClosedDate,CommentCount,CommunityOwnedDate,CreationDate,FavoriteCount,LastActivityDate,LastEditDate,LastEditorDisplayName,LastEditorUserId,OwnerUserId,ParentId,PostTypeId,score,tags,title,ViewCount)
select id,AcceptedAnswerId,AnswerCount,body,ClosedDate,CommentCount,CommunityOwnedDate,CreationDate,FavoriteCount,LastActivityDate,LastEditDate,LastEditorDisplayName,LastEditorUserId,OwnerUserId,ParentId,PostTypeId,score,tags,title,ViewCount from
StackOverflow_new.dbo.posts
where id not in (select id from StackOverflow.dbo.posts)
set identity_insert Posts off </inputbuf>
</process>
</blocked-process>
<blocking-process>
<process />
</blocking-process>
</blocked-process-report>
</blockingTasks>
</queryProcessing>
</value>
</data>
</event>
I then ran used DBCC page and object name as follows:
DBCC PAGE (11, 4, 8267184) WITH TABLERESULTS
select object_name(2082106458)
This gave the result "Posts".
Does the fact that the object_name query returned a table name show that it was trying to obtain a table lock?
Am I taking the correct approach to this?
I wondered if I need to change the extended event configuration but I couldn't see anything relevant to add? The current configuration is:
CREATE EVENT SESSION [StackOverflow] ON SERVER
ADD EVENT sqlserver.lock_timeout(
ACTION(sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
ACTION(sqlserver.sql_text)
WHERE ([package0].[greater_than_uint64]([duration],(50000)))),
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
ACTION(sqlserver.sql_text)
WHERE ([package0].[greater_than_uint64]([duration],(50000))))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO
November 4, 2020 at 1:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply