When developing stored procedures in SSDT it is important to know when you have written a query that is potentially slow or has a high IO or CPU cost but there is no real way other than examining the the tables you are querying and understanding the data volumes and indexes whether they could be a problem.

The idea of this new tool is to highlight in either yellow or red (yellow is quite expensive, red is really expensive) any statements that have a high cost. This isn't an actual profiler so it is important to understand what it does and its limitations.

What does it do?

When you enable the query cost for a document (I map it to the keys ctrl+k, ctrl+q) what the tool does is connect to a SQL Server instance and run the stored procedure using "SET SHOWPLAN_XML ON" so it isn't actually executed but the estimated query plan is returned and the cost of each statement checked to see how high it is.

By default high statements must have a cost over 1.0 to be marked as high and anything over 0.2 is marked as a warning - you can override these with this in your "%UsersProfile%\SSDTDevPack\config.xml" :

<Settings>

<Costs>

<Medium>0.02</Medium>

<High>0.1</High>

</Costs>

</Settings>


To turn it off again just toggle the costs off (either map some keyboard short-cut to "Tools.ToggleQueryCosts" or do Tools->SSDT Dev Pack->Toggle Query Costs. To stop constantly going back to run the query against the server, I check the checksum of the file so to get a new set of costs make sure you save the file. If you get any issues there is a menu item "Clear Query Cost Cache" which clears all the cache's and you can start again merrily.

Two things to bear in mind:

• If you don't deploy the code I can't get an estimated plan so if it isn't doing what you expect then make sure you do a publish
• It isn't an actual profiler so if you do something like use cursors the statements may have low individual costs but actually be slow and expensive in real life

What does it look like?

Here is a screen shot, feel the awesome:

Finally, I find it useful so hopefully someone else will as well :). This will probably be the last tool I add to this for now as I want to tidy up the ones that I have already done.

Download it from:

https://github.com/GoEddie/SSDT-DevPack/blob/master/release/SSDTDevPack....