Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SSRS query performance (dynamic SQL vs. stored proc) Expand / Collapse
Author
Message
Posted Friday, August 16, 2013 6:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:06 AM
Points: 1,187, Visits: 1,225
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
Post #1485149
Posted Friday, August 16, 2013 7:58 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:55 PM
Points: 1,980, Visits: 3,347
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
Post #1485218
Posted Monday, August 19, 2013 2:30 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:06 AM
Points: 1,187, Visits: 1,225
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, ... 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
Post #1486005
Posted Tuesday, August 20, 2013 6:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:51 AM
Points: 316, Visits: 1,488
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



Post #1486212
Posted Tuesday, August 20, 2013 1:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 13,017, Visits: 10,801
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1486406
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse