September 30, 2009 at 4:49 am
Hi all,
I am troubleshooting an issue I haven't seen before.
We have a large report which is on a Sharepoint integrated Reporting Services 2008 server. The report is accessing and returning quite a lot of XML data and takes about 50 secs to complete.
The problem we are seeing is that there is a block involving the following stored procs: ReportServer.dbo.GetSessionData and ReportServer.dbo.WriteLockSession. When multiple users try running the report they all start blocking and everything backs up.
If anyone has seen this before or has any suggestions I would appreciate it?
I don't know yet whether this is normal Reporting Services behaviour or something to do with SharePoint?
Thanks,
PG
October 2, 2009 at 3:37 am
I have now resolved this issue.
From everything I have found and read, the blocking behaviour by Reporting Services appeared to be normal for large reports.
This wasn't acceptable for us so I enabled the read committed snapshot isolation level for the ReportServerTempDB database. Since then we have not seen any blocks, even when concurrently running multiple large reports.
We need to do more testing, but unless an issue comes up I'm happy with this solution.
PG
February 16, 2010 at 7:14 am
"read committed snapshot"
You can only set one isolation level, that statement is incorrect, where do you do that?
Jason
http://dbace.us
😛
February 16, 2010 at 7:24 am
jswong05, that is only one isolation level.
Search the web or Books Online for READ_COMMITTED_SNAPSHOT.
PG
February 16, 2010 at 1:52 pm
PG, Thank you for your clarification of your own statement. >" I enabled the read committed snapshot isolation level for ".
READ UNCOMMITTED
Specifies that statements can read rows that have been modified by other transactions but not yet committed.
Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.
In SQL Server 2005, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:
The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.
The SNAPSHOT isolation level.
READ COMMITTED
Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:
If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared locks are released when the statement completes.
If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.
When the READ_COMMITTED_SNAPSHOT database option is ON, you can use the READCOMMITTEDLOCK table hint to request shared locking instead of row versioning for individual statements in transactions running at the READ_COMMITTED isolation level.
REPEATABLE READ
Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction. Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. Use this option only when necessary.
SNAPSHOT
Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.
Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data.
During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. The SNAPSHOT transaction is blocked until that transaction has been rolled back. The lock is released immediately after it has been granted.
The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.
A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. A transaction starts at the BEGIN TRANSACTION statement.
A transaction running under SNAPSHOT isolation level can view changes made by that transaction. For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.
SERIALIZABLE
Specifies that:
Statements cannot read data that has been modified but not yet committed by other transactions.
No other transactions can modify data that has been read by the current transaction until the current transaction completes.
Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
Remarks
Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed. All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.
The transaction isolation levels define the type of locks acquired on read operations. Shared locks acquired for READ COMMITTED or REPEATABLE READ are generally row locks, although the row locks can be escalated to page or table locks if a significant number of the rows in a page or table are referenced by the read. If a row is modified by the transaction after it has been read, the transaction acquires an exclusive lock to protect that row, and the exclusive lock is retained until the transaction completes. For example, if a REPEATABLE READ transaction has a shared lock on a row, and the transaction then modifies the row, the shared row lock is converted to an exclusive row lock.
You can change from one isolation level to another at any time during a transaction. When you change a transaction from one isolation level to another, resources read after the change are protected according to the rules of the new level. Resources read before the change continue to be protected according to the rules of the previous level, for example, if a transaction changed from REPEATABLE READ to SERIALIZABLE. Rows read by SELECT statements issued before the change continue to be protected by shared locks at the row, page, or table level. These locks continue to be held until the end of the transaction. Rows read by SELECT statements after the change are protected by range locks.
This table shows the locking behaviors of changing from one isolation level to another in a transaction.
Jason
http://dbace.us
😛
February 16, 2010 at 1:55 pm
by the way, for those who really want to understand how "transaction processing" and "isolation level" works, one should read Thomas Kyte's book - only the chapter about transaction processing.
Jason
http://dbace.us
😛
February 16, 2010 at 2:50 pm
PG,
If you are saying you solve the problem by altering this system SP using SET ISOLATION LEVEL SNAPSHOT
=====================================================================
USE [ReportServer]
GO
/****** Object: StoredProcedure [dbo].[WriteLockSession] Script Date: 02/16/2010 15:45:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[WriteLockSession]
@SessionID as varchar(32)
AS
INSERT INTO [ReportServerTempDB].dbo.SessionLock WITH (ROWLOCK) (SessionID) VALUES (@SessionID)
====================================================================
It is good for you. I would not declare it is the solution too early and I would not recommend it to others.
I have the same problem. MSDN says the locking/blocking is to ensure consistency, and is normal behavior. The only thing you can do is to reduce report data or run it at quiet time. I am sure MSDN understands why it was set this way originally. You are editing a blackbox at your own risk.
Jason
http://dbace.us
😛
February 17, 2010 at 4:21 am
jswong05, I never recommended this solution to anyone and any SQL Server DBA will know the potential issues with making configuration changes to system databases/objects.
No-one could help me with my problem and so I came up with a solution which was fully tested and signed-off. All forum solutions, hotfixes, service packs etc are done at own risk and this one is certainly no different.
It's not acceptable to me or my company that live read-only reports are timing out due to blocking. If you're happy with that then you don't face the same issues that I do.
This is my last post on this topic.
I hope you find an acceptable solution to your problem.
PG
February 26, 2010 at 7:57 am
Thank you for sharing the experiences.
Create snapshots, let reports run against snapshots off-hours would be a better solution in SQL Server environment for reports in general. I encountered the same problem.
Jason
http://dbace.us
😛
April 19, 2010 at 1:10 pm
Paul,
Is Microsoft mentioned about this blocking when large reports runs?
For us blocking occurring even when one user is running a larger report & the report is timing out from application! When we ran the same report, its giving the results in 15 mins
Please advice
April 20, 2010 at 3:39 am
rambilla4, it was a while ago now, but if I remember correctly, we had timeouts from a single user running the report.
The previous post to use Snapshots could be a good idea but as I said in my posts, our reports access live data. Even data an hour old is too old for our clients. Also, one of our report servers is running on SQL Standard so we can't use this functionality on there.
I posted my solution which was the only thing that worked for me i.e. setting the isolation level of the database to Read Committed with Snapshot. its not a perfect solution but since then, I haven't had a single locking or timeout issue with the reports on this server.
It's a big change for a production server so I would test it out on a dev/test server and see if it works for you. I hope you have some luck.
September 13, 2011 at 3:35 pm
We are seeing this issue even though the blocking spid (ReportServer.dbo.WriteLockSession;1) has 0 CPUTIme and 0 IO. Any ideas here?
Thanks!
November 29, 2013 at 6:07 am
im getting some ReportServer.dbo.WriteLockSession;1 while fetching reports from SSRS.
Can any one help me on this.
December 12, 2019 at 5:00 am
Hi Paul,
Did you change the isolation level for reportserver db or reportServerTemp db?
We also have a lot of blocks and locks in the database.
December 16, 2019 at 5:55 pm
Hi Paul,
Did set the isolation level is only available in Enterprise edition? I cannot find the information online.
Did that also require a reboot? I changed for my standard edition without reboot, and it does not help.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply