question

enjoySQL avatar image
enjoySQL 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?
sql servertoolsstress-testing
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

enjoySQL avatar image enjoySQL commented ·
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.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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/
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image TimothyAWiseman commented ·
I can't think of any good tools for capacity planning, but SQL Monitor is very useful for performance analysis.
0 Likes 0 ·
sp_lock avatar image sp_lock commented ·
Idera within its SQLdm app has cap management.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
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/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image sp_lock commented ·
+1 for SQLQueryStress... Love this simple but effective tool!
0 Likes 0 ·
RabihSarieddine avatar image
RabihSarieddine answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.