StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POReport on SQL/SSRS 2k5 takes > 10 minutes, query < 3 mins
text
Body
copied!<p>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)</p> <p>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.</p> <p>If I run Profiler and monitor the calls, I see activity </p> <blockquote> <p>SQL:BatchStarting SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation'), COLLATIONPROPERTY(CONVERT(char, DATABASEPROPERTYEX(DB_NAME(), 'collation')), 'LCID')</p> </blockquote> <p>then wait several minutes till the actual call of the proc shows up.</p> <blockquote> <p>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</p> </blockquote> <p>then more time, and usually the report times out. It takes about 20 minutes if I let it run in Designer</p> <p>This Report was working, albeit slowly but still less than 10 minutes, for months.</p> <p>If I drop the query (captured from profiler) into SQL Server Management Studio, it takes 2 minutes, 8 seconds to run.</p> <p>Database B just had some changes and data replicated to it (we only read from the data, all new data comes from nightly replication).</p> <p>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?</p> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload