Performance Issue

Question

SSRS 2008 R2 BIDS

I have a query that requires a date range.

When ran inside of SSMS it takes about 1 second and produces a result of one row with 4 records (all numbers).

When I add it as a dataset in my report and run it for the same date range the report eventually times out. The report is not using or displaying info from the dataset. If I change the date range to a single day the report runs correctly but takes several seconds. None of my other reports seem to have this issue. The time out happens if I am running it through IE or directly in BIDS.

What could be the problem?

Thank you.

Answer 1

You can use the Reporting Services execution log to check where the time is spent in the server http://technet.microsoft.com/en-us/library/ms159110(v=sql.105).aspx

Every SQL Client could have a different set of options (Set options http://msdn.microsoft.com/en-us/library/ms175088(v=sql.105).aspx) configured which will affect the SQL Server execution plan which I suspect is your issue as is very likely SSMS has different options than the default .NET provider which Reporting uses.

I would recommend some things to try

1. Encapsulate your query in a store procedure so you can ensure are running the same from SSMS and from RS

2. Take a look to the parameter sniffing blog post from Connor Cunningham http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/08/11/conor-vs-misbehaving-parameterized-queries-optimize-for-hints.aspx?Redirected=true

3. You could use the SQL DMV sys.dm_exec_sessions to check the user settings and compare between SSMS and Reporting (just for the sake of validate the differences)

I hope this helps

Regards

Jaime -  http://blogs.msdn.com/b/jtarquino/

This posting is provided "AS IS" with no warranties, and confers no rights

Answer 2

Do you specify a date range as parameters or hard coded values? Looks like parameter sniffing... Try the below

CREATE PROCEDURE sp1

@dt_from DATETIME,

@dt_to DATETIME

AS

SELECT col1,col2 FROM tbl WHERE dt>=@dt_from AND dt<=@dt_to OPTION(RECOMPILE)

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance