Voice of the DBA

Author: way0utwest

  • Continuous Delivery Visualization

    A good one, one of the better diagrams I’ve seen.

    CD

    From http://markosrendell.wordpress.com/2014/03/03/practical-benefits-of-continuous-delivery/

    Now if we can get tooling and processes to make this work reliably for databases.

  • Speaking at Connections

    I just got my acceptance to the DevConnections, SQLConnections in September for two sessions:

    • Continuous Integration for Databases
    • Maintaining High Performance When Using Encryption

    I enjoy the Connections events, usually finding time to go check out an ASP.NET or developer session of some sort.

    Now’s the time to register and save. There will be some great pre-cons as well, including one from a scary individual that I know well.

  • The Cardinality Estimator in SQL Server 2014 – Going Forward and Backward

    I saw a talk from Joe Sack (b | t) on the cardinality estimator (CE) in SQL Server 2014 and found it very interesting. To be fair, some of the "how it works" isn’t something I care about much, but I did like Joe sharing some places in which you might find problems with your queries and how the cardinality estimator might affect you. The talk is worth seeing if you get the chance.

    However one of the really interesting things, and an item I appreciate Microsoft building, is a switch to turn off the new CE. Actually, it’s not turned off, but you can set it to pre-SQL Server 2014 behavior (essentially 2005-2012) or to SQL Server 2014 behavior. What’s even better is that you can set this in a number of ways.

    Setting Database Behavior for all Queries

    Turning on the new CE is as simple as setting the compatibility level to 120. This will turn on the new CE for your queries in this database.

    The flip side is setting your compatibility level to something below 120 (110, 100, etc.) and your queries will use the old CE behavior in the query optimizer.

    Setting Behavior for Queries

    You can also specifically test queries with either the new or old CE. The QUERYTRACEON option can be used with these two flags.

    • 9481 – Uses the 2012 (pre-2014) CE with queries. This is used when the database is in SQL Server 2014 (compatibility mode 120) mode.
    • 2312 – Uses the new 2014 CE when the database, or defaults, are set to use the 2012 CE.

    This is documented in KB 2801413 from Microsoft.

    Setting the Server Level

    There is a trace flag that you can use at Server startup that globally sets the CE behavior. Set 9481 at startup and your SQL Server 2014 databases will use the old CE by default. Joe documented this on his blog.

    Usage

    I think this is great because if you are concerned about workloads being effected by the CE changes, then you can set the old CE as the default and test on your real production server by executing specific queries with the new CE and the query plan.

    Alternatively, if you upgrade and find problems, you can duplicate the old CE by using a query hint and see if the query performs better.

    I’d like to see this upgrade/downgrade granularity in more features that can potentially affect performance and I would say this is a fantastic architectural win by the SQL Server development team.

  • The Subtle Push to the Cloud

    With SQL Server 2014 released, there’s the temptation to upgrade for many DBAs. However the licensing costs and debatable improvements in the product will temper the DBA’s enthusiasm with the reality of the ROI seen by management. While reading about the licensing changes, I also saw this note from Tom LaRock, where he wrote about the features most of us aren’t using. It made me think about upgrades, and perhaps the strategy Microsoft is employing.

    As Tom mentioned, the features not being used are Enterprise Edition features. This prevents many of us from upgrading to use them because Enterprise Edition is so expensive. Actually, even Standard Edition is expansive these days, given the per-core licensing, and I suspect lots of companies with SQL Server 2008, 2005, even 2000 are debating whether or not the upgrade is really worth the cost.

    The piece also mentions that Azure is much less expensive in the short term, and you should consider it. Of course at some point the money you pay will be more than buying a license and a server, but that assumes you run them for the same length of time. You’ll likely upgrade at some point, and if you upgrade both hardware and software, the cloud starts to look more attractive.

    It’s not simple to move into the cloud, nor is it appropriate for some applications, but it does seem that Microsoft would prefer to have most companies running in Azure, on a single platform they run, they patch, and they support. Many companies might prefer the same thing, though I do worry that we might find the on-premises version of many products becoming second class citizens. Vendors will spend less on development and support if too many customers move to the cloud.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.0MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.