Free Tools for Testing SQL Sever

You Need To Test Your SQL Server!

Its like screaming “TEST THE BREAKS ON YOUR CAR!” If you don’t have the tools or know how it is just good advice you can’t follow. I’ve done a TON of benchmarks over the years using every tool imaginable including ones I’ve written myself. There are several barriers to generating a valid repeatable benchmark in your own environment. Lets take a look at the general criteria that I use when testing OLTP systems and the benchmark tools available.

I’ve Been Spoiled

I’m going to come clean. I really like Quest Software’s Benchmark Factory. They have put years of work into the tool set. It is also one of the few database benchmark tools that runs against most major RDBMS platforms. This alone made it my go to tool for a very long time. Also, since it implements most of the TPC Council benchmark specifications when someone asks how I tested I can hand them reams of information on the benchmark in question. TPC also has a well defined metric for determining not just raw performance but a dollar amount to get that performance. The catch is that Benchmark Factory isn’t an apples to apples with the published TPC results. To assume so would be a horrible mistake on your part. If you have ever read through a full disclosure (I read them to my four year old to put him to bed) you know that it would be very difficult for your organization to implement the exact same test on the exact same software without spending some major bucks. I have worked a few projects that were willing to spend the money and bring in a team to implement the testing protocols. If you are talking a project that is going to run say ten million your boss may be willing to spend 100,000 of that to completely test the system before it goes into production. In most cases though, this isn’t the case. Even spending the money on Benchmark Factory may be outside of your budget. So, we turn to free or opensource solutions sometimes we just write our own.

Other Options

There are several other options for testing database servers. I’ve use a few over the years. First, I’m limiting this list to those that run on Windows, they may run on other platforms but that isn’t important to me. Secondly, The test setup has to be repeatable. Lastly, there has to be a way to measure performance. In some cases this may be using perfmon(Windows Performance Monitor) or the tool may capture some meaningful metrics.

Database Hammer SQL Server Resource Kit
http://www.sqlmag.com/content1/topic/put-the-hammer-down/catpath/configuration

Not exactly free or cutting edge but was good a putting a load on SQL Server. Even though this is VERY old due to its simplicity it works pretty much on every version of SQL Server from 2000 on. Again, one of the downsides is capturing metrics. The other down side is finding the dang thing. If you have an MSDN subscription you can still download it or you can head over to your local used book store and hope the CD-Rom that came with the book isn’t toast.

Eye On SQL – Load Test
http://eyeonsqlloadtest.codeplex.com/

A basic tool to put a database under load. There are no predefined tests and it doesn’t gather more than the most basic metrics. It is simple to use and can be a replacement for something like Database Hammer

TPCBench
https://github.com/SQLServerIO/TPCCBench

This is a tool I wrote a while back that implements TPC-C. It is in C# so anyone that is familiar can work on the code and peek inside to see how I implemented my version. The down side is it only implements one test, TPC-C. and TPC-C has several limiting factors to it. The one I never liked was all the data generated was random, all of it. If it was a name it was a random string. This leads to a database that effectively has 100% carnality, and is also a very poor choice for testing database compression. My goal was to implement TPC-E, which overcomes all of these issues but is fairly complex and hard to get right. I fell back to Benchmark Factory and never moved forward with this tool.

Hammerora
http://hammerora.sourceforge.net/

This is a pretty simple to use tool an has a easy to understand GUI. It supports two benchmarks a TPC-C style OLTP benchmark and a TPC-H style OLTP benchmark. That is also one of the down sides, it only supports two benchmarks and both are a bit long in the tooth. It also supports various flavors of SQL Server, Oracle, MySQL and Postgres. This is a nice little bonus if you are testing like hardware and OS and want to vary the RDBMS. The last bonus for me personally is it is opensource. This means I can look at the code and see how each test is implemented.

DVD Store Database Benchmark
http://en.community.dell.com/techcenter/extras/w/wiki/dvd-store.aspx

