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 serveral 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, 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.
This Report was working, albeit slowly, for months.
If I drop the query (captured from profiler) into SQL Server Management Studio, it takes 2:08 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.
