Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lindbergh_ddv
Starting Member
2 Posts |
 Posted - 2011-02-04 : 09:14:53
|
| Hi everybody. I need some help about a 'strange' behaviour I noticed on my test server.Our application buils database structure (tables and views) on the fly in order to produce a single resultset by many different data source. So it's not unusual to have a view tree, with more level of references.I noticed that the final result changes everytime I run the 'top' view: num records and values of the final data set are different each time... So I get the 'flying' database structure and I saved in a isolated database and server environment. Then I simplified the execution logic: nothing at all, always different results.Condider the following:a. my server: Windows 2008 R2 x64, 8 CPU * 2.53Ghz + Sql Server R2 (10.50.1600) x64b. behaviour happens ON THIS CONFIGURATION ONLYc. if a put the maxdop <=2 everything works fined. if I limit the server to 4 CPU to use, everything works finee. if I put a where clause outside the view, data are always correctf. it I SELECT INTO a permanent table the view resultset, the table data are always correctAfter some searching I found something intersting.Article [url]http://support.microsoft.com/kb/981502 [/url](Fix: A query that uses a parallel query plan returns different results every time that you run the query in SQL Server 2005, SQL Server 2008 R2 or SQL Server 2008)seems to describe exactly the behaviour."Consider the following scenario:- On a multiprocessor computer, you run a query in Microsoft SQL Server 2005, Microsoft SQL Server 2008 R2 or Microsoft SQL Server 2008.- The query uses a parallel query plan that has a high degree of parallelism.- The query plan contains a many-to-many parallel merge join operator.In this scenario, the query may return different results every time that you run the query. NoteThis problem is reported for computers that have eight processors. However, you may also experience this problem on computers that have fewer than eight processors. Generally, high degrees of parallelism cause incorrect results to be observed more frequently."So I downloaded last SQL Server 2008 CU and R2 CU too. I don't like CU, but I needed to verify if it really works. Unfortunately the problem is still alive after patching: now my Sql Server 2008 R2 is 10.50.1753 version, but query results gon on changing every run...In short: my problem doesn't raise in environments with less than 8 processors OR with an MAXDOP option >= 2. I reproduced the incorrect execution on SQL Server 2005, 2008 and 2008 R2, but ONLY in eight processor server. S.O. type is not important (I tested Win 2003, 2008, 2008 R2, Standard & Enterprise). I have the simple db that produces the bug.Do someone know something more about this SQL - DOP bug? It makes me wonder: it's not so good that "the query may return different results every time that you run the query"...Thanks to all for help.antonio, Torino - Italy |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-02-04 : 13:50:02
|
quote:
if I put the maxdop <=2 everything works fine...
quote:
In short: my problem doesn't raise in environments with less than 8 processors OR with an MAXDOP option >= 2...
So which one of the above is true?MAXDOP <> 0 is desirable as the optimizer will not consider all the processors for parallel query plans which can give inconsistent results.PBUH |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lindbergh_ddv
Starting Member
2 Posts |
Posted - 2011-02-07 : 03:18:20
|
| Sorry for misprint: behaviour happens with maxdop > 2.Now I'm in touch with Microsoft about this 'bug'. Anyway it makes me wonder that there's the possibility to obtain inconsistent results...Thanks for answers. |
|
|
|
|
|
|
|
|
MAXDOP: query with different results every run...