Voice of the DBA

Category: Editorial

  • Resetting DMVs

    I'd prefer more control on when I reset DMV data.
    I’d prefer more control on when I reset DMV data.

    Recently I was working on an indexing presentation and looking for missing, duplicate, and unused indexes. As I set up demos to show the effects of indexing, I was constantly adding and deleting objects, and even resetting the DMVs to show statistics by starting the instance. The documentation for some DMVs, like sys.dm_db_index_usage_stats include a note that the counters are initialized to zero when the instance is restarted. If a database is taken offline, or detached, all rows referencing the database are removed.

    That got me thinking. Why is this data removed? I’m sure some of the data is stored in memory and automatically reset, but is this the best way to handle this data? Wouldn’t it be better to persist this data and allow the DBA to reset values when they were ready? I know we can store this data in a table periodically now, but I think keeping this inside the system views would make sense as an option in SQL Server. When we shut down a database, persist this data inside the database. That would be closer to my vision of self-describing databases, which contain all their own metadata.

    I know there might be performance impacts to persisting this information, but I can also see benefits, especially when you might have system crashes or problems. Being able to recover the information from DMVs, even incomplete information, brings us closer to a robust, flight-recorder kind of system that monitors itself and allows administrators to review information that might help them discover the causes of any issues.

    I don’t think that every piece of data needs to be guaranteed to be written to the DMVs, or that the system should slow down to do so, but give the DBAs some control here. If we have the need for more extensive logging, or we want to retain whatever information we can, allow it to persist in the DMVs, or at least make snapshots of DMV data easy to take and store. It’s another form of logging that I’d appreciate having available from the platform.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • Self Describing Databases

    Self describing databases would be very, very handy.
    Self describing databases would be very, very handy.

    When contained databases were introduced in SQL Server 2012, I thought they were a very nice enhancement to the platform. The idea of having security managed inside the database, thereby removing the problem of orphaned users, was something I looked forward to. Preventing collation conflicts is also a nice benefit, though I’ve never had a problem with this issue. However I was, and still am, hoping that this was just the beginning of what a contained database would be. I had a vision of a self-describing database.

    There are many things that we need to manage with databases: maintenance, backups, auditing, performance statistics, alerts, and more. Most of these are set up at the instance, but I’ve never quite understood why. Software should help us work more efficiently, and work with us, not against us. So many of these administrative items are dependent on the needs of a particular database or application, but we perform all these actions at the instance level.

    Backups are an example of a database dependent item, as we constantly teach new DBAs. However the backup plans are set at the instance level, and if you have disparate requirements for different databases, these result in different backup plans. The MSDN documentation says we should even have a manual for each database. If that’s the case, why is the information about a database backup stored outside of the database? I think it would make more sense for the backup plan to be stored in the database, and when the database is attached, or the plan created, the instance would read this plan and create the necessary jobs. If we detached the database, the jobs would be removed. We run recovery in each database, couldn’t that serve to enable or disable any alerts, jobs, etc. for any database that wasn’t accessible?

    I envision much more than just backups as being a part of the database. Every action we need to manage in a database from alerts to auditing, should be stored there. Let the Agent on the instance read the database and automatically set up the jobs needed. If there are conflicts with scheduling, alert the DBA at the instance level, but keep the metadata about the needs of the database in the database. It would make consolidation, or movement of databases much simpler. It would also fit with the idea of a database service, rather than a database server.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • Who Built This Thing?

    I love this logo, and I love working with SQL Server.
    I love this logo, and I love working with SQL Server.

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

    I came across a humorous Tweet (since removed for some reason) on David Reed’s blog. It said: Was Microsoft SQL Server designed by someone who doesn’t need to use Microsoft SQL Server? Jesus.

    There are definitely times that I think the people that build the system stored procedures and other code we can see don’t really work with SQL Server. It seems that best practices aren’t followed and things aren’t even well coded.

    But most of the people I’ve met working on SQL Server seem to be really, really smart guys. They care about what they do and I’m somewhat amazed at times how passionate they are. I can’t name everyone, but I’ll point out one that really struck me.

    Recently I was in Redmond for a small conference and quite a few of the SQL Server program and product managers were there to speak about their work and get feedback. Many of the people speaking did a fine job and some were definitely more exuberant than others, but there was one that really stood out in my mind. This presenter works on the spatial team and gave a quick overview, talked about places that his team had debated about going with future work and then walked to the front of the stage to just talk to the audience. He asked us questions, really trying to engage people and drive the discussion forward. And you could feel the passion he had for his work and to build a world class product.

    For those of you that don’t work in the spatial area, his name is Isaac, and he writes the Isaac on Spatial blog. He really opened my eyes to a subject I had somewhat dismissed since I think plotting locations on a map isn’t that interesting. However his passion for the subject, and his lateral thinking to other areas in spatial such as collisions and evolutions of areas in 4D (locations plus time), CAD/CAM work, planning and more made me think that the addition of spatial technologies is more than just a feature match with Oracle.

    In many ways SQL Server mirrors what I see in other parts of Microsoft. It’s a large group composed of teams, and each of those teams is rather passionate about what they do. They work hard, they make mistakes, they try to correct them and move forward, and mostly they care. They care about what they’re doing and it shows when you can sit down with them.

    And most importantly, I think they dislike some of the marketing moves as much as we, or at least I, do.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available at sqlservercentral.podshow.comto 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!

  • Writing

    This was originally published on May 9, 2008. It is being re-run as Steve is at the Red Gate free Micro-Event for Database Professionals.

    One of the great things about SQLServerCentral.com is that so many of you in the community share your work with others on a regular basis. We get articles from all shapes, sizes, and skill levels of DBAs, on all manner of topics. And most of the time, these articles are inspired by things that actually happen in your jobs.

    They’re not contrived or made up examples, or situations that only mimic a portion of the real world. You’re writing about things in the real world.

    With that in mind, we have an easy poll for you this week:

    Why Do (or Don’t) You Write?

    I expect that if you don’t want to write articles you might not respond here, but I’m still curious to see what thoughts some of you have.

    For me, it’s an easy question to answer: I really enjoy writing. The challenge of coming up with new thoughts, new ideas, trying to explain things to people, getting feedback in comments, it is just very exciting..

    Often my wife will look up and see a goofy smile on my face after I’ve read some comment and ask me about it. I’ve even laughed out loud at times. And I’ve been ready to curse people at others.

    But overall, the thing that I hated most in high school has become the thing I enjoy doing most of the time. I think about writing all the time and spend almost every day working on some editorial here or there.

    So let us know why you write, or why you don’t. Maybe I’ll be able to convince a few more of you to participate and share your knowledge in the next year.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available at sqlservercentral.podshow.comto 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!