This is a tool I’m currently working on to bridge the gap between TPC-C and TPC-E. It was written in house by Dell and now is developed and maintained by Dell and VMWare. It is more complex than TPC-C but not as complex as TPC-E. It is opensource which was a huge plus for me and allowed me to fork it over at https://github.com/SQLServerIO/DVD-Store-Database-Benchmark and tweak on it to meet my needs. I’ll have a full blog post on this soon explaining the benchmark in detail and what changes I have made to it. It is a staple for testing databases on ESX. It has a standard way for gathering metrics. The load generator is actually in C#. You can also do a full stack test since it will work against a website that is also included in the benchmark. The down side again, is it only implements one benchmark.

Description of the Replay Markup Language (RML) Utilities for SQL Server
http://support.microsoft.com/kb/944837

This is a tool set that was initially developed in house at Microsoft to test customer scenarios. This is different than the other tools since it is a replay tool. But you can capture metrics and vary your workloads. The big down side is it isn’t easy to use. Also, if you are testing new hardware and don’t have a trace to replay from production you are back to zero.

Apache JMeter
http://jmeter.apache.org/

Not a database stress tool in the strictest since of the word but you can use it that way. The upside for JMeter is it will test your whole stack, web servers, middle tiers and database servers. You can use it to drive pretty much anything. Since it is written if Java it runs on Windows. Since there is a JDBC driver for SQL Server you can use it directly to test your database as well. The down sides are also huge. It is written in Java for starters. It is also using the JDBC stack and that can be a limiting factor. It is a significant undertaking to setup as well. I’ve included some links to people that have set it up to test databases and a ASP.NET web site.

http://ilkinbalkanay.blogspot.com/2010/03/load-testing-relational-databases-with.html
http://blog.technicallyworks.com/2009/06/load-testing-aspnet-sites-with-jmeter.html
http://blog.technicallyworks.com/2009/06/load-testing-aspnet-with-jmeter.html

Now What?

Well, that is easy, start reading, building and testing. I personally use something like Eye On SQL or Hammerora to do an initial smoke test or break in. Then move over to the more complicated benchmark tools. I have pretty much settled on the DVD Store benchmark for now as my OLTP testing tool. Now I need to find something more up to date than the TPC-H test everyone else has implemented so I can round out with a solid OLAP benchmarking tool. Maybe I’ll write something again!

Speaking at PASS Summit 2012

It’s Not A Repeat

Speaking at the PASS Summit last year was one of the highlights of my career. I had a single regular session initially and picked up an additional session due to a drop in the schedule. Both talks were fun and I got some solid feedback.

The Boy Did Good

I won’t say great, there were some awesome sessions last year. I did do well enough to get an invite to submit for all the “invite only sessions”. I was stunned. I don’t have any material put together for a half day or a full day session yet and the window to submit sessions was a lot smaller this year. But I do have three new sessions and all of them could easily be extended from 75 minutes to 90 minutes. So, I submitted for both regular sessions and spotlight sessions and got one of both! WOO HOO!

The Lineup

I’ll be covering two topics near and dear to my heart.

How I Learned to Stop Worrying and Love My SAN [DBA-213-S]
Session Category: Spotlight Session (90 minutes)
Session Track: Enterprise Database Administration & Deployment

SANs and NASs have their challenges, but they also open up a whole new set of tools for disaster recovery and high availability. In this session, we’ll cover several different technologies that can make up a Storage Area Network. From Fibre Channel to iSCSI, there are similar technologies that every vendor implements. We’ll talk about the basics that apply to most SANs and strategies for setting up your storage. We’ll also cover SAN pitfalls as well as SQL Server-specific configuration optimizations that you can discuss with your storage teams. Don’t miss your chance to ask specific questions about your SAN problems.

I’ve built a career working with SAN and System Administrators. The goal of this session is to get you and your SAN Administrator speaking the same language, and to give you tools that BOTH of you can use to measure the health and performance of your IO system.

Integrating Solid State Storage with SQL Server [DBA-209]
Session Category: Regular Session (75 minutes)
Session Track: Enterprise Database Administration & Deployment

