July 17, 2014 at 5:24 am
Hi
I have a parametised SSRS report that when is first run it returns incorrect results (i.e. number of rows and grand total of a value column aren't correct). When run for a second time, and every time after, the correct results are returned until a parameter is changed where a similar thing happens again (but not always). The report is running against static tables in a data warehouse. Both the warehouse and SSRS report are SQL 2005.
The report uses a stored proc. I have run the SQL from the stored proc using the same parameters that generate the problem in the SSRS report and get the same results - first run incorrect number of rows returned then correct on second, third etc execution. This seems to go against everything I know of databases in that the same result should be returned every time.
Does anyone have any ideas on what could be causing this behaviour as all my investigations and tests so far have drawn a blank.
Thank you.
July 17, 2014 at 8:27 am
My first thought was that the report was using a cached version of the report, but that wouldn't be the behavior you are describing and it wouldn't explain why a direct call to the SP does the same thing.
I've never seen this happen on any version of SQL Server. Have you tried capturing the execution plan and seeing if it changes from run to run? That shouldn't cause incorrect results, and if it does I'd say you've found a serious bug.
What is the isolation level used?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 17, 2014 at 8:47 am
Are you by chance using NOLOCK in your stored procedure?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 17, 2014 at 9:22 am
Hi
The WITH (NOLOCK) hint isn't being used in the SELECT statement.
Regards
Mark
July 17, 2014 at 9:27 am
DataTrim (7/17/2014)
HiI have a parametised SSRS report that when is first run it returns incorrect results (i.e. number of rows and grand total of a value column aren't correct). When run for a second time, and every time after, the correct results are returned until a parameter is changed where a similar thing happens again (but not always). The report is running against static tables in a data warehouse. Both the warehouse and SSRS report are SQL 2005.
The report uses a stored proc. I have run the SQL from the stored proc using the same parameters that generate the problem in the SSRS report and get the same results - first run incorrect number of rows returned then correct on second, third etc execution. This seems to go against everything I know of databases in that the same result should be returned every time.
Does anyone have any ideas on what could be causing this behaviour as all my investigations and tests so far have drawn a blank.
Thank you.
I have actually seen this. I would guess your tables have some Indexes, and transaction logs are enabled and being backed up for increments longer than an hour, you have set no level of transaction isolation or locking hints in the Queries to your DataWarehouse tables.
I set the Recovery Option to Simple, made sure all indexes that effected by query including clustered indexes where rebuilt after each DataWarehouse data load. That made things better most of the time.
What fixed it permanently was putting NOLOCK locking hints .
This seemed to resolve everything, but if it does not for you, try doing a table sp_recompile after your datawarehouse data load jobs complete.
July 17, 2014 at 9:28 am
DataTrim (7/17/2014)
HiThe WITH (NOLOCK) hint isn't being used in the SELECT statement.
Regards
Mark
Okay... ummmm.... Was this a test? Are you making a confession? Are you saying that you tried this with no results?
July 17, 2014 at 9:31 am
Is there something else other than a select statement in that proc? Trying to gauge if nolock is used in the proc at all.
And back to Jacks question about isolation levels. Is the proc setting an isolation level within it? What is the isolation level set at for the database(s) in question?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 17, 2014 at 9:38 am
SQLRNNR (7/17/2014)
Is there something else other than a select statement in that proc? Trying to gauge if nolock is used in the proc at all.And back to Jacks question about isolation levels. Is the proc setting an isolation level within it? What is the isolation level set at for the database(s) in question?
If it is setting Isolation level in the sproc that would explain why running it in SSMS could have this effect.
Very large Datawarhouse databases can take too long to populate these things so I use the (NOLOCK) statements that do something much different than your default Snapshot Isolation levels on SQL 2005.
July 18, 2014 at 4:38 am
Thanks to everyone who has replied to my question. To answer some of the questions that have been raised:
- The default database isolation level is set to Read Committed
- No isolation level is being set in the stored proc
- No Lock is not used in the stored proc
So far I have tried adding in NOLOCK to the stored proc and this hasnt stopped the incorrect results on first run. I have also compared the exection plans between 1st and 2nd runs and they are both the same. I'm just going through the process of checking all the indexes are present and being rebuilt after the warehouse rebuild.
Thanks again
Mark
July 18, 2014 at 7:40 am
Based on all of this, can you provide DDL and DML that would reproduce the issue? Since the issue occurs when running the stored proc alone, there is no need to include a report. But having a look at the query that can cause the issue will provide tons of help.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 12, 2014 at 3:03 pm
Did you ever find a fix for this issue? I am experiencing the same thing. SQL08R2 Reporting Services connecting to an Analysis Services Cube. Getting an incorrect sum until the report is refreshed and then its correct after that until the parameters are changed to something that I'm "thinking" has not been cached for that session. I've tried changing cache settings on the cube, data source, etc. Nothing that I try has fixed the issue.
Thanks,
~ Frankie
September 16, 2014 at 7:43 am
Hi Frankie
I think this issue has been resolved. One of the tables used in the stored proc didnt have a clustered index on it. Adding a clustered index to this large table seems to have resolved the issue in our dev environment. Just waiting for the change to be made in live to be certain this was the cause.
Regards
Mark
September 16, 2014 at 7:46 am
Ok, thank you! I ended up installing Service Pack 2 and it appears to have fixed my issue.
September 16, 2014 at 8:33 am
I had not heard of this being caused by the wrong Service Pack level.
However every version of Reporting services has suffered from this when the data was:
* missing Indexes
* Need Indexes rebuilt
* Working with Heaps (See missing indexes)
* Working with Real Number data types (they do not always sum the same)
September 16, 2014 at 9:12 am
When you say every version do you also mean 2008 R2? I will check the indexes and see if there are any missing or that need to be rebuilt. Our server had never had any Service Packs applied to it. Once I applied SP2 the page that was coming up incorrect on the first run started to be correct. However, one of my columns on the first page came up blank after the update but when I looked at the dataset's query, it was calling the total differently than the second page. So I updated the DS to match the way the 2nd page was calling the total and now both pages are working correctly each time I run them.
It is definitely a hair pulling issue :crazy:
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply