Connect Digest : 2010-01-22

AaronBertrand — Fri, 22 Jan 2010 19:47:00 GMT

Give us easier to read execution plans

Michelle Ufford (@SQLFool) recently asked for help pinpointing the most expensive node(s) in a complicated execution plan. Mladen Prajdic (@MladenPrajdic) has a useful workaround; he coded up a quick query to parse the showplan XML and order results by cost descending. The Connect item that would make this workaround unnecessary was filed by "Ewan1":

#477390 : Rank cost of graphical execution plan components in SSMS

Give us more SARG intelligence in the optimizer

As Adam Machanic (@AdamMachanic) lays out in a recent blog post, the optimizer is getting better at this with each new version, but in this Connect item, Rob Farley (@Rob_Farley) correctly points out that there are a lot of places where non-sargable arguments could obviously (and automatically) be made sargable.

#526431 : Make more functions SARGable

Give us more Agent smarts : system procedures to avoid ad hoc queries

Dave Ballantyne (@DaveBally) blogged that SQL Agent is still very stubborn about submitting ad hoc queries all day long when, in reality, there should be a whole bunch of procedures in msdb to satisfy these queries (and prevent unnecessary ad hoc cache bloat) He also filed this Connect item:

#526485 : dm_exec_cached_plans Bloat

Give us more power to work with data in the results pane

After some back-and-forth with Buck Woody (@BuckWoody), I filed this suggestion, which asks for the ability to embed Excel in the results pane of Management Studio. This would allow us much more immediate analysis of results, with fewer steps.

#524769 : SSMS : Ability to embed Excel in results pane

Give us better error messages

Since I work with multiple schemas, I am coming across more and more error messages from the engine that were obviously written at a time when everything was owned by dbo. As the product gets more complex and the schema model takes hold, this will need to be corrected.

#525308 : All error messages that include object name need to also include schema name

Give us more predictable function performance

Simon Sabin (@Simon_Sabin) and Andrew Novick had similar suggestions: to finally fix the abysmal performance of UDFs that has been present since they were first introduced to the product.

#524983 : User defined function performance is unacceptable
#273443 : The Scalar Expression function would speed performance while keeping the benefits of functions

Give us more complete metadata about stored procedures

Greg Low (@GregLow) came up with an interesting suggestion about providing better metadata about stored procedures (a "contract"). I don't agree with all of Greg's ideas (see recent threads here and here), but in general I agree that the metadata for stored procedure interfaces could be exposed better - providing benefits all around, but especially in environments where a consistent standard is enforced (e.g. resultset shape is not only deterministic but also remains consistent even when inputs do change).

#525653 : Stored procedures should expose detailed contracts

Search results matching tags 'SSMS', 'functions', 'execution plan', and 'plan cache'

Connect Digest : 2010-01-22