As solid state becomes more mainstream, there is a huge potential for performance gains in your environment. In this session, we will cover the basics of solid state storage, then look at specific designs and implementations of solid state storage from various vendors. Finally, we will look at different strategies for integrating solid state drives (SSDs) in your environment, both in new deployments and upgrades of existing systems. We will even talk about when you might want to skip SSDs and stay with traditional disk drives.

I’ve spoken quite a bit on solid state storage fundamentals this time around I’ll be tackling how people like myself and vendors are starting to mix SSD’s into the storage environment. Where it makes sense and where it can be a huge and costly mistake.

Finally

I hope to see you at the Summit again this year! Always feel free to come say hi and chat a bit. Networking is as important as the sessions and you will build friendships that last a lifetime.

News Flash! PASS Board Makes Unpopular Decision!

Meh.

(n.) A multi-purpose response, primarily used to imply a degree of indifference. Tone of voice and circumstance often implies a meaning. Can be used when you don’t want to answer an awkward or embarrassing question, or if you just plain have nothing else to say, and you want the other person to interpret the “meh” however he/she chooses. As in: Q: “What do you think of my new dress?” A: “Meh.” or Q: “What do you want to do tonight?” A: “Meh.”

Source: unwords.com

You heard me. I think this whole situation is getting way out of hand. People are making personal attacks, making false statements on the behalf of others and generally being pin heads about the whole thing.

It’s Not The Process, It’s The People

The amount of personal attacks going on is just crazy. Andy wants to change the process, I’m fine with that. You want more transparency, I’m down. What I’m really upset about is community leaders making subtle and not so subtle attacks on the entire board. If you have a beef with a particular board member then call them out. If you have a problem with the WHOLE BOARD maybe, just maybe, its you.

JJ Abrams Runs The Board

Yep, I’m Lost(tm) too. I love a conspiracy theory, I don’t think there is an invisible monster eating people at PASS HQ though. I believe the majority of the board are trying very hard to make the best possible choices for PASS and it’s future. I don’t agree with every choice made, and I despise politics in general, but I don’t think there are 12 odd people colluding to keep out the good guys and run PASS into the ground. Were James and Kendal the last two pieces in a much larger puzzle to control the fate of PASS and SQL Server? We are about to find out….

I’m Gonna Let You Finish, But Sri Was The Best Candidate For The Board!

For a guy that said he was done with PASS politics Brent has a lot to say. I get it, you don’t like the way the bylaws are written. Run for the board and get them changed. You know and I know you would win by a landslide. You also clearly understand that the bylaws are written and approved by the board. You know who I feel bad for? It’s not Sri. He is an awesome guy, this won’t stop him at all. I fully expect him to run again. It isn’t James Rowland-Jones, he has had great success with SQLBits and will make a solid board member with PASS. It is squarely Kendal Van Dyke. Also an awesome community leader and will make a great board member. The problem is he will always have to work harder and have this stigma of being second in the votes but chosen by the board to serve over the next candidate. Who wants that following them around? Would we be having this raging hemorrhoid of a debate if Sri was picked up and Kendal was passed over for James? Kendal was gracious in defeat and would have made a mistake if he had turned down this appointment.

You aren’t going far enough

Andy Warren started the petition. I don’t thing it goes far enough. We need to strip the PASS board of the ability to appoint anyone to the board at all. If it isn’t voted on by the membership there shouldn’t be an appointment to the board PERIOD. I’m serious. All our issues stem from the fact that a few people, that we have voted onto the board, have the ability to appoint more people without the consent of the governed. While we are at it, get rid of the board, all of them. We live in an age where people can vote on topics in real time, a pure democracy, where the governors are the governed. As long as we have a representative form of democracy governing PASS there will always be a group of people to lash out at and a group of people happy to do so.

Fin.

At the end of the day people are ignoring the fact this is a one year appointment. Both James and Kendal will have to face another election if they want to stay on the board for a second full term. PASS has been making changes to the bylaws, not at the speed I would like but they are changing. No matter what, you can’t make everyone happy.

2011 What A Great Year

What A Diffrince A Year Makes

In January I posted up my new years list basically, stuff I wanted to learn in the last twelve months. Lets take a quick look at last years goals.

SQL Server Stuff

1. Partitioning
Win
Done, and pretty early on. I did a lot of reading and did some partitioning setups in my home lab and feel much better about partitioning.

2. Change Data Capture
Draw
This was a business need. At my old company we were using triggers everywhere and CDC seemed like a good fit for most of that. I did some reading and test setups but didn’t get much farther than that. Not a 100% win but I did learn stuff.

3. Replication
Win
Ongoing, since I’ve changed jobs replication moved up on the list as CDC fell down the list. I’ve geared back up and feel like I am back on solid ground.

4. Analysis Services Administration
Fail
Again, casualty of moving jobs. I have ZERO interaction with Analysis Services and as such, didn’t do much on this front.

.Net Programming Stuff

1.LINQ
Draw
I did do some stuff with the more general LINQ to objects and now that I have moved jobs I actually have more exposure to LINQ so I’ll get to learn more in the new year.

2.Parallel Programming in .Net
Draw
Didn’t do much with PLINQ but did work a bit with the new async framework stuff. My biggest complement is it isn’t horrible. I don’t think it is as slick as say node.js but it is light years ahead of the early 2.0 days and threading.

3.Entity Framework
FAIL
Just couldn’t do it. Partly due to the job change and partly due to complete lack of caring. I just can’t get jazzed up about it. I’ve worked with a lot of ORM’s and they all have strengths and weaknesses.

90 Degrees From Center

1. Get Better With Python
Win
I did a few little things in Python on a few different platforms like the Google App Engine. I mostly use it at home. There is a large user base and it does make lots of programming tasks much easier so I’ll hang on to Python for a while longer.

2. Pick Up Lisp again
EPIC FAIL
I spent about two days banging on Lisp again and realized why I don’t use Lisp today. There just isn’t enough support for it in the wild. There are lots of flavors and variations on Lisp but to gain any real proficiency you really need to focus on one dialect and master it. I just couldn’t find one and settle. Eventually, that time popped over to Python.

Changes, Changes and Changes

This seems to be a constant in my life. Admittedly most of that is self inflicted but I wasn’t expecting the amount of stuff to happen to me personally or professionally this year.

Lets make the short list:
New House
New Job
Spoke at SQLRally
Pulled off SQLSaturday #97
MVP
Spoke at PASS Summit

yep, crazy year but a good one for sure.

Thank You

I don’t say that enough. The community has been great to me this year and I can’t wait to see what next year holds!

See you next year!

SQLSaturday 97 Speaker Recap

Great Content + Great Speakers = Awesome Event

All the evaluations have been tallied, its official SQLSaturday #97 was a hit! As a speaker I always like to know how well I did. As a competitor, I like to know how well I stacked up overall. SQLSaturday #97 had some heavy hitters with Conor Cunningham, Joe Celko and the Midnightdba’s in the house. I won’t release the full rankings to the public but I will share some metrics with you. If a speaker had two sessions they were not aggregated together, each session stands on its own.

The Stats

28 speakers signed up to speak and we had zero no shows. Out of those 9 were first time speakers at a SQLSaturday.

Top 3 speakers with more than ten evals ordered by Rating, Expectation and number of evals.

Jennifer McCown Afternoon
Ryan Adams
Jim Murphy

Top 3 speakers ordered by rating.

Jennifer McCown Afternoon
Ryan Adams
Jim Murphy

Top 3 speakers ordered expectation.

Jennifer McCown Afternoon
Jim Murphy
Conor Cunningham

Top 3 speakers ordered by number of evals.

Conor Cunningham
David Stein
Sean McCown

Average ratings For Whole Event

Rating	Expectation	Number Evals
4.42	2.46	22.60

Wow, I don’t know what else to say. It was a great event with awesome speakers. I can’t wait to see what next year looks like!