Report on SQL/SSRS 2k5 takes > 10 minutes, query < 3 mins

We have SQL and SSRS 2k5 on a Win 2k3 virtual server with 4Gb on the virt server. (The server running the virt server has > 32Gb)

When we run our comparison report, it calls a stored proc on database A. The proc pulls data from several tables, and from a view on database B.

If I run Profiler and monitor the calls, I see activity

SQL:BatchStarting SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation'), COLLATIONPROPERTY(CONVERT(char开发者_Python百科, DATABASEPROPERTYEX(DB_NAME(), 'collation')), 'LCID')

then wait several minutes till the actual call of the proc shows up.

RPC:Completed exec sp_executesql N'exec [procGetLicenseSales_ALS_Voucher] @CurrentLicenseYear, @CurrentStartDate, @CurrentEndDate, ''Fishing License'', @PreviousLicenseYear, @OpenLicenseAccounts',N'@CurrentStartDate datetime,@CurrentEndDate datetime,@CurrentLicenseYear int,@PreviousLicenseYear int,@OpenLicenseAccounts nvarchar(4000)',@CurrentStartDate='2010-11-01 00:00:00:000',@CurrentEndDate='2010-11-30 00:00:00:000',@CurrentLicenseYear=2010,@PreviousLicenseYear=2009,@OpenLicenseAccounts=NULL

then more time, and usually the report times out. It takes about 20 minutes if I let it run in Designer

This Report was working, albeit slowly but still less than 10 minutes, for months.

If I drop the query (captured from profiler) into SQL Server Management Studio, it takes 2 minutes, 8 seconds to run.

Database B just had some changes and data replicated to it (we only read from the data, all new data comes from nightly replication).

Something has obviously changed, but what change broke the report? How can I test to find out why the SSRS part is taking forever and timing out, but the query runs in about 2 minutes?

Added: Please note, the stored proc returns 18 rows... any time. (We only have 18 products to track.) The report takes those 18 rows, and groups them and does some sums. No matrix, only one page, very simple.

M Kenyon II

Database B just had some changes and data replicated to it (we only read from the data, all new data comes from nightly replication).

Ensure that all indexes survived the changes to Database B. If they still exist, check how fragmented they are and reorganize or rebuild as necessary.

Indexes can have a huge impact on performance.

As far as the report taking far longer to run than your query, there can be many reasons for this. Some tricks for getting SSRS to run faster can be found here:

http://www.sqlservercentral.com/Forums/Topic859015-150-1.aspx

Edit: Here's the relevant information from the link above.

AshMc

I recall some time ago we had the same issue where we were passing in the parameters within SSRS to a SQL Dataset and it would slow it all down compared to doing it in SSMS (minutes compared to seconds like your issue). It appeared that when SSRS was passing in the parameter it was possibly recalculating the value and not storing it once and that was it.

What I did was declare a new TSQL parameter first within the dataset and set it to equal the SSRS parameter and then use the new parameter like I would in SSMS.

eg:

DECLARE @X as int
SET @X = @SSRSParameter

janavarr

Thanks AshMc, this one worked for me. However my issue now is that it will only work with a single parameter and the query won’t run if I want to pass multiple parameter values.

...

AshMc

I was able to find how I did this previously. I created a Temp table placed the values that we wanted to filter on in it then did an inner join on the main query to it. We only use the SSRS Parameters as a filter on what to put in the temp table.

This saved a lot of report run time doing it this way

DECLARE @ParameterList TABLE (ValueA Varchar(20)) 

INSERT INTO @ParameterList
select ValueA
from TableA
where ValueA = @ValueB


INNER JOIN @ParameterList
ON ValueC = ValueA  

Hope this helps,

--Dubs

Could be parameter sniffing. If you've changed some data or some of the tables then the cached plan that will have satisfied the sp for the old data model may not be valid any more.

Answered a very similar thing here: stored procedure performance issue

Quote:

f you are sure that the sql is exactly the same and that the params are the same then you could be experiencing a parameter sniffing problem .

It's a pretty uncommon problem. I've only had it happen to me once and since then I've always coded away the problem.

Start here for a quick overview of the problem:

http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx

http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/

try declaring some local variables inside the sp and allocate the vales of the parameters to them. The use the local variables in place of the params.

It's a feature not a bug but it makes you go @"$@