Connect Digest : 2010-01-22
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.
Give us more Agent smarts : system procedures to avoid ad hoc queries
Dave Ballantyne (@DaveBally) mentioned 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:
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.
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