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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy