January 7, 2011 at 2:38 pm
This is with 2008 R2.
I've deployed one series of reports, in all 60 reports exactly. I have two that are especially slow due to the amount of data they aggregate. I created a shared schedule and a data drive subscription that runs on that shared schedule. I selected the null delivery provider because I want to preload the cache daily in order to improve performance of these two slow reports.
I created a stored procedure that pulls in the values that map to the parameters of these two reports. I configured the subscription and tied each parameter to a column returned by that stored procedure.
I allowed for the subscription to run and the result is that it processed. The status after it ran is:
Done: 2178 processed of 2178 total; 11 errors.
How do I check the errors? Also, any combination of parameters I select result in the same old slow poke report rendering time I had before so I'm convinced this isn't working correctly...
January 10, 2011 at 7:55 am
you mention there are 2 reports that are slow due to the amount of data they aggregate...
Are you pulling all the data to SSRS then doing the aggregation there? IF so, have you experimented with pre-aggregating your data in your SQL query (or view/stored procedure) and then just formatting it with SSRS instead of having SSRS do all of the heavy lifting?
This approach may allow you to continue to render your report real time as it will only be pulling minimal data across the wire, instead of the entire recordset.
-Luke.
January 10, 2011 at 8:07 am
Yes, in fact both reports do exactly. I'm aware of that trade-off though I don't care for it as it makes for much longer stored procedures and messy reports to maintain.
Regardless of our approach, we'd like to start making use of pre-loading the cache for improved performance but can't seem to get it to work. We've got the right scenario for caching: the reporting database is updated once each day so that reports are static through the day.
Do I understand the purpose of the null delivery provider correctly? My understanding is that I can create a subscription that pulls in a query that maps parameter values to parameters and the subscription will pre-load the SSRS TempDB cache with all of the combinations of parameters in the query I provided. It seemed to work except for the fact that my performance didn't improve. Is there some global setting I have to tick to get it to check the cache first?
January 10, 2011 at 9:13 am
I understand you want to use caching, but I'd probably look toward getting the data preaggregated first, as it will help over all system performance, so that you won't need to cache each and every report, particularly as your data loads grow.
Are you actually getting reports from the cache or from the live system? If you have date/time info on your report you'll see it as the time from your NULL subscription, otherwise you'll see the date you ran it.
Alternatively, you can check the execution log and check the Source Column 1=Live, 2=cache.
While I don't believe this is a comprehensive list, BOL mentions the following as a list of reason why a report many not be cached... "Not all reports can be cached. If a report includes user-dependent data, prompts users for credentials, or uses Windows Authentication, it cannot be cached."
also, check to make certain your subscription is scheduled such that the reports are expired prior to them being rerun. while, unlikely, the report may be expiring after the subscription runs thereby forcing a new report execution as a live request...
-Luke.
January 10, 2011 at 10:50 am
Yes, currently the reports do aggregate in T-SQL in the stored procedure. I agree and accept that T-SQL can aggregate faster than SSRS at render time. Despite using this performance improving pattern, the report is still quite slow (2+ minutes) for some combinations of parameters, in particular combinations that involve our two largest companies. I want to preload the cache with a null provider subscription for all combinations of parameters involving those two companies.
Can the cache expiration and the subscription use the same shared schedule? Is there any way to expire the cache the moment that the subscription is finished running so that there's no gap, you'd always hit a cached report pre-loaded by the null provider subscription?
January 10, 2011 at 10:58 am
Thanks for pointing me to the Execution Log, I think this is probably the direction I need to go for troubleshooting this further.
In BOL, it says, "To view report execution data, you can run a sample SQL Server Integration Services package that Reporting Services provides to extract the data from the execution log and put it into a table structure that you can query. For more information, Errors and Events Reference (Reporting Services)", and then if you go to that link there isn't any further reference the the sample SSIS package. Any advice on getting to view this data in a more usable format?
January 10, 2011 at 11:18 am
as for the execution log, I just query the table directly. It's in the reportserver database and is called ExecutionLog. Select * from ExecutionLog will get you your data I think by default it's the past 60 days (or was in 2005, not sure if they changed it). From there you may need to join to the catalog table to to find out what report the reportID column is referencing. You can see which parameters are used etc... and that may help in troubleshooting some of your issues here as well. Perhaps you're not specifying what you think you are in the subscriptions?
Also important are the start/end times as well as the timedataretrieval, timeprocessing, and timerendering columns. they will show you where your report server is doing the most work. Is it while acquiring data from the datasource, while processing that data(aggregations, custom formatting/logic etc), or rendering i.e. formatting the report for the specified output method.
I suppose the point I was trying to make about the pre-aggregation techniques would be that if the query returns the data quickly but your server spends the next 90-110 seconds processing that data, you may be able to process your data faster in TSQL.
-Luke.
February 9, 2011 at 1:19 pm
Here is the query I use to pull the execution data for reporting, we use this for report usage tracking:
SELECT
CL.Name AS ReportName, CL.Path AS ReportLocation, CL.Description AS ReportDescription,
REPLACE(U.UserNAme,'CROSSMARK\','') AS ReportCreatedBy, Cl.CreationDate AS ReportCreatedOn,
REPLACE(U1.UserName,'CROSSMARK\','') AS ReportLastModifiedBy, Cl.ModifiedDate AS ReportLastModifiedOn,
REPLACE(EL.UserName,'CROSSMARK\','') AS ReportExecutedBy, EL.Parameters AS ReportRunTimeParameters,
EL.TimeStart AS ReportStartTime, EL.TimeEnd AS ReportEndTime,
EL.TimeDataRetrieval, EL.TimeProcessing, EL.TimeRendering, SUBSTRING(EL.Status,3,100) AS ReportExecutionStatus
FROM dbo.ExecutionLogStorage EL (nolock)
INNER JOIN dbo.Catalog CL (NOLOCK)
ON CL.ItemID = EL.ReportID
AND CL.Path = @ReportName -- replace ID with Name since when a report is moved the itemid changes
INNER JOIN Users U (NOLOCK)
ON U.UserID = CL.CreatedByID
INNER JOIN Users U1 (NOLOCK)
ON U1.UserID = CL.ModifiedByID
ORDER BY EL.TimeStart DESC
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply