asked
SQL Server stress testing tools for dbas
Looking for a tool/product DBAs must have. Any recommendation for 3rd party product for capacity planning and performance analysis?
Thanks but the main reason we are looking for a 3rd party tool is to do workload analysis of a new product before it goes to production. We have a development environment but we don’t have many users on that environment to mimic production. So, we are looking for product that you can request it how the app is going to function with these many users and it will give you a report on how it will handle it. Hope this makes sense.
That's a pretty loaded question. Lots of people use different tools for this type of work. I work for one of the tool vendors, R[ed Gate Software][1]. We make some absolute, must-have, applications. But so do many other vendors. For what you're looking for, I'd recommend our tool, [SQL Monitor][2]. It monitors your servers, including showing you all sorts of performance metrics, showing you what runs slow, when. You can see this in action against the servers that manage this web site as well as others at [
monitor.red-gate.com][3]. We also publish articles about using the tool over at SQL Server Central. [1]:
http://www.red-gate.com/ [2]:
http://www.red-gate.com/products/dba/sql-monitor/ [3]:
http://monitor.red-gate.com/
I can't think of any good tools for capacity planning, but SQL Monitor is very useful for performance analysis.
To stress test a system to determine if it can support a given load, I'd suggest using SQL SErver 2012's new tool, [Distributed Replay][1]. You can capture or create a load and then dial it up, steps at a time to place greater and greater stress on a system. You'll still need to have a monitoring tool to watch to see where you develop bottlenecks, but you can use Perfmormance Monitor and Extended Events to capture all that information. I detail how to do this in my book, [SQL Server 2012 Performance Tuning][2]. [1]:
http://msdn.microsoft.com/en-us/library/ff878183.aspx [2]:
http://www.amazon.com/Server-2012-Query-Performance-Tuning/dp/1430242035/ref=sr_1_1?ie=UTF8&qid=1346259142&sr=8-1&keywords=fritchey
My favourites tools for stress testing are SQLIO, HammerOra and SQL Query Stress (
http://sqlblog.com/blogs/adam_machanic/archive/2006/10/21/announcing-sqlquerystress-a-simple-query-load-tool.aspx) You can get a pretty good list of these and other tools from
http://sqlserverio.com/2012/08/16/free-tools-for-testing-sql-sever/
I use the
PIEngine.Net tool. You can create stress test scenarios from SQL Server trace files, or you can create a scenario from scratch. Then once the scenario is finished, you can run it manually from the UI or automatically from the command line tool. Stress test results are written as text files. In addition to that, you can capture SQL Server requests at runtime an include the captured requests in your scenario. Stress test configuration include the number of users to simulate, the number of scenario replays and the delay between requests. You can automate stress tests from multiple client machines. Check it out:
http://www.piengine.net
12 People are following this question.
