March 27, 2008 at 1:33 pm
Hello,
Could someone comment on or direct me to any documentation regarding database compatibility level and performance?
Specifically, if one has a SQL 2005 server where database A is 80 compatibility level and database B is 90 compatibility level (both database on the same server), is there any possible performance hit from queries that would join tables from both databases?
Also, aside from performance, are there any other potential issues with using two databases with different database compatibility levels in conjunction with each other?
Thanks for any help!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 27, 2008 at 3:08 pm
I'd probably say it depends! a 2000 db in either mode is still going to be the same database - it's not as if the convert chnages the sql. I've seen big performance gains taking a 2k db to 2005 but as to the rest of it - dunno. I'd advise using 2005 mode as it allows you all the dmvs and custom reports. I'd have to say it just depends.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 27, 2008 at 3:13 pm
OK, thanks! Database A (the one at the 80 level) is a SQL 2000 database that was restored onto SQL 2005, so I am inclined to leave it that way until the client tells me otherwise - unless there is a reason why having the two levels of database interact will cause any problems. (Which they will tell me after testing.) In the long run, they may want to upgrade the compatibility level to take advantage of 2005 features, but I think by that time it will be long past the initial migration so they won't feel the need to remain in 80 anymore.
Thanks again!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 27, 2008 at 3:18 pm
webrunner (3/27/2008)
OK, thanks! Database A (the one at the 80 level) is a SQL 2000 database that was restored onto SQL 2005,
Make sure that you update the statistics. The 2005 optimiser doesn't much like the stats that SQL 2000 kept.
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
March 27, 2008 at 3:20 pm
OK, thanks!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 28, 2008 at 5:16 pm
I'll add your question to my list of things to test, assuming i can figure out a reasonable test that I can repeat.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 25, 2008 at 11:47 am
Hi everyone, so there is not a known answer for this topic yet :crying:
Do you guys think if Microsoft has an answer for this?
I have to migrate a database from sql 7.0 to 2k5 and it's not "that complicated" as I see it 😀 but I have to decide whether the new database (the migrated one) will have the compatibiliy option enabled so I don't have to change all my sp. My database is about more than 50gb but I am worried about the client applications that were connected to my sql 7.0 and about the performance and the down time lol it's not that simple eh?
Hope someone can help me out.
Thanks in advance.
April 25, 2008 at 3:19 pm
chileu17 (4/25/2008)
Hi everyone, so there is not a known answer for this topic yet :crying:Do you guys think if Microsoft has an answer for this?
I have to migrate a database from sql 7.0 to 2k5 and it's not "that complicated" as I see it 😀 but I have to decide whether the new database (the migrated one) will have the compatibiliy option enabled so I don't have to change all my sp. My database is about more than 50gb but I am worried about the client applications that were connected to my sql 7.0 and about the performance and the down time lol it's not that simple eh?
Hope someone can help me out.
Thanks in advance.
Before even thinking of upgrading your prod database, run the SQL 2005 upgrade advisor on it, implement its recommendations in a test environment and...
test, test, test!!! 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 25, 2008 at 4:23 pm
About the upgrade advisor, it's done 😎 and all the implementations it asks for. But there are some warnings like the networks connections that aren't supported anymore by 2k5 and I read in some places that I won't be getting the 100% performance from my server if I use the option compatibility level 70 and that is what I don't know my friend. I am worried about my application clients and the impact this migration will have on them too.
Thanks again 😀
April 25, 2008 at 8:49 pm
chileu17 (4/25/2008)
About the upgrade advisor, it's done 😎 and all the implementations it asks for. But there are some warnings like the networks connections that aren't supported anymore by 2k5 and I read in some places that I won't be getting the 100% performance from my server if I use the option compatibility level 70 and that is what I don't know my friend. I am worried about my application clients and the impact this migration will have on them too.Thanks again 😀
you've got to raise compatibility level to 90 and live with the times! 🙂
It's like someone is offering you an Audi, but you'd still rather drive your old Lada! 🙂
If you are worried about the effect the migration will have on your application, test it thoroughly. No one is going to give you a definitive answer one way or another - every app is unique.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply