Voice of the DBA

Category: Editorial

  • Cool Projects

    I had a conversation recently with a developer that was working on a rather neat problem. This had to do with a financial system and it involved some complex calculations and real time interactions with a variety of systems. It was important for their company, and a lot of pressure was on this developer to not only deliver this software quickly, but also to have it perform at a very high level. This particular person was using Red Gate’s ANTS Performance Profiler to dig into their code, feeling pressure to make it as efficient as possible.

    However this person was also enjoying themselves. It was a challenge, and it was a cool project.

    I know this community is made up of people working in all sorts of industries, in a variety of roles. We develop many different kinds of software. Some may be for employers, some may be for ourselves, some may be for a side business, but  across that spectrum of work, there are some interesting applications.

    What cool things are you working on?

    It might not be software. Perhaps you’re working on an interesting data application, like a super collider. Maybe you work on some gaming software, or you manage a system that makes people’s lives better, easier, or even possible. I’m sure more than a few of you work on some real time systems, which have all kinds of data challenges.

    Let us know this week what type of interesting work you’re doing.

    Steve Jones

    Video and Audio versions

    Today’s 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.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 16.1MB) feed

    MP4 iPod Video ( 13.4MB) feed

    MP3 Audio ( 3.3MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • The Costs of Data

    Most of us know that there is a cost associated with the management and storage of data. After all, it’s part of the reason many of us have jobs as data professionals. Our salary is a cost, though we also realize there are real costs in electricity, on-line storage, backup storage, and more that relate to the stewardship of data.

    Many US government agencies are being asked to share more of their data publicly, but without additional funding. That could mean less research is funded as the cost of opening their databases rises. How this should be handled is open to debate, but Vint Cerf and others have noted that someone has to pay to manage data, and public/private partnerships are a potential solution. However, the fate and cost of public data isn’t the core issue.

    The core issue is that data sets are growing, and in some cases, growing at phenomenal rates. We see companies getting caught up in the Big Data hype, often gathering and storing data just because they have the ability to capture more data. However as our data volumes, and rates of growth increase, that impacts the performance, and perhaps availability, of our systems. Perhaps more to the core for many of us, all the data impacts the performance of our jobs. Systems might run slower, we have more work to do to analyze data, write reports, or deal with the every widening array of tools that people want to use to analyze the bits and bytes we manage.

    The costs of data storage, of tracking and managing systems, or administering a multitude of environments, of supporting new tools for analysis, are going to grow. Most likely that means that each of us will be asked to handle more data, with few staff increases. We will be more valuable, but more will be demanded of us. The physical costs will be easy to measure, but I do worry about the personal costs, in stress and pressure, as we each continue to do more and more every year.

    Steve Jones

    Video and Audio versions

    Today’s 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.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 15.7MB) feed

    MP4 iPod Video ( 19.3MB) feed

    MP3 Audio ( 3.8MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • Always Abstract

    The way we build databases and design security is rarely done ideally. It doesn’t matter what your Utopian design is for schemas and objects; most of us make compromise as we deal with the pressures and diverse requirements thrust upon us in the real world. That’s unfortunate, but it’s also reality. I often see advice to use some best practice, like using only stored procedures and revoking all access to tables. I rarely see this in place, though I sometimes think that an investment in developer education would quickly erase any productivity losses from implementing stored procedures and probably reduce security issues.

    I don’t know we’ll get away from stored procedures, but I wonder if we can get away from tables. I’ve seen a lot of design refactoring lately that requires changing schemas. The use of views to hide base tables would allow developers to easily make changes changes. Implementing vertical or horizontal partitions (or federations), additing of new columns, and even enhancing your application with RBAC (row based access control) or encryption would be easy.

    This has me wondering if we should consider it a basic practice to not ever expose base tables and use views for all of our data access? Would it make sense to make a tCustomers table and a Customers view from the very beginning of development? We could grant rights to the view and use that for all queries. Perhaps SQL Server should include a dataViewReader role and dataViewWriter role that would allow quick assignment of rights to all views, and not all objects.

    I know there can be times when this wouldn’t work, and the proliferation of queries building on views built on views can be an issue. However, I also think it would make sense for data access through tables to be the exception, not the rule. Would this work in your environment? I bet it would for most of you.

    Steve Jones

    Video and Audio versions

    Today’s 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.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 17.0MB) feed

    MP4 iPod Video ( 20.6MB) feed

    MP3 Audio ( 4.1MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • Guidance

    How do you manage SQL Server? Do you have a list of rules that tell you to do yy whenever xx happens? Or if yy doesn’t solve the problem, you next try zz? Some of you have guidelines like that, though it’s likely that the way you solve problems is somewhat internalized. You adjust your “rules” or “steps” based on the situation. Do you reduce or increase parallelism when you have high CPU? Either change might be the solution, depending on the causes of an issue.

    SQL Server is complex. We can’t easily reduce the solutions to problems to a set of rigid protocols that ensure optimal performance. If we could, there truly wouldn’t be as much of a need for DBAs as there is. Consultants and experts are well paid, and often very busy precisely because SQL Server is not so easy to tune and optimize in many organizations.

    That’s a bit of the point I saw in Grant Fritchey’s “Be Cautious Offering Guidance” piece recently. When trying to help someone improve performance, or reduce issues, or anything else, you can’t often give someone a rule of thumb that just works. You can’t give them a one sentence solution. Most things in life aren’t that simple, and neither is working on SQL Server.

    The environment in which your application runs is complex and there are lots of variables that can affect the advice or guidance that an expert might recommend. It’s the reason we see DBAs often saying “it depends.”

    Because it does.