Connect Digest : 2011-04-03

This week's digest is sponsored by two different concepts: "sargability" and "DDL triggers."

Sargability

#653206 : The optimizer should be able to understand the invertability of built-in intrinsics on dates for the purposes of index sargability

Here Simon Sabin (twitter) talks about how the optimizer has a blind eye when using date-related functions against indexed columns – it should still be able to use an index when you have a filter or grouping on YEAR(indexed_datetime_column), for example.

#526431 : Make more functions SARGable

Rob Farley (blog | twitter) raised a broader request to have the engine identify more cases where an index could be used – currently, the use of functions prohibits them, sight unseen. With a little more intelligence, the optimizer could squeeze a lot more performance out of some of these queries.

#539631 : Find Potential Non-Sargable Where Clauses In Proc

I presume chopswil wants these to show up more obviously in a query plan. I agree that it would be great if the default showplan XML came with reasons why a scan was chosen over a seek.

DDL Triggers

#299328 : INSTEAD OF DDL Triggers and DMF

Greg Low (blog | twitter) entered this issue regarding a case that I've seen too, where an expensive DDL operation has been rolled back by Policy-Based Management – effectively doing the work twice instead of zero times.

#243986 : Add "instead of" DDL triggers

This more generic request from Adam Machanic (blog | twitter) was referenced by Greg in the above item, but is marked as postponed, and has been categorized as such since 2006 (before SQL Server 2005 Service Pack 2 was released – the vehicle they said was too far along to include this feature). We've plenty of vehicles since then, and I hope they will add this feature soon.
 

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, speak frequently at conferences, and write at SQLPerformance and MSSQLTips. In real life I am a father of two, an architect at Wayfair, and my pronouns are he/him.

3 Responses

  1. Aaron Bertrand says:

    I am not sure what is going on, but a lot of Connect items seem to be disappearing from the site, returning an error message or not. The title has nothing to do with it… that is just extra information for SEO purposes.
    Here is a new Connect item, that is visible, that shows the problem is widespread:
    http://web.archive.org/web/*/http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/details/656763/all-my-feedbacks-are-gone-feedback-site-whats-happening-with-the-connect-site

  2. IL says:

    Hm-m, all likns lead to Page Not Found
    "The content that you requested cannot be found or you do not have permission to view it." Does it mean what it means?
    Aaron, your previous links were reachable without any hassle.
    By the way, the link for #539631 contains the title
    http://web.archive.org/web/*/http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/details/539631/find-potential-non-sargable-where-clauses-in-proc
    All others are not.