Incomplete upgrade from SQL 2000 to SQL 2005

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)

Incomplete upgrade from SQL 2000 to SQL 2005

Author

Topic

David Singleton
Starting Member

35 Posts

Posted - 2010-02-24 : 12:38:32

(some background)
I work with a product that uses SQL as a backend. So I know SQL pretty well, but compared to everyone here I would be a beginner. The current product works on 2005 and 2008, earlier versions ran on both 2000 and 2005.

I have a client that has various versions of Navision running on Various versions of SQL, mostly on VMWare.

I searched the forum and found most of what I need to know, but need some more help.

Recently (a few months back) one database (using an older version of the product) was moved from a physical box to a VM machine. the box ran 2000 to the VM running SQL 2005. Now it seems to me that the upgrade was incomplete.

1/ I a seeing a number of tables with clearly wrong row counts, so my guess is they missed the dbcc updateusage step.

2/ I am trying to do get some queries and getting errors, so I checked and found that the DB is still set to compatibility level 80. The product is compatible and fully tested with 80 or 90, but some features are disabled at 80.

So I have two questions.

1/ Can I just run dbcc updateusage and set compatibility to 90 after this time and will it be OK?

2/ Is there some way I can find out if any other steps have been skipped?

Thanks in advance, any help appreciated.

David Singleton

Microsoft MVP Dynamics NAV

Kristen
Test

22859 Posts

Posted - 2010-02-24 : 13:21:19

" dbcc updateusage step"

SQL 2000 was unreliable on this. Each time it ran it usually changed something. SQL2005 is, supposedly, "perfect" and has no leaks in this regard. The data is for reference only, so run that update once, and thereafter you should not get any further errors.

"Can I just run dbcc updateusage and set compatibility to 90 after this time and will it be OK?"

Personally I would do a full regression test when moving to a new Compatibility Mode - and check with Vendor if they have any patches for the newer version / compatibility mode.

There were some query plans that were adversely effected by some upgrades (specifically SQL 2000 ServicePack 3 -> SP4, so may not effect SQL2005 CompatbilityMode 80 -> 90) ... but that is the sort of thing that could impact you.

