Possible Duplicate:
Does SSRS lock the table when querying?

I'm using SQL Server 2008 R2 and Reporting Services. Do the reports block other queries or are they executed in highly isolated transactions, such as a snapshot?

Next obvious question: Can reporting services produce deadlocks or timeouts in other queries as a result of locking tables for a long time?

  • 3
    For pessimistic isolation levels, unless you are using the Read Uncommitted transaction isolation level, then SSRS queries (to be treated as any other query) can and will issue locks on data. As for what resource is locked (RID/key, page, table, etc.) all depends on specifics. Sep 12, 2012 at 15:26
  • 3
    See @gbn's answer to a similar question: dba.stackexchange.com/questions/24091/… Sep 12, 2012 at 15:28
  • Thanks @Shark, I think the other answer solves my question then. I've flagged the question as duplicate then. Sep 12, 2012 at 15:55

1 Answer 1


FYI, unless specifically directed, all TSQL statements are presumed Snap-Shots.

1st Question:

  • So Think of it as your SSRS is looking at your data as if it were a View/Select statement.

2nd Question:

  • It could, pending on the volume of calculation (SSRS) and volume of users. Think you would have to have well over 10% CPU/RAM utilization from SSRS and over 50% CPU/RAM utilization from general queries in order for you to notice an effect, albeit hardly noticeable (like 1-10 nSecs).
  • Not sure if I understand. "All TSQL statements are presumed snapshots"... Do you mean all statements in RS or everywhere? Sep 12, 2012 at 15:12
  • 3
    "unless specifically directed, all TSQL statements are presumed Snap-Shots." Are you insinuating that the default transaction isolation level in SQL Server is Snapshot Isolation? In which case, that is not true. It is Read Committed. Sep 12, 2012 at 15:23
  • @DiegoJancic RS, if you mean RecordSets, are not TSQL. Even though the Query results in a TSQL, they are two different view points pending on which end of the transaction your talking about.
    – GoldBishop
    Sep 12, 2012 at 16:14
  • @Shark Really then why are Batches only committed at the end, and not while it is happening? If everything wasnt a SnapShot Isolation then you wouldnt need to Commit them at all. Read Committed is a subset of SnapShot Isolation. By definition a Snapshot is a view of data at the time of the query.
    – GoldBishop
    Sep 12, 2012 at 16:16
  • @CleanFill experience in supporting those environments. You notice a bigger hit when your Service CPU Utilization hits around 75-80% utilization.
    – GoldBishop
    Sep 12, 2012 at 16:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.