1

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.

  1. Create a #temp table
  2. Insert the exisiting stored procedure into the

    temp table

  3. Select from #temp
  4. 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?

Edit:

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?

  • Did you try using the temp table in the stored procedure? – Shawn Melton Apr 17 '14 at 13:28
  • I'd like to get some more information on this blocking issue since that is the root issue. – Zane Apr 17 '14 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.