Upgrade Advisor should tell you if you have anything that will not work in SQL2005 (native). We had some sloppy coding that worked [but shouldn't have done ...] in SQL2000 but broke in SQL2008.

But on the whole my gut-feeling is that all will run faster using the native compatibility mode for the SQL version you are on (particularly if it uses newer features available in the language - although from what I understand of Navision there isn't much hope of SELECT * performing better whatever you do!!)

"Is there some way I can find out if any other steps have been skipped?"

I'm not sure any of them are critical, just desirable.

Not rebuilding the indexes and updating the statistics will likely give poor performance, but those are commonly included in regular housekeeping - so even if not done at the time are probably being done daily / weekly (if not then they should be).

Other things like changing to use CHECKSUM (which gives earlier reports if there is corruption in the DB files), and so on, can be done at any time.

However, now you mention it!, I am 100% certain that all our migrated databases are perfectly configured, but a script that checked all these settings would give me a little more reassurance

... perhaps it s time that I knocked something up.

David Singleton
Starting Member

35 Posts

Posted - 2010-02-24 : 22:25:24

Thanks Kristen, that helps me a lot. The product is fully tested on 90, so no problems there. Just wanted to make sure there wasn't something else they missed. Statistics are updated weekly and indexes based on how fragmented they are.

But there are probably 150 or so dbs on 60 or 70 instances/VMs on the server, so I guess I need to look at some way to go through and check them all. I get the feeling that is going to be a manual process.

And yes my concern is about the 90 mode giving us a bad execution plan. In our world, a bad plan can take 30 minutes to respond some times, and right now I am seeing in some cases 20% of IO going through the tempDB, so something is wacky.

David Singleton

Microsoft MVP Dynamics NAV

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 02:14:14

"I get the feeling that is going to be a manual process."

A SQL script can check for Compatibility mode, what sort of page-protection is in place, when indexes were last rebuilt, that sort of thing ... so it should be possible to script all the "system checks" and run that on each database.

Easy enough to automate for all databases on the current server. I don't know about automating for all-servers-in-the-organisation/world though

that's not a problem I have to worry about here ...

" And yes my concern is about the 90 mode giving us a bad execution plan. In our world, a bad plan can take 30 minutes to respond some times, and right now I am seeing in some cases 20% of IO going through the tempDB, so something is wacky"

If you can pinpoint a bad query (well, a "slow" query) then my approach would be:

Use SQL Profiler to "steal" the actual SQL sent to server (unless you have an easier way of getting it).

Get/check the query plan for that one query.

Restore that DB to a fresh, test/temp database, convert to 90, rebuild all indexes and update stats, re-check the same query plan.

(I prefer text query plans, 'coz I like to see easily whether Index Seek (best), Index Scan or Table Scan (worst) were used, but others prefer the GUI Graphical plans. If you need command for Show Query plan in text, and Show Logical I/Os then pls ask)

David Singleton
Starting Member

35 Posts

Posted - 2010-02-25 : 15:55:41

Thanks Kirsten,

looks like it all worked OK. Now I can get the queries I want and now start tuning.

This is where I need to start:

This query runs 3.6 million times
TableA contains 33.5 million records.
TableA has 77 fields

quote:

SELECT SUM("Invoiced Quantity"),SUM("Sales Amount (Expected)"),SUM("Sales Amount (Actual)"),SUM("Cost Amount (Expected)"),
SUM("Cost Amount (Actual)"),SUM("Cost Amount (Non-Invtbl_)"),SUM("Cost Amount (Expected) (ACY)"),
SUM("Cost Amount (Actual) (ACY)"),SUM("Cost Amount (Non-Invtbl_)(ACY)"),SUM("Purchase Amount (Actual)"),SUM("Purchase Amount (Expected)"),SUM("Discount Amount"),SUM("Valued Quantity"),
SUM("Cost Posted to G_L"),SUM("Cost Posted to G_L (ACY)") FROM "MyDatabase"."dbo"."TableA" WITH (READUNCOMMITTED)
WHERE (("Item Ledger Entry No_"=@P1))

The only value used is "Invoiced Quantity".

and

quote:

SELECT * FROM "MyDatabase"."TableA" WITH (UPDLOCK) WHERE (("Item Ledger Entry No_"=@P1)) AND (("Adjustment"=@P2)) AND
"Item Ledger Entry No_"=@P3 AND "Expected Cost"=@P4 AND "Document No_"=@P5 AND "Partial Revaluation"=@P6 AND
"Entry Type"=@P7 AND "Variance Type"=@P8 AND "Adjustment"=@P9 AND "Entry No_">@P10 ORDER BY "Item Ledger Entry No_","Expected Cost",
"Document No_","Partial Revaluation","Entry Type","Variance Type", "Adjustment","Entry No_" OPTION (FAST 5)

Only two fields are needed from this query.

I have pages of queries like this.

David Singleton

Microsoft MVP Dynamics NAV

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 18:23:16

WITH (READUNCOMMITTED)

Hard to believe that something that is getting financial data (which executives presumably use to make decisions that could effect the whole outcome of the company) would use READUNCOMMITTED

OPTION (FAST 5)

This is presumably their way of getting the TOP 5 rows? ... Maybe I'm being unkind ...

And "us lot" haven't used double-quotes around column names for nearly 10 years!

The first query won't optimise much / at all.

The second one almost certainly will (if the right indexes are in place)

To get the Query Plan (in plain text), or the logical I/O's:


-- Comment in the SHOWPLAN to see the Query Plan, OR the STATISTICS to see the logical stats
-- SET STATISTICS PROFILE ON; SET SHOWPLAN_TEXT ON	-- If Temp Tables involved
-- SET SHOWPLAN_TEXT ON
GO
-- SET STATISTICS IO ON; SET STATISTICS TIME ON

... *** YOUR QUERY HERE *** ...

SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO
SET SHOWPLAN_TEXT OFF
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources