April 9, 2014 at 1:38 am
Hi all,
When I run a stored procedure as a direct query in Management studio it's taking ~18 seconds to complete, however when I run the exact same stored procedure in SSRS it's taking about 3 seconds for the query to be called and the report to fully render. I could understand if it was the other way around, but I'm a bit baffled by this. Any ideas?
Thanks.
April 9, 2014 at 2:03 am
In that past I see this for 2 reasons
1 - "SET" option differ in SSMS than the default connection to the server i.e. ARITHMETIC ABORT (check server setting and your query options within SSMS)
2 - If you are debugging the SP and provide local variables then I have also seen a different execution plan selected.
April 9, 2014 at 3:40 am
Another side effect from SSMS is that it has to display all the rows in the result set. It might do it not so efficiently as SSRS.
(just a wild guess here)
Anyway, to really compare you would have to take a look at the execution plan and rule out caching as well.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 9, 2014 at 5:54 am
Koen Verbeeck (4/9/2014)
Another side effect from SSMS is that it has to display all the rows in the result set. It might do it not so efficiently as SSRS.(just a wild guess here)
Anyway, to really compare you would have to take a look at the execution plan and rule out caching as well.
I'm not sure how to check the execution plan for the procedure when it's called from Reporting Services as I've not looked at that before. Is there an easy way?
Caching is disabled on the reporting, and if I change aspects of the stored procedure the changes are reflected live in the report on a normal report refresh, so I'm confident there's no caching going on.
April 9, 2014 at 6:14 am
Oblivion (4/9/2014)
Koen Verbeeck (4/9/2014)
Another side effect from SSMS is that it has to display all the rows in the result set. It might do it not so efficiently as SSRS.(just a wild guess here)
Anyway, to really compare you would have to take a look at the execution plan and rule out caching as well.
I'm not sure how to check the execution plan for the procedure when it's called from Reporting Services as I've not looked at that before. Is there an easy way?
I think you can capture it using Profiler. (at least the query)
The plan might be retrieved from a DMV as well, but that's a bit out of my comfort zone.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 9, 2014 at 6:32 am
If it is currently in the cache then use the query below (replacing the check with your SP name).
SELECT UseCounts ,
Objtype ,
[text] ,
query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE [text] LIKE '%check%'
Note: It can take a while to run, so please plan accordingly.
April 9, 2014 at 7:10 am
SSRS designer caches query results by default. So, if it has cached results, it won't rerun the query at all
Gerald Britton, Pluralsight courses
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply