Blocking from Report server in production

Question

Blocking from Report server in production

gary1

SSC-Insane

Points: 21152
More actions
March 3, 2010 at 10:54 am

#137575

Hi,
We have SQL Server 2005 EE x64 with SP3 on Server A and Reporting Services on Server 'SQLRS' and the ReportServer & ReportServerTempDB databases are on Server A's sql instance.
We have an application, from where the report server URL (http://sqlrs/reportserver) will be called to the server SQLRS and from there it will connect Oracle database (Oracle datasource) to get the data and display in the application GUI.
We are frequently getting Blocking from this Reportserver (SQLRS) as below (we are using spot light for monitoring):
spidWait TimeTypeResourceCommandSQL UserProgramWin UserCPUI/OHost NameStatusSession SQL
740AWAITING COMMANDABC\SQLRS$Report ServerSQLRS$00SQLRSsleeping, blockingCREATE PROCEDURE [dbo].[WriteLockSession]
@SessionID as varchar(32)
AS
INSERT INTO [ReportServerTempDB].dbo.SessionLock WITH (ROWLOCK) (SessionID) VALUES (@SessionID)
105166,391LCK_M_SKEY: 10:72057594038386688 (51024aa10a72)SELECTABC\SQLRS$Report ServerSQLRS$00SQLRSsuspended, blockingCREATE PROCEDURE [dbo].[CheckSessionLock]
@SessionID as varchar(32)
AS
DECLARE @Selected nvarchar(32)
SELECT @Selected=SessionID FROM [ReportServerTempDB].dbo.SessionLock WITH (ROWLOCK) WHERE SessionID = @SessionID
Please advice why this blocking occurring & what steps I need to perform to avoid this blocking?
Thanks for your help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

gary1 SSC-Insane Points: 21152 More actions · Answer 1

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/5d33cdc5-998c-4b11-8b8a-372d6abca723?prof=required

I went through the above link which addresses this issue. Is having blocking with reportservertempdb normal?

please advice

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

gmamata7 (3/3/2010)
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/5d33cdc5-998c-4b11-8b8a-372d6abca723?prof=required
I went through the above link which addresses this issue. Is having blocking with reportservertempdb normal?
please advice

Blocking would not be normal. As described in that link you found, it appears to have been related to collation.

I would check your patch level and apply a patch if one is available.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

gary1 SSC-Insane Points: 21152 More actions · Answer 3

We have SQL Server 2005 Standard edition with SP3. And from the link http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/5d33cdc5-998c-4b11-8b8a-372d6abca723?prof=required

if we ran a report which will bring large data, then blocking from ReportserverTempdb is normal. Is that right?

please advice me

thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

If you run a report that retrieves massive amounts of data then you run the risk of contention and blocking. However, you should tune your queries and database such that the potential for blocking is minimized.

If you are pulling so much data, then it needs to be evaluated as to whether or not that amount of data is truly needed.

I would start with performance tuning your queries and database.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events