SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 sql2000 to sql 2005 Migration Tests
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/01/2008 :  12:25:16  Show Profile  Reply with Quote
Hi guys, long time no speak ...

I have a client who wants to migrate from SQL 2000 to 2005 (or maybe 2008 ...)

I wanted to run some tests to

a) identify any processes that run slower
b) show how much faster their system will be

and thus enable them to "size" the new server.

Can I, realistically, use Profiler under SQL 2000 to record activity on a Live database, and then restore to a point-in-time representing the start of that recording, under SQL 2005 and play back the SQL Profiler recording?

Any alternative plans you would recommend?

Any pitfalls? (I'm pretty sure that are not using any GUIDs)

Cheers!

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/01/2008 :  12:37:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
All systems I have encountered this far during a SQL Server 2000 to SQL Server 2005 migration process have shown a 15-25 percent increase in performance by just upgrading the SQL Server and with a backup and restore of the database.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/01/2008 :  12:48:37  Show Profile  Reply with Quote
Thanks Peso, that's going to be a very useful rule-of-thumb for me to use.

I need to find any processes that run slower - in case they are the amongst the more frequently called ones - hence the proposed SQL Profiler test. What do you think of that as an idea?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/01/2008 :  13:11:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Well... Hard to say because the internals of SQL Server 2005 is different from the internals of SQL Server 2000.
For example, in 2005 the query engine can look into derived tables and correlated subqueries to better see if any index can be used which 2000 didn't do.
I don't think there is a way to foresee that.

But the idea of making a 2000 trace for DURATION, READS, CPU and WRITES and save that, and make a 2005 trace to compare with is a good start, but still an aftermath process.

Send me an email if you like, and I can return a performance matrix chart (in pdf) I did when migrating a customer's SQL Server 2000 to SQL Server 2005.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/05/2008 :  15:18:17  Show Profile  Reply with Quote
Well ... tests to date with REPLAY in SQL Profiler have not been great.

I can live with the limitations (so far ...) [No GUIDs, Database name and all Logins/Password must be identical to the recorded-from server etc. etc.]

But I'm getting stacks of errors replaying the SQL :(

Here's a SINGLE piece of SQL in the recorded profile:

SET FMTONLY ON dbo.MySproc 'Param1','Param2',null,null,null,null SET FMTONLY OFF

clearly the syntax of this is illegal; there are three statement here, and breaks are needed between them :( and nto surprising Replay barfs on this "single statement"

I can set up some Macros in my text editor to take care of some of these, maybe all of them, but its going to elongate the process.

Anyone else used Profiler Trace Replay in anger??
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/05/2008 :  15:25:41  Show Profile  Reply with Quote
"SET FMTONLY ON dbo.MySproc 'Param1','Param2',null,null,null,null SET FMTONLY OFF

there are three statement here, and breaks are needed between them

Ah ... not quite true. I expect this has been coded without an EXEC (for some reason) and thus the SET FMTONLY stuff is colliding with the MySproc attempted execution.

I wonder if this actually works in the REAL application :(

Blast!

However, there are other statements that fail to execute, and they look much more "reasonable"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000