Voice of the DBA

Tag: sql server

  • Data Overload

    Data rich and information poor. I think that describes most of the companies I’ve worked for. It’s a theme in this piece from Forbes, noting that many companies use only a fraction of the data they have to make decisions. Certainly I think that many of us that work as data professionals might note that most queries access a portion of our data, often the newest data, with older data sitting on storage systems, constantly powered on, but rarely included in a report.

    Retrofitting archival processes into an existing application can be hard. Even more so when there is rarely queried data that clients want to be sure is still accessible somehow. The Stretch Database feature in SQL Server 2016 might help, but I bet it’s a long time before most of us have all our systems on SQL Server 2016 or later versions, let alone finding someone to pay for this feature.

    There are real costs to keeping this data around, first and foremost of which is the stress for us as developers and DBAs as we try to tune queries the must run against larger and larger data sets. Actually, I’m only somewhat kidding. Management and clients might not care about this, but having to work against larger and larger can be stressful for technical professionals.

    There are other, more concrete and measurable costs to keeping this data around. The cost of power and larger storage systems. With many companies keeping multiple copies of production systems around for different purposes, these can be noticeable costs. There’s also the time factor. If our systems run just 10% slower, that’s potentially 10% less business we can handle. Or maybe all that extra data means more annoyance and frustration from our customers due to slow systems.

    We are going to get more and more data in our systems. While much of this data may be useful, if we’re overloaded, we may not be able to take advantage of the information. We also might get erroneous results if we don’t recognize that data gets old, and the value we might have from a row today might not exist in a few years. We should also realize that at times we have lots of data that isn’t useful at all for our organization.

    I really would start thinking about the ways in which we can actually remove older data from our systems, with archival to cold systems, or even deletion if we’ve moved copies of data to other applications, such as data warehouse systems. Or maybe just deleting data we know isn’t going to provide any information. Above all, remember that warehouses will fill up at some point, unless you buy more and more (expensive) storage. Keeping all data accessible might not be the best decision for your organization.

    Steve Jones

    The Voice of the DBA Podcast

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

  • T-SQL Tuesday #80–Chris Yates’ Birthday

    tsqltuesdayIt’s the 80th version of T-SQL Tuesday. You can see all the parties from the past on my page, but feel free to jump in here or elsewhere. This is a monthly event, where anyone can write on a topic.

    This month’s invitation is from Chris Yates, with today being his birthday.

    Happy Birthday, Chris!

    The topic is open this month, so here we go.

    SQL Server Isn’t Easy Enough

    I think that SQL Server is a fairly easy system to work with. I first started working with SQL Server on OS/2 1.3, and had come from working with lots of Unix, DOS, and Netware command lines. Windows 3.1 was still new, and we thought all the various command line tools for SQL Server were great.

    Even as I continued on in my career, working with Oracle on multiple platforms, DB2, and other relational systems, SQL Server was the easiest to use.

    However, I still find some simple, easy things that aren’t easy to handle. I think there is work being done with some of these items in Redmond (nothing specific I know or can say, but just a feeling), but these items have been an issue for some time.

    Database and Log Backups

    One of the really simple things many SQL Server professionals know is that we need to schedule at least one full backup to run, and then regular log backups, at least by default. The alternative is to set the database to simple recovery (not the default).

    The problem is that for many installations, people don’t realize that they need to do this. While it’s easy to say they should learn this, it’s also silly. This could be easily handled with software setup. Create a default backup setup for each new database, as part of the database creation wizard.

    I’d really like to see these defined in the database themselves, and loaded into the instance from the database, and hopefully we’ll get there.

    Replication

    I don’t have specific complaints here, though I could come up with some. The real issue I see is that replication is very brittle. The way replication works, and it’s barely changed in a long time, is that it requires lots of setup, and isn’t tolerant of issues. Far, far too often the solution when deploying changes to the database, or troubleshooting issues is to drop replication and re-enable it.

    Most people running replication have been burned and end up scripting everything as soon as it’s set up.

    This shouldn’t be that hard, or at the very least, some work should be done to improve the robustness of replication.

    More

    There could be other things, but I’ll stop here, and see what else people say about SQL Server this month.

  • Why Use R Services?

    I ran across a blog post from the very talented Joe Sack recenty, who I hope makes a few more minutes for me the next time we’re in the same town together ;).

    The post is a look at some of the customers that are using R Services in SQL Server 2016. As you might expect, there are highlights from customers that show dramatic improvement in performance. Going from 200 valuations/hour to 12,000/minute and taking a process from 2 days to 1 hour. I’m sure not all operations improved that much, but I bet most had some improvement. R Services is a big improvement in the way data is analyzed with the R language.

    What I really like, however, is that the piece includes some of the gotchas customers experienced, with links on how one might go about fixing the issues. There are also hints on visualizations and performance tuning options. I like looks at technology that include some details on those items that work well or don’t work well. This is a good overview that I hope gets more customers interested in using R services.

    But, I hope that we see deeper pieces that can give technical guidance on specific scenarios. Which models scale well and which don’t? Which options in some analysis improved (or hurt) performance? There are plenty of items which are best answered with specific examples, rather than general advice. With all the customers and data Microsoft gathers, especially for companies that might use R Services in azure, I’d expect that we could get some useful, detailed examples on how this (and other) technologies actually work in the real world. I’d like to see this guidance, not just with R Services, but with other technologies as well. Certainly providing some sample data sets and code that performs well really (or tuning options), is what most of us want to see.

    Of course, I’d like to see more of these stories and details at SQLServerCentral as well, so if you are solving problems and want to publish something, drop us a note. We’d love articles from the real world, whether on SQL Server 2016, or any prior version. The more reference problems and solutions we have, the more people learn to code better.

    Steve Jones

    The Voice of the DBA Podcast

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

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