Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS query performance (dynamic SQL vs. stored proc)


SSRS query performance (dynamic SQL vs. stored proc)

Author
Message
robert.gerald.taylor
robert.gerald.taylor
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1330 Visits: 1399
Gazareth (8/14/2013)
Could be a difference in SET options between SSMS & SSRS? (I'm thinking ARITHABORT)

In my SSMS, I have the SET ARITHABORT checked to on in Tools,Options. I'm not sure where I check that setting in SSRS.

You can query the TimeDataRetrieval, TimeProcessing, and TimeRendering fields in ReportServer.dbo.ExecutionLog view to find what's taking all the time.

We have SSRS integrated with SharePoint, so when I query the dbo.Execution log from the SP_ReportServerIntegrated db. Interesting that the only report that shows a Status <> rsSuccess is this particular one with the query issues. I'm going to do more research into these execution times.

Thanks,
Rob
Gazareth
Gazareth
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2872 Visits: 5351
Thanks Rob, SSMS does have arithabort on by default, I think SSRS has it off.

Any easy way to check if it could be responsible is to run this in SSMS:

SET ARITHABORT OFF
GO
EXEC procname

and see if the performance reflects what you see in SSRS.

You can also check the SET options of cached plans via this query:

SELECT s.name, pa.value
FROM sys.procedures s
INNER JOIN sys.dm_exec_procedure_stats d ON s.object_id = d.object_id
OUTER APPLY sys.dm_exec_plan_attributes(d.plan_handle) pa
WHERE pa.attribute = 'set_options'

pa.value will give you the set options used in the proc plan as seen here: http://technet.microsoft.com/en-us/library/ms189472(v=sql.105).aspx

I haven't used SSRS with sharepoint so not sure on grabbing execution data from there.

Cheers
robert.gerald.taylor
robert.gerald.taylor
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1330 Visits: 1399
Thanks to Koen, Pete and Gazareth -- it turns out that it was parameter sniffing causing the problem.

As Paul Harvey used to say, here's the rest of the story:

SSRS report has run fine for past couple of months (5-10 seconds to come up); after a rollout of an update to our LOB app over the weekend, it's now taking "forever" (2-3 hours) for this same report to run. Tempdb is filling up, users are complaining, dogs and cats living together, ... w00t So we yanked the report down. The other 40+ reports are running fine -- just this one seems to have lost it's mind.

Running the query from SSMS pointing to any environment ran fine. Running the report from Test SharePoint pointing to any environment (including production) ran find (5-10 seconds). Only the combination of running report on production SharePoint pointing to prod database caused horrible delays. Querying the SP_ReportServerIntegrated database was showing a 1000X increase in the report's data retrieval from the previous week.

I had switched the report from T-SQL text in the report to a stored procedure that specifically mapped the parameters to internally declared variables to get around the parameter sniffing issue. Of course this worked fine and dandy in all of the environments I tested in. So I didn't think initially it was a param sniffing problem. Eventually in a maintenance window, I was able to run the report using the new stored procedure on prod SharePoint pointing to prod db and performance was back to usual speeds. So it was a problem with parameter sniffing.

The good news is that everything is working fine now.

Thanks,
Rob
peterzeke
peterzeke
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 1766
Well done, Rob! (..and nice reference to Paul Harvey)

I can't imagine a report running from seconds to hours, and of course, only in production (arrg!) Performance problems due to parameter sniffing can suddenly appear in SSRS so keep an eye out for this issue in the future for other reports, especially if the underlying query is altered.

--Pete



Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16471 Visits: 13207
Great! Glad that you got it solved and thanks for posting back!



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search