SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


compatibility level


compatibility level

Author
Message
Q -631159
Q -631159
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 Visits: 165
When we upgraded from 2000 to 2005 we tested running everything in 2000 compatability mode. We did not see any degredation in performance. I will concede that we do not have tera byte databases, but we do have some databases in the double and triple didget gigabyte range. We have moved internal databases to 2005 after completing thorough testing in development.

We wait until vendors certify their software on 2005 before moving their databases. We still perform testing in development first. The major reason for waiting for vendors to certify is that they generally will not support the product if there are issues and they have not certified.

Q

Please take a number. Now serving emergency 1,203,894
jim.powers
jim.powers
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2778 Visits: 871
Interestingly enough, our vendor has certified the product for SQL Server 2005 but they require the database to remain in compatibility mode 80.
Cliff Jones
Cliff Jones
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5177 Visits: 3648
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.
Paul Mu
Paul Mu
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1427 Visits: 391
What's the point of that! Unless they are not 100% sure that everything will work in 90 mode? Don't trust your vendors and fully test your system yourself I would say Tongue
Cliff Jones
Cliff Jones
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5177 Visits: 3648
We are the vendor for our product. Our requirement was that the product still had to run on SQL 2000 and also on SQL 2005. We discovered through testing that the behavior of certain queries was not exactly the same on level 90 as level 80 which forced us to run on SQL 2005 on level 80 for one version.

As a vendor you always have new customers and early adopters that want to run on SQL 2005 and a few customers you have to drag kicking and screaming to the new platform. They are the lowest common denominator . So you have to create an upgrade path that allows the late adopters to get there.
Q -631159
Q -631159
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 Visits: 165
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
chileu17
Right there with Babe
Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)

Group: General Forum Members
Points: 789 Visits: 401
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 BigGrin
Thanks.
Cliff Jones
Cliff Jones
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5177 Visits: 3648
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
chileu17
Right there with Babe
Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)

Group: General Forum Members
Points: 789 Visits: 401
Thanks for that quick answer Cliff. But I have one last question to fire BigGrin
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
Cliff Jones
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5177 Visits: 3648
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search