Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

compatibility level Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2008 8:10 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 26, 2008 7:27 AM
Points: 239, 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

Post #492141
Posted Tuesday, April 29, 2008 8:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:11 AM
Points: 1,330, Visits: 815
Interestingly enough, our vendor has certified the product for SQL Server 2005 but they require the database to remain in compatibility mode 80.
Post #492146
Posted Tuesday, April 29, 2008 8:22 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 3,909, Visits: 3,636
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.
Post #492154
Posted Tuesday, April 29, 2008 8:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:02 AM
Points: 339, Visits: 390
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 :P
Post #492159
Posted Tuesday, April 29, 2008 8:38 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 3,909, Visits: 3,636
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.

Post #492176
Posted Tuesday, April 29, 2008 8:42 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 26, 2008 7:27 AM
Points: 239, 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

Post #492179
Posted Tuesday, April 29, 2008 9:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:59 AM
Points: 119, 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 :D
Thanks.
Post #492235
Posted Tuesday, April 29, 2008 9:33 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 3,909, Visits: 3,636
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.
Post #492266
Posted Wednesday, April 30, 2008 8:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:59 AM
Points: 119, Visits: 401
Thanks for that quick answer Cliff. But I have one last question to fire :D
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.
Post #492970
Posted Wednesday, April 30, 2008 8:33 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 3,909, Visits: 3,636
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.
Post #492984
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse