compatibility level

Question

compatibility level

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Q -631159 Ten Centuries Points: 1077 More actions · Answer 1

I believe that certifying a product can run in 2005 only in compatability mode is so companies can move forward with upgrades. Some products will not run in 2005 because of the changes in the way 2005 did security among other things. In compatability mode SQL server still functions in the same manner. Some vendors have not changed their applications to function properly in 2005 mode. I would find out what the vendor is planning when 2008 is released.

Q

Please take a number. Now serving emergency 1,203,894

chileu17 Hall of Fame Points: 3093 More actions · Answer 2

Cliff Jones (4/29/2008)
jim.powers (4/29/2008)
Interestingly enough, our vendor has certified the product for SQL Server 2005 but they require the database to remain in compatibility mode 80.
Actually, that is what we did as well since we had to have a version that was backwards compatable to SQL 2000 but that we certified to run on SQL 2005. So that is not uncommon. Since it had to be backwards compatable we could not use any SQL 2005 features and it had to run compatibility mode 80.
Generally we find that performance over all is better on SQL 2005 but we do find several queries that run considerably slower on SQL 2005. We have opened up incidents with Microsoft on these and they admit there are some issues with complex queries involving outer joins. We have had to add some Force Order query hints to resolve them until we can rewrite the queries.

Hi Cliff, you said that some queries run slower in SQL 2005, what kind of queries are they? and do you have any ideas why is that? I am a developer and would be thrilled to know about the things I can do better 😀

Thanks.

Cliff Jones SSChampion Points: 10547 More actions · Answer 3

Yes, generally they are complex quereies involving both inner and outer joins. SQL 2000 would join the Inner Join tables first since they were the most likely to reduce the result set. SQL 2005 does not necessarily join the tables in that order since sometimes the outer joined tables may be very small. But sometimes when the optimizer chooses to perform the outer join first, the result can be a cartesian product. You will see a No Join Predicate warning in the execution plan and high CPU utilization with LOTS of reads. There is a known bug (#50001820) that is similar to this that was fixed in SP 2, CU 4 but it does not address all occurances of this problem. Adding a Force Order hint on SQL 2005 results in a better execution plan for these queries.

chileu17 Hall of Fame Points: 3093 More actions · Answer 4

Thanks for that quick answer Cliff. But I have one last question to fire 😀

What about the clients?Does changing the compatibility level affect the clients in terms of network connections? I mean like network protocols that are not supported "anymore".

I read somewhere that changing the compatibility level in 70, I wouldn't have to update my clients connections which would be an advantage I guess.

Thanks again for the help.

Cliff Jones SSChampion Points: 10547 More actions · Answer 5

No it should not effect your network connections. You are still connecting to the SQL 2005 database engine. By setting the compatibility level to 80 on your database you are just ensuring that your database still behaves like SQL 2000.

Cliff Jones SSChampion Points: 10547 More actions · Answer 6

After re-reading your question, perhaps I should add that you can only connect to SQL 2005 using the methods that SQL 2005 supports. Changing the compatibility level of one of your user databases to level 80 would not effect those requirements

chileu17 Hall of Fame Points: 3093 More actions · Answer 7

So I would have to upgrade my clients applications connections right? in order to make them compatible with sql server 2005?

Cliff Jones SSChampion Points: 10547 More actions · Answer 8

I am not sure I understand what you mean by upgrading your clients connections, but yes the mechaninsm that you use to connect to the database must be compatible with SQL 2005 since that is what you are connecting to. Changing the compatability level of a user database does not change that. Your system databases and SQL Server's application code is still SQL 2005.

chileu17 Hall of Fame Points: 3093 More actions · Answer 9

I am sorry but my English is not very good anymore :hehe: but you just keep guessing what I am trying to say lol Thanks again for answering so soon. Yeah I was talking about the client connections that in SQL Server are not the same as in Sql 2k. I had probably misunderstood what I read in that article 😀 lol thanks for the explanation anyways.

chileu17 Hall of Fame Points: 3093 More actions · Answer 10

hey what about SQL Native Client??? does changing the compatibiliy level affect this in any way? Thanks

Cliff Jones SSChampion Points: 10547 More actions · Answer 11

Your English is fine. It is probably more the medium that we are using. Same problem with email. Good luck with what you are doing. If you have a link to that article feel free to include it and I will try to decipher it. 😛

Cliff Jones SSChampion Points: 10547 More actions · Answer 12

I think the same idea applies to the SQL Native client. Everything is still SQL 2005. When you set the compatability level of a database to be SQL 2000 running on SQL 2005, it merely emulates the behavior of a SQL 2000 database. But the database still resides on a SQL 2005 instance.