Having utterly failed to resolve why a perfectly acceptable but admitidly large stored procedure was suffering the blocking process
lck_m_s when called via a report in SQL Reporting Services I have been forced to implement the following as a work around.
- Create a #temp table
- Insert the exisiting stored procedure into the
- Select from #temp
- Delete #temp
This process results in a report generation time of 10 seconds vs the 2 minutes I was suffering when calling the origional report stored procedure via reporting servces.
This to me feels like a dirty hack but it works and works well from the perspecitive of the users.
Is there a better method I could have used?
I'd like to get some more information on this blocking issue since that is the root issue. – Zane
More info here Reporting Services TempDB lock blocking report generation However, the issue mysteriously reappeared and then as I was finishing the hack implementation it disappeared, again!
Did you try using snapshot isolation? – Jon Seigel
Yes, but it had no affect, still blocking seen similar to this issue Shared locks taken under Read Committed Snapshot Isolation (RCSI)
Did you try using the temp table in the stored procedure? – Shawn Melton
No, I don't understand how to implement it that way round, why would it be of benefit?