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.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2008-12-01 : 12:25:16
|
| 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 slowerb) show how much faster their system will beand 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
30421 Posts |
Posted - 2008-12-01 : 12:37:05
|
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" |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2008-12-01 : 12:48:37
|
| 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? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-01 : 13:11:44
|
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" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2008-12-05 : 15:18:17
|
| 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?? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2008-12-05 : 15:25:41
|
| "SET FMTONLY ON dbo.MySproc 'Param1','Param2',null,null,null,null SET FMTONLY OFFthere are three statement here, and breaks are needed between themAh ... 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" |
|
|
|
|
|
|
|
|
sql2000 to sql 2005 Migration Tests