Tuesday, February 05, 2013 10:48 PM
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?
Wednesday, February 06, 2013 12:11 AM
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
This posting is provided "AS IS" with no warranties, and confers no rights
- Marked As Answer by Scott_Hanebutt Wednesday, February 06, 2013 9:26 PM
Wednesday, February 06, 2013 5:59 AM
Do you specify a date range as parameters or hard coded values? Looks like parameter sniffing... Try the below
CREATE PROCEDURE sp1
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
- Marked As Answer by Scott_Hanebutt Wednesday, February 06, 2013 9:27 PM