June 11, 2008 at 9:53 am
Hi All
We are upgrading our servers to SQL 2005. We've all read the articles from MS claiming how much better SQL 2005 is but we would like to see evidence that upgrading will see a performance improvement for the environment in which we operate.
What we want to do is recreate our live servers in a test environment and then to see if our internal web app performs better with SQL 2000 or SQL 2005 as the RDBMS.
If anybody has any suggestions for strategies to use I would be very interested to 'hear' them.
The stategy I have in mind is as follows ;
i) use SQL Profiler to record activity in live environment
ii) Install SQL 2000 & SQL 2005 on test server and restore test databases to both servers
iii) Shut down SQL 2005 & replay transactions from i) against SQL 2000 on test server
iv) use Performance Monitor with appropriate counters to capture resource usage with above transactions
v) Switch app to write to 2005 instance. Repeat iii) & iv) for SQL 2005 (shutting down SQL 2000 instance of course)
vi) Compare Perf Mon results.
We also want to be sure that queries actually execute quicker so we can run a few common queries in Query Analyser but want to know how best to deal with compiled execution plans. i.e. do we record the results of the query only after running it 2-3 times first or can we 'age' the execution plans etc.
Thanks in advance.
June 12, 2008 at 7:40 am
To do the kind of test you are trying to do, you need all other variables equal. So, if you really want to do this, I would suggest you virtualize and create a copy of your production system with SQL 2000 and a copy with SQL 2005 on IDENTICAL hardware - just to get started.
I think you are going to find that the type of test you are trying to do is actually useless in the real world. To start, I would completely ignore the version difference comparison and get the best hardware you can afford for the new server.
- At this point, you should be going with 64bit and buying as much memory as your project can afford - it has gotten pretty inexpensive. Face it, it's a 64bit world now.
- Get everything moved over, upgrade anything that needs to be upgraded to get it out of compatibility mode.
- Check your calendar and make a choice about SQL 2008 (the upgrade is the same at it is to 2005).
- Then, tune the server and your procedures / queries as as much as time allows.
It will be faster when you are done.
June 13, 2008 at 5:42 am
Thanks for response.
Why do you think the test is useless ? We want to prove that SQL 2005 really does perform better with the same hardware and I cant think of any other way of doing it. You suggest virtualisation, what metrics and method would you use to then compare the two (as opposed to what I have outlined)?
I have read many posts from DBA's that they have had nothing but problems with 64 bit architecture. I guess your experience is different.
I usually only upgrade when the software has been around for at least 2 years so that most of the niggles are fixed. Thus I have not even considered 2008 upgrade. If the upgrade from
2000 ---> 2008 is the same then I'm guessing it wont be too difficult to go from 2005 ---> 2008 should we want to in the future.
June 13, 2008 at 5:54 am
The comparison is relatively usless (in my opinion) because unless you intend to do an in-place upgrade and retain your original hardware (which I would not recommend) you will not be comparing the performance of the current configuration to the new one. But, if you really compare the old configuration to the new one and the hardware is newer on the SQL 2005 server, you have tipped the scale anyway. If you want comparitive numbers regarding performance of just the software, take the published papers from Microsoft at face value and skip the testing you were going to do.
I am not sure what issues you have seen with 64 bit SQL, but it has worked fine for me. There were some minor headaches with SSIS, but the database engine itself is much better with more memory available - and you only get that by going to 64 bit.
June 16, 2008 at 9:25 am
Thats exactly it. The hardware will remain the same and we will be performing an in-place upgrade, although for the comparison there will be 2 sql instances (2000 & 2005). Whether or not we upgrade the h/w is a separate issue.
What I was really after was an opinion as to whether or not the method itself is credible as I cant think of any other way to do perform the comparison.
Although MS have done the necessary performance analyses, we would like to see evidence that performance will be enhanced in our environment. I can then present these findings to senior management with MS's own benchmark tests to supplement our own results.
Most of the posts about 64 bit SQL that I have read are regarding problems with ;
i) database mail
ii) Lack of drivers for linked servers
iii) General compatability issues.
Although I must admit the problems dont seem as bad as when I looked 4 months ago.
June 16, 2008 at 11:12 am
Your two-instance testing plan is not going to work unless you are able to stop the alternate instance during your tests. The two instances are going to compete for resources and skew your results in a way that will be really hard to reconcile. In addition, unless you can actually run each instance over some time, you will not see some of the benefits of the caching model 2005 uses.
Since you are using the same hardware, 64 bit is really out - especially if you intend to do any side-by-side comparison on the hardware. You are correct that there are some complications with a few linked server drivers (although I think most are worked out). I have not had any issues with database mail. I think your concerns about 64bit have been minimized or eliminated over time. Lots of companies are running it without issues - inclusng at least one very large network television company.
Rather than testing the entire application, you may want to simplify things a bit and gather up the most common queries and processes. See if these will automatically work better in SQL 2005 and if they can be optimized better in SQL 2005. Show these items as examples of the benefits to an upgrade rather than trying to benchmark the entire application. It will allow you to actually eliminate some variables. Also, pick some problem processes and see if they can be improved. You can then include in this information, the MS information regarding scalability and reliability.
June 16, 2008 at 1:45 pm
Yes, the plan is to shut down one instance as the other is scrutinised (step iii) in first post). We will replay a day's transactions as well as run some common queries. If we can see that 2005 performs better without the caching model coming into full effect, then that would be a positive sign as it should perform even better once we upgrade.
64 bit is something to consider for the future as we have 3.5 GB RAM and we dont really have any memory bottlenecks. So we still have an extra 0.5 GB to play with.
Thanks for your comments.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply