September 6, 2011 at 10:37 am
I'm seeing extensive blocking involving the ReportServer and ReportServerTempdb databases.
This appears to be causing the report server (hosted in a separate server, scale-out deployment) to max out on CPU and memory.
The query blocking is from stored procedure ReportServer.dbo.ReadChunkSegment:
SELECT substring(seg.Content, @DataIndex + 1, @Length) as [Content]
FROM [ReportServerTempDB].dbo.Segment seg
JOIN [ReportServerTempDB].dbo.ChunkSegmentMapping csm
ON (csm.SegmentId = seg.SegmentId)
WHERE csm.ChunkId = @ChunkId
AND csm.SegmentId = @SegmentId
That is blocking the following statement from stored procedure ReportServer.dbo.DeleteSnapshotAndChunks:
DELETE [ReportServerTempDB].dbo.SegmentedChunk
WHERE SnapshotDataId = @SnapshotID
What could be the reason for this blocking and how can it be corrected?
The above blocked statement has been completely blocked from running by the blocking statement for the last several hours!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 6, 2011 at 1:28 pm
Have you run a trace to see what the commands are?
Have you thought about setting the default isolation level to READ_COMMITTED_SNAPSHOT? (Data here: http://msdn.microsoft.com/en-us/library/ms173763.aspx)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 6, 2011 at 1:30 pm
GSquared (9/6/2011)
Have you run a trace to see what the commands are?Have you thought about setting the default isolation level to READ_COMMITTED_SNAPSHOT? (Data here: http://msdn.microsoft.com/en-us/library/ms173763.aspx)
READ_COMMITTED_SNAPSHOT sounds like a good idea for this db. Thanks for the suggestion.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 6, 2011 at 2:29 pm
GSquared (9/6/2011)
Have you run a trace to see what the commands are?Have you thought about setting the default isolation level to READ_COMMITTED_SNAPSHOT? (Data here: http://msdn.microsoft.com/en-us/library/ms173763.aspx)
Those commands are run from the SSRS engine. Can't tell you anymore than that about them.
When I was trialing R-G SQL Monitor I was getting blocking warnings almost hourly and certainly daily.
I have not tried READ_COMMITTED_SNAPSHOT the db to see if that solved it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply