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….

---

Comments:

Anonymous

December 10, 2015 - 14:40

I have a problem with this

I have a problem with this tool. Every time I get such message “Input string was not in a correct format”.
Please look at the output:
ToggleQueryCosts error: Object reference not set to an instance of an object.
ToggleQueryCosts error: Input string was not in a correct format.
ToggleQueryCosts error: Input string was not in a correct format.
ToggleQueryCosts error: Input string was not in a correct format.
ToggleQueryCosts error: Could not find stored procedure ‘dbo.procIMSNodeGetMediaFileListHistor2’.
Deploying [dbo].[procIMSNodeGetMediaFileListHistor2]
Deploying [dbo].[procIMSNodeGetMediaFileListHistor2]…Done
ToggleQueryCosts error: Input string was not in a correct format.
ToggleQueryCosts error: Input string was not in a correct format.

Here is the procedure:

CREATE PROCEDURE dbo.procIMSNodeGetMediaFileListHistor2
AS
SELECT m.Id AS MediaFileListId
, m.NAME
, mv.Id AS MediaFileListVersionId
, mv.Description
, mv.VersionNo
, mv.DisplayTypeCodes
, mv.CreatedBy
, mv.CreatedAt
, mv.ModifiedBy
, mv.ModifiedAt
, mv.ReleasedBy
, mv.ReleasedAt
, mv.Guid
FROM dbo.tblMediaFileList AS m
INNER JOIN dbo.tblMediaFileListVersion AS mv ON (m.Id = mv.MediaFileListId)
ORDER BY mv.VersionNo DESC

Ed Elliott

December 10, 2015 - 14:56

Hmm I have fixed an issue

Hmm I have fixed an issue recently that might have caused this - you can try the latest build from:

https://github.com/GoEddie/SSDT-DevPack/blob/NonSargableIsNulls/release/…

I haven’t documented what is this build yet as I am still testing the two new features in it (re-write non sargable isnull statements and something i’,m calling t-sql clippy) so I would stay away from those two menu items but everything else is fine to use and includes the fix that I think you need.

ed

Anonymous

December 11, 2015 - 07:47

Hey!

Hey!
I would like to try this. I installed it. The menu in VS shows up and I can connect to a sql instance using your dialog. That’s all that ever happens. I searched for the config.xml file you referenced to. There is no folder SSDTDevPack on my drive. Same behaviour with VS2013 & VS2015.
Any idea what went wrong?
Regards, Markus

Ed Elliott

December 11, 2015 - 13:25

Hi Markus - does the code you

Hi Markus - does the code you are trying to get costs for exist in the database you connected to? All it does is a showplan on the procedure (or tvf) name so if they code isn’t dpeloyed it won’t be able to get it.