January 31, 2011 at 4:13 am
Hi everybody. I need some help about a 'strange' behaviour I noticed on my test server.
The environment is a Windows Server 2008 R2 Enterprise x64, 8 CPU * 2.53Ghz. And Sql Server 2008 R2.
There's a piece of logic of my application that builds a data comparison by more level views, and finally get 2mlns records as result. What happens?
a. By monitoring the comparison, everything works fine
- on my Windows 2003 X64 Server
- on my Windows 2003 x32 Server
- on my local server Windows 7 X64
b. Just in the Windows 2008 R2 x64 environment I have different result set, but IF I SET THE MAXDOP <=2 the result set matches perfectly.
The SQL Server version is everywhere 2008 R2 (10.50.1600). The differences are the following:
a. in all environments (except Windows 2008 R2) the comparison tells that the two millions of rows of the table A are exactly equal to the 2 millions of rows of the table B.
b. in the Windows 2008 R2 environment I have 20.000 marked as different; the differences are in a numeric field, calculated in the last level of views. BUT
- if a put the maxdop <=2 everything works as usual (all rows results as equal).
Another strange thing is the following. The two final views on which I compare the data return the same data; in effect, if I materialize these views results (= if I put the views data in two permanent tables) and then I compare them, it works.
So, my strange situation is: a view1 and a view2 with the same data as result; if I compare these views by anothere query SQL tells that there're 20000 unmatching rows; if I put the data into two permanent table and then I compere these tables, SQL tells that all rows are matching.
Did someone see something like this? I tested all environments carefully; if needed, I have a little db backup that shows the 'different results mistery'.
Thank you very much everybody.
January 31, 2011 at 6:12 am
I'll chime in to try and spur some conversation.
I'm not sure how MAXDOP works in detail or what effect it has on query results but I'll touch on something you mentioned.
When comparing two views for unmatched records, you get different results than if you ran the same query on the same views in other instances on different platforms.
When comparing two tables for unmatched records (made from the same views), you get the same results as when you ran the same query on the same views in other instances on different platforms.
This makes me think it's an instance collation problem. Perhaps they (defaults collations) don't match across all platforms, hence the different results. Then I think about your comments about MAXDOP <= 2 and things are hunky dory and I'm stumped. Not sure how that would have anything to do with results.
Perhaps posting the query might help? Help us see what you're working with?
Table structure, sample data, view scripts, and query might help.
January 31, 2011 at 12:26 pm
We actually had a situation very similar to this where we would receive different results based on the MAXDOP being greater than 1. It also turned out that it could be temporarily resolved by updating the stats on every table involved in the query with full scan. In th end we opened up a case with MS, and they discovered that it was a legit bug (we are on SQL 2008 not SQL 2008 R2) in the optimizer and it was fixed as part of CU6 on SP1 or within SP2 itself. We went straight to the service pack and have not been able to replicate the issue since that time.
It is possible that this same bug is within SQL 2008 R2 and that potentially they have fixed it with a current CU? Have you attempted to load the most recent CU onto your server? I think CU5 is out there now. I am not a big fan of loading the CU's and far prefer the SP's when available but it may be worth your time to test this on one of your dev environments or a test server somewhere.
February 1, 2011 at 4:37 am
Thank you for answer.
Old Hand, thank you for your suggests. Unfortunately the collations are the same on all instances (we have a strict standard about this). Plus, updating the maxdop value results had to be the same. It seems to me that with a maxdop > 2 QPE follows wrong routes to execute the query, so wrong that the same data may be discard or not with same clauses.
I have to add some detail about this in order to exemplificy.
The 'comparison' is not the core of our trouble, it's just the top of the application logic, in other words just the final result. The core is: I have two views (based on views and permanent tables) that HAVE to return the same data set as result.
Each view has a not so unusual logic inside, similar to
CREATE view1 as
SELECT field1, field2, ...
FROM view_1_a AS c LEFT OUTER JOIN
view_1_b AS d ON d.Code = c.Code AND
d.ValidityDateFrom <= c.EndEvaluationDate
LEFT OUTER JOIN
view_1_c AS rdc ON
rdc.Code= d.Code AND rdc.ValidityDateFrom <= c.EndEvaluationDate
I'll add another 'strangeì following behaviour, related to a platform SQL 2008 R2 and Windows 2008 R2 (with maxdop >2) only.
Suppose that view1 returns 10 rows, as follows:
field 1 field2
a 5
a 1
a -4
b -5
b 12
b 23
c 34
d 4
e 23
e 12
If I put a where clause outside the view in order to obtain just field1 = 'b' (select * from view1 where field1 = 'b' ), SQL will returns more than 3 rows returned by the non-filtered view... So it's evident that the results are different; changing the maxdop the number or rows (and the calculated numeric values too) may be different...
Thank you for sharing your experience, SSC-Enthusiastic: mine might be the same bug inherited from SQL 2008. I don't like CU's too, but sure I'll try in test enviroment.
I'll write soon if something happens by this patching. It seems intersting to me: it's not so good that a query may return different result changing the number of CPUs involved in the work, isn't it?
Hi everybody
February 1, 2011 at 6:42 am
Hi,
You don't mention how you are ORDERing the queries , how you are restricting the results (WHERE) nor whether you are using TOP.
Can you clarify those points as they could easily have an effect on this.
Also, can you explain how you are testing for "matching" rows? you mention it is based on a numeric column - what is the exact datatype and how exactly are you comparing?
Thanks
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 1, 2011 at 6:43 am
Can you post the actual view's and create table scripts?
You may be doing something in the views that relies on an arbitrary order (e.g. TOP or Row_Number() without ordering by a unique field) that returns different rows depending on however SQL Server happens to generate the execution plan. I doubt the parallelism in itself is the cause of the problem, it just highlights it as you're forcing a different execution plan.
February 1, 2011 at 8:07 am
You're right. I'm reducing the logic to send you a more clear sample to reproduce the mismatching results.
Just a note: ok, ordering and where clauses may be the reason why, I thought this. But I have to remark this point: with MAXDOP <=2 query returns a data set, with MAXDOP > 2 query return a different one.
So the question is conceptual, for me: is it possible that the same query has different result (and I mean different num rows and different values) depending of that query hint?
I'll send as soon as possible the structure details.
Thanks for your attention.
February 1, 2011 at 8:11 am
everything is possible if you are using read uncommitted or nolock.
Also, no insult intended, it's possible you are running this on prod server and that the data changes between runs.
You could also have different connection settings depending on how you are running this.
Different accounts could do this too.
February 1, 2011 at 8:12 am
lindbergh_ddv (2/1/2011)
So the question is conceptual, for me: is it possible that the same query has different result (and I mean different num rows and different values) depending of that query hint?
Yes. If you ask for an arbitrary set of data (e.g. you do not specify exactly which rows and in which order they must be returned) then you are in no way assured of consistent results.
The important point here is that it is not the query hint that is causing the problem, SQL Server could decide on it's own to change the plan that's generated based on different statistics/data volumns, or just how busy the server is at execution time. The hint just highlights the problem by guaranteeing you'll get a different plan to the original one.
February 1, 2011 at 8:15 am
What do you mean by different data? Different order in the results (almost certain with parralelism). Or different data altogether?? whereas an except in both directions would return data?
February 1, 2011 at 8:35 am
Yes, I would say it is definitely possible to get different results for an undefined set based on DOP.
If you can post the actual execution plans for both queries it will be easier to explain it to you.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 1, 2011 at 9:02 am
I'm not using uncommitted data. Everything is saved on a isolated db for test purpose, as fixed, 'read only' data, and I'm the only reader. On sql 2005 platform (or sql 2008 using at least 2 cpus) results are always the same.
Ninja's_RGR'us, different results means what you can see in attached picture. I copied the results for the code '1000016' returned in the whole data table by MaxDop =2 and MaxDop = 4; then I get data returned by MaxDop = 4 AND filtered by (WHERE Code ='1000016') clause. The field value has many different values for the MaxDop = 4 only...
Returned records are unique (fields src + code + yearSequence).
February 1, 2011 at 9:09 am
Hi Mister Magoo. These are the two QEP (QEP_MaxDop2 is with option MaxDop 2).
Thank you!
February 1, 2011 at 9:16 am
Are the below two objects tables or views?:
[tbl_233421d595dc4719b2429d27bf5a0399_\PreparingFactTable\CommissioniPerformance\FactTab]
[tbl_527741baf48446da94b7e767ed004314_\PreparingFactTable\CommissioniPerformance\FactTab]
Can you post the DDL for these if they're views?
February 1, 2011 at 9:18 am
We need the basic view definitions as well.
You have 4 very serious warnings about missing join predicates that I'd take care of 1st... that may even fix it for you.
That could in part explain why you have to group by all columns (this is sometimes a sign a unintended cross join).
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply