February 1, 2011 at 9:19 am
HowardW (2/1/2011)
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?
Can't be a table. It has to be a synonym or a view. The plan would have about 100 too many extra steps if those were tables.
February 1, 2011 at 9:33 am
Right, they're not table, are both views.
Just an explanation about the group by with all columns. Our developers had used this syntax to verify if views results are the same. In a sample:
"
select sum(src) as scr, a, b
FROM
(
select src = 1, a, b from view1
union all
select src = 2, a, b from view2
) tbl
group by a, b
having sum(src) != 3
"
Consider that the final views are built on other views, and the architecture on these view levels are choosen on the fly by our application, that mixes data from multiple sources and build all structures (tables and views) needed by a complex reporting query. It's better that I complete the 'simplified version' of this framework for you.
Thanks for yor attention and help
February 1, 2011 at 9:35 am
Agreed they must be views (called tbl_. .. ?!?!?)
I did notice that the Actual row count coming from [VotaAntonio].[dbo].[IDQ_000acd57f2394d8a8ed291773efdab25] is different between the two queries...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 1, 2011 at 9:36 am
Do as you wish... but there's nothing more I can offer than fix the missing join.
February 1, 2011 at 9:38 am
What patch level are you on on that server?
February 1, 2011 at 9:53 am
Thank you Ninja's_RGR'us. I noticed that warnings, and the 'query writers' are working.
The server update status is ** SQL Server - Product version: 10.50.1600.1 - Product level: RTM ( - Edition: Enterprise Edition (64-bit)) ***
I don't like so much to install CU's, so... But perhaps it's a way.
hi
February 1, 2011 at 9:56 am
lindbergh_ddv (2/1/2011)
Thank you Ninja's_RGR'us. I noticed that warnings, and the 'query writers' are working.The server update status is ** SQL Server - Product version: 10.50.1600.1 - Product level: RTM ( - Edition: Enterprise Edition (64-bit)) ***
I don't like so much to install CU's, so... But perhaps it's a way.
hi
Well 2 things. Either the query is wrong which is still likely at this point or you're hittinga bug. Either case upgrading the server(especially ifit's isolated can't really hurt).
Also there's a bug fix in 2k8 iirc that fixes false join warnings (lost 1 day last week on 2k5 about a similar bug, tho the results were always the same).
February 1, 2011 at 10:03 am
Well 2 things. Either the query is wrong which is still likely at this point or you're hittinga bug. Either case upgrading the server(especially ifit's isolated can't really hurt).
I really wouldn't apply CU's without having a clue what's causing the problem - the OP's running the latest build of SQL Server - there are no SP's available for 2008 R2 yet.
Based on what you've said in terms of auto code generation and views on top of views, I suspect that it's a problem in the code, but if it's too complicated to even post the details of then you're unlikely to get any conclusive cause.
Writing a simplified mock-up of the code is likely not going to help
February 1, 2011 at 10:03 am
Ok, I'll try. I'll tell you some news (i hope...)
February 1, 2011 at 10:08 am
HowardW (2/1/2011)
Well 2 things. Either the query is wrong which is still likely at this point or you're hittinga bug. Either case upgrading the server(especially ifit's isolated can't really hurt).
I really wouldn't apply CU's without having a clue what's causing the problem - the OP's running the latest build of SQL Server - there are no SP's available for 2008 R2 yet.
Based on what you've said in terms of auto code generation and views on top of views, I suspect that it's a problem in the code, but if it's too complicated to even post the details of then you're unlikely to get any conclusive cause.
Writing a simplified mock-up of the code is likely not going to help
My bad, I was thinking 2008, not R2.
February 2, 2011 at 10:46 am
Hi everybody.
I came to a simplified version of the structure that causes different results (number of rows and values) in my Wind2008 R2 + Sql2008 R2 environment.
I attached a db backup with tables, views and data as they are on my test server, and an excel file with the results I obtain by different result.
Briefly:
- (with maxdop <= 2) OR (with a specific where clause on the resultset) the result are correct
- (with maxdop > 2) OR (with unspecified maxDop option) the result changes (as record number and values)
To produce the behaviour simply run the view_final_TOCOMPARE_2 view.
Platform Windows 2008 R2 Enterprise x64, SQL Server 2008 R2 x64 Enterprise. The problem happens on this configuration only (2 CPU QuadCore).
Finally: I found this http://support.microsoft.com/kb/968449. Title is "FIX: A query that runs in parallel on a multiprocessor computer returns incorrect results in SQL Server 2005 or in SQL Server 2008"...
Is there someone how had this problem in aprevious SQL version?
Thanks everybody
February 3, 2011 at 2:13 am
I haven't got an R2 environment to hand at the moment - any chance you could post the DDL for the view you've created to test this?
February 3, 2011 at 3:35 am
Hi everybody.
And finally I found a KB concerning my problem.
As you can read in my older posts the incorrect results seemed related to the maxdop value AND to server CPUs number. In my experience, 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).
Article http://support.microsoft.com/kb/981502 (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.
Note
This 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'm downloading last SQL Server 2008 R2 CU: it had to fix the bug. Once again, I don't like CU, but I need to verify if it really works. Although it's not so dramatic for our application (it has an internal logic to avoid incorrect results by 'materializing' view data) I believe that this behaviour has to be emphasized: "...returns different results every time that you run the query..."
After patching I'll write some news.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply