SQL Server Performance Script

Test transactions per second on your server

About 8 years ago, I worked with a group of folks at Bank One as a SQL Server contractor working on a mortgage loan application. The third-party software company who wrote the front-end had a programmer that had a "speed test" TSQL script that he used to check for transactions per second on the server. We ran this to get a basic measurement of how the server was running, and it served as a ball park figure for the relative responsiveness of the server. So, I have used this for years and continue to use it, and wanted to share it with you in this blog.

There are a few disclaimers that I want to point out concerning this script. First off, it is very crude in nature. It measures transactions/second using only inserts, and not even random ones at that, and is very unscientific. If I knew then what I know now about disk performance, I would have made many changes to it, and probably would have written two additional "speed test" scripts; one for reads, and the other for updates, in order to get a more clear picture of what the disk I/O subsystem is capable of doing. Second, it is very rough, poorly written - it isn't even formatted that well - and is pretty generic. Indeed, no one will earn any SQL Server magazine or TPC awards (or any other award for that matter) with this thing, and if you told me that there were better ways to get an indication of this metric I would probably agree with you in earnest.

However, given the above disadvantages, the big things that it has going for it are:

• Longevity - I have used it for several years across many servers in various settings with wide variety of server specifications and configurations
• Consistency - The script has not changed since I began using it back in 2000. Therefore, it has a large track record.

If you like, try it out across many servers, and get baselines for all of your servers. If you experience a slowdown, compare your baseline to a current reading. Also, run it within the context of several databases using a different seed in the WHILE loop. Generally speaking, I have found that a SQL Server on a "standard" SQL Server box that I see most often these days (2-4 processors with 2-12 GB RAM, give or take) will run this thing somewhere between 1,000 and 2,500 transactions per second in a user database, and 5,000-10,000 transactions per second in tempdb. (I'll blog why tempdb is so much faster somewhere down the road). Also, open up perfmon and look at it compared to what the o/s is seeing as far as transactions/second go; you'll be surprised that it is very close, although it is difficult to start and stop it to coincide exactly with performance monitor. I hesitate purporting accuracy as one of the advantages, because I have not compared it to perfmon other than simply eyeballing the graphical interface. Maybe I should look at it closer using some of our tools here at Integrated Services, Inc - SQL Rx - to get an exact measurement of how well it coincides with performance monitor.

So, if you find that your production server is cranking out a hefty 96 transactions/second, you might want to open up perfmon and look at some of the disk counters, such as Avg Disk Sec/Transfer, to verify whether or not a problem exists.

Best regards,
Lee

Speedscript.zip


------------------------

Not perfect, but not too bad either