Voice of the DBA

Category: Editorial

  • SQL Server 2016 is Just Faster

    I’ve enjoyed the series of posts from Microsoft on the ways in which SQL Server 2016 was improved and is faster. Some of these posts show improvement without any change in your code. Some showcase features that might require change, but will bring about improvements, and after all, wouldn’t you rewrite some code if you knew you could get performance improvements? Most of us would, if the changes are fairly simple. I know some changes are hard, but that’s our job as developers: make systems run better, even if it’s work to change our code.

    Some posts are just fascinating. The one on log stamping, which highlights something I had no idea about. I wouldn’t think that stamping 0xC0 is better than 0x00, but I’m glad someone dives deep into hardware changes like this. I know many of us don’t have more than 8 CPUs running our databases, but we might at some point. Having a better way of ensuring soft NUMA works well automatically is good. Even if you don’t care about how this works from a practical standpoint, it’s an interesting look at how Microsoft is keeping up with hardware advances where a single CPU these days can encapsulate more than 8 cores.

    SQL Server 2016 is not only the best version to date, but it’s also the most tested and evaluated. Apart from all the evaluations and writeups performed on the CTP 2.x’s , CTP 3.x’s, the RC’s, much of the code has also been running in Azure, where metrics have been gathered and evaluated for quite some time. There’s at least one feature I’ve been hearing about for over 3 years, since well before SQL Server 2014 was released, at which time it was running in Azure already. In all that time, there have been lots of tuning and tweaking to ensure that the code is running as efficiently and effectively as possible for this release.

    Note that I’m not saying that the product is without bugs. While some people think there’s no need to wait for SP1 (I agree), I also think you should test thoroughly for your situation. It is entirely possible that there will be some bug that affects your code. And you need to test with actual systems and code. Also, be aware that there are documentation updates being made constantly, as we find that bugs also exist in BOL.

    While SQL Server 2016 is faster than previous releases, there are quite a few new features that do require code changes. However, if you can take advantage of those features, I think it’s well worth the development effort and the upgrade cost. Let me know what you think.

    Steve Jones

    The Voice of the DBA Podcast

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

     

  • What’s Your Area of Expertise?

    I ran across a great quote that made me laugh, but also struck me as being quite true. The quote is from Scott Curie’s post on the PASS Summit Speaker’s Contract and goes like this: “You would laugh at a lawyer who tried to build their own data warehouse, so why would you try to write your own legal agreement?” Having used lawyers for a few contracts, I am well aware that I’m not remotely qualified to putting together a detailed contract. I know if I need a business agreement, I need to either use a lawyer, or make it really simple. “I’ll cut your grass and you’ll give me $25” is probably the last well written contract I wrote.

    We all have strengths and useful skills, things we are really good at performing. We often improve those skills over time and may develop some level of expertise in our field. However, even if we were the best T-SQL coder in the world, or the hands-down, acknowledged world-renowned expert on replication, that wouldn’t mean that we necessarily had any skill in some other area of the SQL Server platform, such as Integration Services. We might know something, we could learn, and certainly be competent quickly, but would a company that had a mission critical, multi-TB import or export of data want us to patch their ETL process today? Probably not.

    Usually when we have a problem with a specific technology, we want someone that knows that technology well to guide us or do the work. The same thing is true in business. If an inventory specialist questions the way some system works, or a financial guru wonders about the calculations in an application, we should defer to them and ensure that we can explain how the code works, double checking the accuracy. After all, specifications could contain bugs as easily as code, and an expert in the end user of software might have a better idea of whether a system is working properly than the developer.

    We should remember that when we venture outside of our own area of specialization. Many technology professionals are quite intelligent, but they aren’t going to be experts in all problem domains, and they shouldn’t present themselves as such.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Stretch Pricing

    I have to admit that I was really excited about the Stretch Database feature in SQL Server 2016. This will automatically archive older data away from your database, but let the query optimizer go get the data if needed. That’s outstanding. That’s the type of archive solution I’ve struggled to implement in the past, finding the effort complex and requiring application change or active DBA management. Often I’ve found that only 10-20% of the data in my database was accessed often, and the rest relatively rarely. Maybe old data was queried every day, but still somewhat rarely compared to a small percentage of the data.

    Then the pricing for Stretch was released, and I think it’s definitely aimed at the Enterprise. If you stretch a sales database, say a 100GB database and want to move 60GB of that data away, you’re going to pay at least US$930/month for the compute at the lowest performance level. Regardless of whether anyone queries the data. If you want better performance, you can run up in roughly multiples of that amount ($1860, $2790, $3720, etc), however, that’s just for compute. If you add in storage, and you must, it’s a minor cost even 1TB, but still, having a $1k bill for access to archive data, especially when you might find people make the mistakes and do query cold(er) data might seem like a lot for a small or medium sized business (SMB). If you have to get better performance, you’ll pay more per month.

    I’m not the only one that doesn’t love the pricing of stretch. It seems to me that the pricing very much favors the Microsoft share price more than the value of my own business. Perhaps this makes more sense at an enterprise level where storage costs can be high, and separating out older data could result in savings. However for smaller companies, if you’re running a SQL Server, even a 1TB (or 10TB) database, is the addition of another 1TB of storage going to cost much? I’m not sure it does.

    Of course, there are other factors. Less data should mean much better performance from your local system. With some tuning of the feature, I would bet that plenty of people might be able to get 90% of their queries satisfied by on premise resources, when they have a substantial amount of older data stored in Azure. That’s not something I can easily do in my own archival system, or with the addition or more storage.

    What I’d really like to see is a stretch to another SQL Server feature added. I’m guessing we’ll see that, likely in SQL Server 20,18 as I don’t think this would be hard to implement. However since this looks like a cash cow for MS, I bet when we get stretch to an on premise SQL Server, this will be an Enterprise only feature, once again, ignoring SMB needs and desires. Maybe in 2020 or 2022 we’ll be able to stretch on premise at a reasonable cost.

    Steve Jones

    The Voice of the DBA Podcast

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

  • We Manage Algorithms

    “Every business is an algorithmic business.”

    That was a phrase that Microsoft’s Joseph Sirosh used in a keynote at SQL Nexus, talking about the future of software and data. Rather than managing data, many of us will move to manage algorithms, which will determine how data is interpreted, used, processed, and potentially returned to users as information. There are starting to be too many sources of data, too much data itself, being generated at too quick a rate, to the point where algorithms become more important than the actual data in examining, grading, interpreting, filtering, and more.

    This is exciting on one hand, with new opportunities for those that can develop, choose, write, tune, or enhance algorithms. I can easily see greater influence from both developers and DBAs as we work to better manage the floods of data. Especially with 50 billion sensors, IoT devices, and more that are predicted to be online in the 5 years. That’s potentially a tremendous amount of data being generated.

    On the other hand, this is a bit scary as separating good data from bad in the ocean of bits, and choosing helpful rather than hurtful algorithms might create lots of stress, and perhaps even fewer opportunities if few algortihms are reused. This also means we will need algorithms that can help us determine if data is actually good enough to use. After all, in the deluge, there will be bad data, that potentially needs to be excluded from queries. Will software developers become more important than DBAs as we end up with more unstructured data stores, data lakes, or other constructs that might require less administration?

    I’m not sure how things will change, but it will be an interesting world the next few years as we work with larger and larger, more diverse sets of data in our organizations.

    Steve Jones

    The Voice of the DBA Podcast

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