Voice of the DBA

Author: way0utwest

  • The Gadget Itch

    Many of the people that work in technology are interested in gadgets and hardware as much as software. Not all of us, but many of us have had side projects where we’ve melded hardware and software together. For those of us that are older, and started working with computers decades ago, that might have been the only way that we could get a computer system to work.

    I’m glad that I still find people interested in hardware. My son recently built his own gaming computer from parts, and I’m hoping to distract him from some games and get him to help me build some robots with a Raspberry Pi computer and BrickPi add-on board. However it’s not just kids as I’ve seen a number of people at Red Gate working with hardware to build a variety of interesting projects.

    This week I’m wondering if any of you are interested in creating your own devices. If you had the chance to build a device or gadget for work, what would it be? A panic button when there’s a problem that you can press and instantly set of Star Trek “red alert” sirens? A status display based on your continuous integration builds? The question this week is:

    What would you build if you had a parts list like this one?

    Whether you’ve started actually assembling things or you’re just dreaming of something, or perhaps even just seen something that’s intrigued you, let us know. I actually think this might be a fun competition of some sort for an event, perhaps a hacking competition across a couple hours at a SQL Saturday or conference.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 1.8MB) 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.

  • 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.