Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

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?

share|improve this question
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. – Thomas Stringer Sep 12 '12 at 15:26
3  
See @gbn's answer to a similar question: dba.stackexchange.com/questions/24091/… – Thomas Stringer Sep 12 '12 at 15:28
Thanks @Shark, I think the other answer solves my question then. I've flagged the question as duplicate then. – Diego Jancic Sep 12 '12 at 15:55

marked as duplicate by Jon Seigel, Jack Douglas♦ Oct 7 '12 at 20:22

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer

up vote -2 down vote

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).
share|improve this answer
Not sure if I understand. "All TSQL statements are presumed snapshots"... Do you mean all statements in RS or everywhere? – Diego Jancic Sep 12 '12 at 15:12
2  
"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. – Thomas Stringer Sep 12 '12 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 '12 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 '12 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 '12 at 16:17
show 5 more comments

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