August 27, 2009 at 3:09 pm
So a query runs on SQL Server A and it returns in the "proper" and expected order, it runs on Server B which is not as up to date patched as Server A and it is not in the expected order.
The query sorts on Name.
The Name part works great, the query also returns department.
Now the sort order SHOULD have specified Department Asc, but it did not include Department.
So what is odd is that the way the server returns that second column is different.
Both are identical databases, test and production.
Any thoughts?
Could the differing patch levels be the cause of this?
Some other place I should start looking?
Collation settings are the same on both servers and both databases.
August 27, 2009 at 3:13 pm
Show us. I can't tell you anything based solely on the description of the problem. It would also help to post the DDL, including indexes, for the table(s) involved.
August 27, 2009 at 3:17 pm
Maxer (8/27/2009)
So a query runs on SQL Server A and it returns in the "proper" and expected order, it runs on Server B which is not as up to date patched as Server A and it is not in the expected order.
Does the query have an ORDER BY that specifies the "proper" and expected order? If not, the order that the rows will be returned is undefined.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2009 at 3:17 pm
Yeah, I figured as much.
I didn't know if it would be a simple "Oh yeah, last month patch XYZ came out and it addressed sorting...".
I'll gather that up and post it.
EDIT:
I wonder... if one of the tables had a clustered index on NAME and the query was
SELECT Department, Name
FROM someTables
ORDER BY Department
Then is it at all possible that it would return the Departments, and then the Names would just happen to come back in order as well since no order was given and the table was already ordered by NAME?
August 28, 2009 at 8:14 am
A few more random things:
Looks like one of the servers is Enterprise edition and the other is Standard and the execution plans are completely different between the two.
So I am going to chalk it up to that.
August 28, 2009 at 8:29 am
Maxer (8/27/2009)
I wonder... if one of the tables had a clustered index on NAME and the query wasSELECT Department, Name
FROM someTables
ORDER BY Department
Then is it at all possible that it would return the Departments, and then the Names would just happen to come back in order as well since no order was given and the table was already ordered by NAME?
Maybe, but not necessarily. The order of the index does not guarantee the order of the results. It can (and often does) happen that with simple queries the data comes back in the order of whichever index was used. If however there's any hash or sort in the exec plan, the data will come back in a completely different order.
Bottom line, if you want your data back ordered by X and Y, there must be an ORDER BY X, Y specified or it's quite possible for the data to come back in different orders at different times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2009 at 9:22 am
Makes good sense.
The main question was "Why does this one behave differently than this other one here", and that does help to explain that.
Thanks again!
August 28, 2009 at 10:16 am
Maxer (8/28/2009)
Makes good sense.The main question was "Why does this one behave differently than this other one here", and that does help to explain that.
Thanks again!
If you run the queries enough times (keeping up with other activity on the server to make it realistic), you will find that both will eventually do something different. Meaning 99 times it may do one thing, and then that 100th time, something a little different.
It's not guaranteed (unless specifically in the ORDER BY like Gail was getting at), so it may change.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 28, 2009 at 10:22 am
Now that is very interesting.
Can you explain why that is in more detail?
Does it have to do with the order in which it accesses the data on the table for some reason?
Is one part of the table possibly in use so it "skips it" reads the next part, then doubles back to pickup the first part?
I don't understand the mechanics of SQL Server that would cause that behavior, but I would very much like to understand it.
August 28, 2009 at 10:31 am
Could be influenced by what is in cache (say it already has the table in memory, but ordered a litte differently), could be that the read heads of the drives are "past" the table, so reading the table in "reverse" is faster.... It could be that the data pages for the table are in no particular physical order, so scanning the drive(s) will yield an odd order. It could be that the reads are happening in parallel. In short - lots of possibilities.
The engine is given a lot of leeway to figure out what the fastest way is to return data. This has to do with some of the basic princiaples surrounding RDBMS'es (tables are considered to be unordered sets, so order of operation is irrelevant), but mostly it has to do with being efficient.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply