Voice of the DBA

Author: way0utwest

  • Tracking Professional Athletes

    I used to read about technology to allow computers to track a single person’s movements from video footage. We’ve seen this shown in Hollywood movies, where casinos can take a picture of a person and backtrack all their previous movements throughout the day. I have no idea if this is possible, but if it is, it’s a scary proposition.

    However in specialized, controlled circumstances, we can track people very well. The NBA (with SportVU) is tracking all player’s movements and gathering more data that can be analyzed to better evaluate player’s performances. I expect in the next year or two, players will start to review this analysis and learn how to better adapt to the situations on the court.

    The NFL is also starting to use sensors to track their players, but with different goals. They are monitoring workloads, trying to ensure the health and peak performance of players. It remains to be seen how else they might use this data, but in the linked article, there are perhaps more implications for us as data professionals.

    Can you imagine more tracking data, for any movable object available? People, machinery, who knows what else will be tracked, and what else we might need to analyze. I thought spatial data had tremendous possibilities when I first saw it introduced in SQL Server 2005. However across the last decade I think we’ve barely scratched the surface of what location data might mean for applications, using it mostly for mapping locations and routes. If this data does become useful, that means that learning to aggregate, trace, and analyze location might just need to be a valuable, if not core, skill set for the DBA and developer in the future. If you want to get started, maybe run through this article on your system and see what you think.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Rebuilding a Heap–Don’t Do It

    I saw someone mention recently that you can run a REBUILD on a heap table. I hadn’t realized that, but when I looked in BOL, I saw that indeed you can run this on a heap. That’s interesting, in that you can now move the pages in a heap around to remove fragmentation. At first glance that sounds good, but I wasn’t sure.  A little more searching was required.

    The authoritative source for me on many things like this is SQLskills, and sure enough, Paul Randal has a myth piece on this. Paul has a great explanation, but basically if you rebuild the HEAP structure, you are creating work in that every non-clustered index also has to be rebuilt. Why? The pointers back to the heap pages, which are locations, will change.

    What about adding a clustered index and dropping it? Nooooooo, and again, I learned something new. This causes two rebuilds of the non-clustered indexes as they are rebuilt with the cluster addition and then rebuilt when the table changes back to a heap (to get the heap locations). That’s crazy, and certainly not what we want.

    The short answer here is that you want a clustered index, for the maintenance reasons here, as well as others. If you don’t have a fundamental reason not to create a clustered index, just add one.

    Just do it.

  • The Ongoing Costs of Building Software

    I’ve almost never heard of a developer backing down from solving a problem quickly. In fact, I think most developers assume they can solve many problems quickly and whip up useful software. I know I’ve felt this way numerous times in the past. Need a little monitoring? I can knock that out this week.

    However there are costs to maintaining software over time. While many of us can build scripts and small programs that solve problems, we also learn that those little pieces of software need some care and attention over time. That’s well illustrated in the piece titled If You Build It, They Will Complain.

    I had a similar path to that of the author. I built a utility, then another, then another for various departments and people inside of my company. I knocked out useful software that others used. And then I got caught up in enhancing, repairing, and otherwise attending to said software. It was an eye opening experience to realize that often software is never done, unlike the experience I’d had in school.

    I saw a very experienced SQL Server professional note a few years ago that they would never write monitoring software again. It’s not that this person couldn’t, or that they wouldn’t do a good job, it’s that they didn’t want to maintain the software, and there are plenty of good packages that do most of the job needed. This person could fill in missing functionality in small areas, while spending their time on more valuable tasks.

    That’s how I think most of us should look at software. We should look to reuse and take advantage of software that’s written to accomplish tasks, evaluating costs against our time. It’s far too easy to discount an employee’s time, when we could easily outspend the cost of a piece of software already written by someone else if we tackle it on our own.

    Steve Jones

    The Voice of the DBA Podcast

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

  • T-SQL Tuesday #74–The Changes

    It’s T-SQL Tuesday time, the monthly blog party on the second Tuesday of the month. This is the first T-SQL Tuesday of 2016, and all you have to do to participate is write a blog post. It’s supposed to be today, but write one anytime you can and look for the invitation next month.

    I’ve got a list of topics here, but watch the #tsql2sday tag on Twitter.

    This month’s topic comes from @SLQSoldier, Robert Davis. The topic is Be the Change, and it’s a good one.

    Quick Changes

    I’m going to write about SQLServerCentral here. Years ago we were updating our email system to send a high volume of email in two ways. At the time we’d considered purchasing software from others, but found the cost to be significant at our volumes (5-6 figures a year). Instead we needed to handle emails stored in our SQL Server database in two ways:

    • Thousands of bulk emails sent overnight, as quickly as possible
    • Quick, high priority emails sent in response to actions

    These two conflicting requirements meant that a simple queue of emails to send wasn’t easy for us to design around. We also needed to deal with the issues of scaling, so we wanted to have mutliple separate machines that could help spread the load.  We were building a small .NET process that would run every minute and send a series of emails.

    Our design process led us to the need to build in priority levels into our table. We couldn’t think of more priorities, but we allowed for them with a smallint. Our bulk emails were inserted with a priority of 2, and the registration emails, forum notes, etc, were stored with priority 1.

    Once we had a separaton of emails, we needed a way to determine what was sent already. To do this, we used a NULL date for the sending date. This allowed each process to determine when new information had been inserted into the table, and needed to be processed.

    This worked well for a single machine. The process would:

    • query for xx priority 1 emails
    • send priority 1 emails
    • update sent priority 1 emails with the sent date/time.
    • query for yy priority 2 emails
    • send priority 2 emails
    • update priority 2 emails with sent date/time.

    The updates actually occurred for each email sent, so we could easily track the time/order of sends for troubleshooting purposes. We would query a few hundred emails each minute, let’s say 500, knowing that was the rate at which we could send emails. We wanted all priority 1 emails to go, so our value for yy would be  500 – xx.

    As we worked to scale things out, we also needed to track what items were queried by which client. Our solution here was to add a machine name to the data, which was blank when emails were inserted, but would be updated by a client with its name as it queried rows. Since we were looking to determine which emails to send, we’d update xx rows with the name of a client process and then query back those rows. The query used the sent date of NULL with the client name to get the correct rows.

    Using a combination of the date sent, the client name, and the priority, we could easily manage detecting and working with changes to this table and build a high volume queue that worked extremely well on SQL Server 2000, and all versions since.