Apr 01 2014

SQL Server 2014 New Defaults

CelebratingToday, April 1st, 2014, marks the release of SQL Server 2014. There are tons and tons of great new methods and functions and processes within the product. We’re all going to be learning about them for quite a while to come. One of the most exciting though is one of the changes to the defaults. In the past there’s been a lot of debate around how best to configure your databases. What cost threshold should be set for parallelism, the max degree of parallelism, memory settings, auto growth, and all sorts of other settings affect how your databases work. But, Microsoft has finally done something smart. They’ve bowed to the pressure of hundreds and hundreds of DBAs, Database Developers and Developers around the world. They’ve finally done the one thing that will improve everyone’s code once and for all. Expect to see massive performance improvements in SQL Server 2014 thanks to this one default change.

What have they done you ask? What miracle is this that is going to result in both better code and better performance? Simple, by default, all connections to the database are now using the transaction isolation level of READ_UNCOMMITTED. In a single stroke, we no longer are forced to put with WITH NOLOCK on every single table reference in every single query. All the pain and suffering caused by blocks from locking has been removed from the product. We can look forward to a much cleaner code base and better query performance. Thanks Microsoft.

Please, note the date carefully.

15 Comments

  • By Steve Hood, April 1, 2014 @ 7:33 am

    It’s too early in the morning for this! I had to read it three times to realize you weren’t saying read-committed snapshot isolation!

    Grant Fritchey, the Cruel DBA, got me first this year.

  • By Grant Fritchey, April 1, 2014 @ 7:35 am

    #SQLWINNING

  • By Rick Sellers, April 1, 2014 @ 8:31 am

    Although I caught it the first time, I agree with Steve. Since I was thinking about the date already, I figured there was something wrong because in there somewhere. Good one!

  • By Kevin Hazzard, April 1, 2014 @ 8:44 am

    Mmmm. Smells like some dirty and phantom data cookin’ in the pot. Consulting dollars for me to fix though so that’s good.

  • By Jeff Moden, April 1, 2014 @ 9:22 am

    Ok, Grant… you owe me a new monitor, keyboard, and some fresh underpants. I blew coffee out of every facial orfice laughing and farted really hard after I read this. “It Depends” suddenly has a whole new meaning for this ol’ man. Too funny! ;-)

    The odd part is that you actually might not be that far off especially where Hekaton is concerned. It’ll be interesting to see how many people lose or overwrite data by mistake with the rather optimistic locking done there.

  • By Kenneth Fisher, April 1, 2014 @ 10:00 am

    Best one I’ve read so far. You had me until the very end.

  • By Greg Edwards, April 1, 2014 @ 10:11 am

    Revolutionary!!!
    Can you explain the Blow to the Head tag?
    I am having a hard time relating that to the article…..
    I thought I felt an aftershock from California, but I can see maybe it was actually centered to my East.

  • By Grant Fritchey, April 1, 2014 @ 10:44 am

    Thanks for all the feedback.

    Greg, the “blow to the head” tag means that if I really thought that changing the default behavior of the databases to allow for dirty reads was a good idea, it was because I had taken a blow to the head.

  • By Grant Fritchey, April 1, 2014 @ 10:45 am

    Kevin,

    If they really did this, consultants would make a fortune.

  • By Kenny Wymore, April 1, 2014 @ 2:20 pm

    Good one Grant! And I thought for a second that you would be serious today. Thankfully this is a joke!

  • By Greg Edwards, April 1, 2014 @ 3:03 pm

    And I was thinking ‘what a novel feature, why didn’t they do this years ago’ ;>)

    Thanks for the good humor to set the starting point for the day!

  • By Grant Fritchey, April 1, 2014 @ 5:23 pm

    Kenny, Yeah, let’s hope it is one.

    Greg, glad to help out.

  • By Dennis Parks, April 5, 2014 @ 11:12 am

    although I’ve just read this on the 4th, I still thought it must have been a joke. But a very good one! Nice!

Other Links to this Post

  1. (SFTW) SQL Server Links 04/04/14 • John Sansom — April 4, 2014 @ 7:42 am

  2. weight loss plans — July 4, 2014 @ 4:56 pm

RSS feed for comments on this post. TrackBack URI

Leave a comment