Voice of the DBA

Tag: sql server

  • The Identity Debate

    This editorial was originally published on Mar 5, 2008. It is being re-run as Steve is on vacation.

    The Identity property might be one of the most controversial constructs in SQL Server, or at least right up there with the NULL/No NULLs debate. Someone sent me a note awhile back about this debate and I thought it might be worth bringing forward in an editorial.

    When I was getting started in SQL Server in the early 90s, I learned about primary keys, natural keys, and the ways you go about modeling a system. It all made good sense, especially at the time when I was working with systems that took years to build and were based on very well understood processes with tight controls on the data involved. So I learned to look for natural keys.

    A few years later I moved to a smaller company, considerably more agile, that was trying out new way of doing business. In the process of trying to revamp their systems, I found that things I was told were “unique” or “set in stone”, weren’t. Not even close, more like set in Play-Doh. And even then in a very humid environment with no danger of things drying out.

    I ran into identity field at some point and found that they were lightweight, solved a lot of problems, didn’t interfere with natural keys, and above all, were easy for a DBA to remember and work with when building applications or troubleshooting issues.

    However there is often a huge debate out there on what the value of identity colums is and how should they be used. Some people, like Joe Celko, rail against their use in a relational system. Others use them everywhere, making them clustered primary keys for every table. Many people are somewhere in between, using them where it fits, and sticking with natural keys if they are identifiable. Some people even use identities as their primary key, relegating natural keys as alternates with a separate unique index.

    I don’t think we put out enough information here, or on any other database site, that would help people better model their data and build relational systems. It’s a hard topic to write about and it’s hard to do it well without making everyone’s eyes glaze over.

    But I wanted to put this out there and see what you think about identity columns and their use (or mis-use) in today’s databases. They are definitely a tool that’s available to the SQL Server professional and one that I bet everyone has an opinion on. We surely value those opinions and as with many of these debates, I’m looking forward to learning a thing or two.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

    Overall RSS Feed: or now on iTunes!

    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.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.

  • A New World Record

    TPC Logo
    SQL Server is doing well in the TPC benchmarks.

    This editorial was originally published on Mar 4, 2008. It is being rerun as Steve is on vacation.

    Editor’s note: The current TPC-E record is 4,614.22 tspE.

    The product hasn’t even been released, but there are already two TPC-E benchmarks completed for it. Neither of these is a world record in price performance, but the NEC benchmark takes the top Performance slot with 1,126tpsE transactions. That’s nearly double the previous SQL Server 2005 mark from Unisys, but to be fair it was twice the CPUs and cores, though the same number of threads. And a significantly larger price tag.

    What’s even more interesting is the IBM entry, which took 3rd place on the Performance chart and 5th place on the Price/Performance chart. However this system is a smaller system, a 4 way system (quad core) that used the same amount of RAM as the 2nd place system (128GB) and much less than the top performance system (512GB).

    There was also an ETL world record, where Len Wyatt posted a blog entry about his team loading 1TB of data from flat files, moved across a network and loaded into a SQL Server database in less than 30 minutes. The actual time was 1794 seconds, which I’m sure excited Len and his team.

    Now benchmarks are just that, they’re a way to compare systems using a common set of application software and measurements. They don’t necessarily apply to the real world as there are all sorts of tricks (disabling checkpoints, RAW volumes, etc) that companies play to tweak these systems. Some of these shouldn’t be allowed in my mind, like RAID 0 disks, which wouldn’t be in a production system.

    These are new benchmarks, so it’s hard to tell what impact or relation they have on the real world. These new systems also used Windows 2008, which might have affected the speed at which things get done. There are definitely networking improvements in there, so time will tell if these are really, really fast systems or just what we’ll expect from the new versions of Microsoft servers.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

    Overall RSS Feed: or now on iTunes!

    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.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.

  • The Cost of Storage

    SAN disks
    The cost of storage per GB drops, but the cost to companies seems to keep going up.

    One thing that we can be sure of as data professionals: we will need more storage over time. We gather more data in our organizations and more data is sent to us by partners or automated systems. At the same time we must ensure all our existing data is always available for users. I don’t know how we solve any of these problems, but I do know that any solution will require likely more storage in the future.

    As DBAs, we aren’t too often involved in the cost of storage. These days as many companies move to SAN-based storage, there are separate administrators are involved in the specification and purchasing of the actual hardware. That means that as DBAs we are removed from the impact of our requests for more storage. We don’t necessarily know what the cost to the organization is when we request another 400GB LUN. However this week I’m curious if you know the answer to this.

    What’s the cost of storage in your organization?

    I’m wondering if you know the actual cost in dollars or the impact to limited IT resources of enterprise level storage in your company. Does this impact the way you do business as a DBA? We often don’t have control over the growth of our data, but we seem to regularly be pulled into the discussions on how to best manage the amount of data and even archive data or reduce its impact on the organization’s storage.

    Let us know this week if you are impacted by the cost of storage in your organization.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • The Encryption Primer Resources

    I delivered my Encryption Primer talk this past Tuesday at the Boulder SQL Server Users Group and last night at the Denver SQL Server Users Group. Great questions and discussions, and I hope those of you that attended found it interesting.

    I’ve updated my deck and code samples, and here are the updated files for download:

    Powerpoint: The Encryption Primer v8.ppt

    Code: EncryptionPrimer.zip