Thursday, April 05, 2007
SqlQueryStress - Handy Little SQL Performance Tool
I was googling today for a few query tweaking tools and came across Adam Machanic's SqlQueryStress utility which is FREE and is a nice little tool that can show you information such as how "fast" a query will run 1x, 10x, 100x, specific metrics about page reads, CPU time, and a little more. It works with stored procedures, functions, views, ad-hoc queries - whatever.
It's too small to do a review on and besides, Adam made the software so easy to use that you really don't need much of a tutorial :) I did find a tiny bug when I was playing with it for the first time. If you type a query into the "Query" textbox, then click "Parameter Substitution", and then the "Get Columns" button, you get an error: "ExecuteReader: CommandText property has not been initialized." Obviously I shouldn't have clicked that button since my query had no parameters but it was my first time using it so I was just playing :)
I suggest you download it and play with it while it's free. It's very handy and who knows, Adam may get the bright idea to charge us for using his handy tool in the future! You can download it here. I particularly like the parameter substitution bit where I can take values from TableA and substitute them into queries against TableB. Adam posted a nice walk-through here.
Wishlist:
It's too small to do a review on and besides, Adam made the software so easy to use that you really don't need much of a tutorial :) I did find a tiny bug when I was playing with it for the first time. If you type a query into the "Query" textbox, then click "Parameter Substitution", and then the "Get Columns" button, you get an error: "ExecuteReader: CommandText property has not been initialized." Obviously I shouldn't have clicked that button since my query had no parameters but it was my first time using it so I was just playing :)
I suggest you download it and play with it while it's free. It's very handy and who knows, Adam may get the bright idea to charge us for using his handy tool in the future! You can download it here. I particularly like the parameter substitution bit where I can take values from TableA and substitute them into queries against TableB. Adam posted a nice walk-through here.
Wishlist:
- I wish it would preserve my connection data between sessions
- I wish I could use GO to separate statements but an exception is raised
- I wish I had an option to dump the plan cache and buffer cache before running the "test"
- I tried adding DBCC FREEPROCCACHE, a GO, and then my query/proc but it failed. Once I removed the GO it was fine.
- I wish I could log the exceptions to a file
- I wish I could generate a report/something that would allow me to keep historical results of this information. The format is not important: .xml, .txt, .csv. I just want to be able to test a query, log the results, change something about the table/query/etc, run the query again, and compare results against previous execution.
Labels: sql 2005, sql server, sql server 2005
