We just got approval to purchase a new database server. My manager is worried that, after we deploy the new server, some users might not perceive the improvement in performance. I've been tasked to come up with some way to measure the improvement. But first, some details on the upgrade:

The old server has dual Intel Xeon 3.40GHz single-core processors and 4 GB of RAM. It's running Windows 2000 Server Standard 32-bit and SQL Server 2000.

The new server will have dual Intel Xeon 2.4GHz quad-core processors and 18 GB of RAM. It will be running Windows 2008 Hyper-V bare-metal. Initially, we'll be virtualizing Windows Server 2003 Premium (until we can upgrade our ERP software to the latest version) and SQL Server 2000 (not enough money in the budget to upgrade SQL Server yet).

Having never done this sort of thing before, my idea was to create a SQL query that hits some of the really large database tables such that the query takes a minute or two to run, and measure how long it takes to execute by setting two date/time variables, one before the big query and one after. I would then store these two date/time values in a table and schedule the job to run a night when activity is minimal and equivalent each day. This should factor out the load variability during the day.

By comparing the elapsed time in our current situation and the elapsed time after the upgrade, that should give an idea of time savings, so that we can say the upgrade resulted in an improvement of x%. Does my plan seem solid, or did I take a wrong turn at Albuquerque? Is there something I should be measuring additionally?

  • The interesting thing is that you mention everything that DOES NOT MATTER. What abot the ONE important thing for most database server - the DISC IO BUDGET? SQL speed, unless you rundata warehouses and OLTP, pretty much depends on the disc speed. Not on CPU and memory. – TomTom Mar 17 '11 at 17:07
  • The new drives are 10k RPM SAS, 2 in a RAID-1 configuration for the operating system and 3 in a RAID-5 configuration for the data. I don't have the specs on the old drives. – Scott Mar 17 '11 at 17:11
  • Pretty crappy. I hope you dont ahve a lot of IO planned there. YOu miss a fast set of discs (2 SSD in a raid) for the LOGS. Log write performance is pretty much THE critical thing. ven so, 5 Velociraptor equivalents are still not really server territory. Plan for expansion here, like geettnig a server that can take 24x2.5" discs in 2 rack units - those exist). – TomTom Mar 17 '11 at 17:17
  • We are indeed doing OLTP. There are essentially three databases: our ERP system, our web-based workflow system, and a third database where our custom solutions do their business (zip locator for our web site, cached data for business intelligence, etc.). – Scott Mar 17 '11 at 17:20
  • 1
    Then it is crappy. Get discs for the log. Either put them on the OS (and mke that more discs ina a RAid 10), or get another 2 ssd in a raid 1 (mirror). SSD have a REALLY low latency, so they give you OLTP speed (again, log write speed is what makes OLTP speed, NOT db write speed, as thedb can be written in the background later, unless you have a LOT and the database safepoints all things out). – TomTom Mar 17 '11 at 17:25

Here are a set of videos on how to use perfmon and create a baseline for sql server which will help you track and compare different counters. You can also benchmark your disks for random and sequential IO using IOmeter. Also check out SQLIO to measure your new storage io performance. Hope this will get you started.

EDIT: I also found this article regarding storage IO benchmarking

  • 1
    In addition there is also profiler - you could capture the duration and use that to show AVG, MAX, MIN duration for statements over a typical working day. You could even take it a step further and try and find common SQL calls and report the AVG, MAX and MIN duration for those statements. – Ian Chamberland Mar 18 '11 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.