compatability 90 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

compatability 90

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2009-05-29 : 22:31:48

I am on a new prod sql server and noticed all their db are 80 and been running for period of 8 mths. I need to set all to 90 and run update statistics and rebuild all indexes.

Is there anything i should do with the cache procedures or anything else since they been running for ever on the wrong compatability and not taking much use of the new query optimizer.

Cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-30 : 02:49:24

you should be careful in changing the compatibility level of production db which has been working in current level for long. there are few features which are not supported by 90 level like old join syntax and any code that uses them will stop working. suggest you first check for any such usages in db and make sure any such code is rewritten in new way before you apply this change.
to understand the differences b/w compatibilty level see below

http://www.mssqltips.com/tip.asp?tip=1436

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-30 : 03:20:12

You can download an "upgrade wizard" from Microsofts' site.
It will find incompatibilities.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-30 : 03:36:37

also see

http://www.databasejournal.com/features/mssql/article.php/10894_3595276_1/SQL-Server-2005-Upgrade-Advisor.htm

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2009-05-30 : 09:50:35

Thanks but i know this db works. But good advice

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2009-06-03 : 14:56:13

HI,,before we set to 90 i will be testing but it ok to update the indexes for mode 80 databases on 90. I am aware the statistics are different in 90 and the way the optimizer works in 90 so rebuilding indexes on 80 will use the old methods of optimizing...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-03 : 16:28:15

Updating the stats is necessary after upgrading a database to SQL 2005, not after changing the compat mode. All the compat mode affects is how some T-SQL constructs are interpreted by the query processor. Nothing else. The database is still a 2005 database and the optimiser, stats and indexes behave the same as any other SQL 2005 database.

--
Gail Shaw
SQL Server MVP

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-06-03 : 16:32:18

you might also want to enable the CHECKSUM option for Page recovery under database options. It offers more robust torn page detection than earlier versions.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2009-06-03 : 21:13:51

The database is still at 80 on SQL 2005 and some can't be changed to 90 until i upgrade them. There no sql 2000 server so doing update statistics and re build index be on the 80 database. I guess it still will work on 80 mode database and SQL uses the old optimizer formats ? I just wondering if i have to move them all back to SQL 2000 as updating stats and index on DATABASE 80 is not doing anything. Hope that makes sense.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-04 : 03:07:57

A database on SQL 2005 is a SQL 2005 database and uses SQL 2005-format statistics, etc. Compat mode does not affect how the optimiser works nor does it affect statistics, index formats, the optimiser or anything like that.
The only thing that the compat mode affects is how certain pieces of T-SQL are interpreted. Nothing else.

Oh, and you cannot move the DB back to SQL 2000 as it is a SQL 2005 database and SQL 2000 does not understand the file format and system tables used by SQL 2005.

What exactly do you mean by "updating stats and index on DATABASE 80 is not doing anything"

--
Gail Shaw
SQL Server MVP

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2009-06-04 : 09:09:19

The databases were on SQL 2000 and then moved over to SQL 2005 using restore methods.
Not move a database back to SQL 2005 regardless if set to 80.
Oh setting to 90 just have effect on how TSQL is interpeted - is this better methods ?

This is what i done when moving db from SQL 2000 to SQL 2005 and set compability to 90. In this case leaving it to 80.
So should continue to do this for all databases currently on the SQL as the statistics have never been run and index frag never run too.

----Set Mode to 80, CHECKSUM , AUTO SYNC
--Conversion scripts
--Run in order
DBCC CHECKDB WITH DATA_PURITY

--Update Statistics
Sp_MSForEachTable 'Update Statistics ? WITH FULLSCAN'

--Rebuild Indexes
(Rebuild all indexes )

--Update Usage
DBCC UPDATEUSAGE('DB NAME') WITH COUNT_ROWS

I can still use AUTO SYNC on database when comp is 80 --i thought it had to be 90 mode....thats good news i will be setting some of my databases to do this update statistics automatically...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources