Stored procedure never finishes running from SSRS report manager

• kiril.lazarov.77

  Hall of Fame

  Points: 3742

  More actions

  July 9, 2015 at 11:27 am

  #299886

  Hi all,

  My SSRS report never finishes running for some reason.

  If I run the same SP with the same parameters from SSMS it runs in 0.5 sec (returns 8-10 rows).

  When running from report manager, I see extremely high CPU Time and Disk IO values, no blocking, no waiting at all.

  Any ideas? Thanks.

• Luis Cazares

  SSC Guru

  Points: 183700

  More actions

  July 9, 2015 at 11:30 am

  #1811401

  Have you checked that your connection settings are the same in both SSMS and SSRS?

  Have you used something to check where's the problem such as profiler or extended events?

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• kiril.lazarov.77

  Hall of Fame

  Points: 3742

  More actions

  July 9, 2015 at 11:39 am

  #1811403

  Cheers Luis,

  I can see the "RPC Starting" entry in profiler but not the "RPC completed", looking at the results from sp_who2 during report execution, CPU time and Disk IO values are high and they keep growing (IO at 50million+ after a minute) and as it won't finish I always have to kill the process.

  When I run the same from SSMS, IO is around 4,000, CPU is 400, runs in 0.5 sec

  Yes, I use the same connection string.

  Thank you

  Edit: I also tried to run this from SSDT and BIDS, same thing happened.

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  July 9, 2015 at 1:12 pm

  #1811437

  Could be parameter sniffing, so please read this: Catch-all queries[/url]

  Could be different settings on the connection (not the conn.string), try capturing the execution plan for the SSRS run and compare it to the plan from SSMS.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]