Voice of the DBA

Tag: mcm

  • Master Training

    A Kung Fu Master

    As a kid, growing up on Bruce Lee, Chuck Norris, and Saturday afternoon Kung Fu Theater, I always wanted to be a martial arts master. A few years later, being a Jedi Master caught my interest despite the fact that it wasn’t a viable goal on Earth in 1977. Now I’ve started down the path towards another master, the Microsoft Certified Master.

    Actually I’m not going down that path. I had the chance to take the written test with no downside for me, so I took it. I have no intention of actually pursuing the lab at this time. It’s a lot of work, and not necessarily something that would provide me a lot of benefits right now. I may change my mind if I were looking for consulting work, but I am quite happy with my job right now and don’t want to change.

    I do plan on taking one or two of the MCM training being offered by SQLskills this year, but I won’t be looking for the intensive  4 weeks of training to prepare me for the lab exam. After reading Brent Ozar’s adventures last year, I think I’d need all 4 weeks of class along with a few more weeks of prep to be ready for the lab.

    So why am I taking a class? First, it’s good training. I have had the privilege of seeing both Paul Randal and Kimberly Tripp speak before, and it’s always enjoyable and informative. I come out of their hour long sessions with so many notes and new knowledge that it I feel like I’ve been learning for a full day. I am sure that a week’s training from them will result in a small novel that I’ll need to review over the following months.

    Training can be hard to get funded, but it is often worth it. I hope that more companies would be willing to improve the skills of their people with this type of advanced training, even without paying for the certification testing. The chance to learn from experts in particular areas is an investment that I think is worth making in my career and in yours.

    Steve Jones

    (originally published at http://www.sqlservercentral.com/articles/Editorial/72278/)

    The Voice of the DBA Podcasts

  • You Need Two SLAs for Disaster Recovery

    Continuing on with my MCM prep, I was listening to the High Availability/DR prep module today and I was once again surprised by something. Typically I have heard all kinds of talk for SLAs, usually in terms of network traffic. For databases, I have had SLA conversations that were for downtime that usually go like this.

    Me: How much uptime do we need?

    Manager: 100%

    Me: We can’t really do that in a cost effective manner.

    Manager: Why not, the telephone companies are always up?

    Me: Well, even the telcos measure their uptime in terms of 9s?

    Manager: (blank look)

    Me: They talk about 99% reliability, 99.9%, 99.2%, each of those being a “9” of availability. The high water mark seems to be companies aiming for five nines or 99.999%.

    Usually at this point I need to write this down so they can understand why five nines are 99.999 and not 99.99999

    Manager: Let’s go for five nines.

    Me: That’s only 5 minutes of downtime a year. We can’t apply patches in 5 minutes. A better number is usually 99.9 for us, which means across the entire year we get a 8 hours of downtime. That’s a good number to aim for across a year.

    Manager: We can’t be down for 8 hours!

    Me: (blank look)

    At this point I usually give up and go in search of someone that will better understand things.

    However an SLA for downtime/uptime isn’t enough for SQL Server. You also have to think in terms of data loss. If we lose a server, what about transactions in flight? What about things not transferred to the mirror server or log shipped server? What about losing disks and no tail of the log backup?

    An SLA for data loss is important as well. And like the conversation above, your business people will say zero data loss. Quiz them to find out what can be recovered and to what extent lost data costs the company. The compute the cost of your various HA solutions to decide how to handle things.

  • Isolation Levels–MCM Prep

    I was watching the Isolation Levels video for my MCM prep and learned something about the isolation levels. I knew there were four levels, but I hadn’t realized that SQL Server used the default of level 1, out of the 4 ANSI levels. The levels are:

    • 0 – Read Uncommitted
    • 1 – Read Committed
    • 2 – Repeatable Read
    • 3 – Serializable

    You can read more about them here, but what I learned was that these levels are actually organized in a logical manner. If you look at it this way, the levels can also be described as:

    • 0 – Dirty reads allowed, so data that has been changed in a transaction, but not committed could be included in a query. This means that you could return results in a query that don’t exist. The transaction could roll back.
    • 1 – Potential phantom reads or non repeatable read. Queries only read committed data, but since they don’t lock the whole slice of data, a multi-statement body of work could potentially get new rows between statements or return different results.
    • 2 – Repeatable reads guaranteed, but potentially phantom rows could appear.
    • 3 – No repeatable reads or phantoms possible.

    The flip side of the greater data integrity is that more locks are needed, and held longer as you move up the levels. That can impact concurrency.

    As with everything in databases, there is a tradeoff.

    Note that SQL Server allows you to actually get around some of these issues with row level versioning. Of course, this isn’t free. It comes at the expense of space in tempdb.

  • Resource Governer–Memory Limits

    I had looked at the Resource Governor early on when it was being developed and first released on SQL Server 2008, but I hadn’t spent a lot of time on it. It was the first cut at a throttle that I had been asking for since 2001 or 2002 when I saw IIS get a CPU throttle. I loved the idea of being able to slow down the amount of CPU that an individual query could take, which I thought would really help limit the amount of damage that a particular query could do on your system.

    However I hadn’t spent much time on the memory limits. While studying for the MCM, I downloaded this white paper and watched the Paul Randal video from Technet. In it, I was reminded that the memory limits you set apply to the query memory, not the buffer pool memory.

    What does this mean? It means that while you can limit the memory that a query uses for it’s data, for it’s plans, etc., you can’t prevent a query from flushing the buffer pool when it causes a lot of reads from disk.

    This is a limitation of the Resource Governor, and we might see this changed later, but it’s a good thing to remember that you cannot use Resource Governor as a way to guarentee predictable performance from your SQL Server. The engine is still a shared resource and one person can still affect it’s operation. Resource Governor does allow you to limit the damage, and it can be used to prevent someone from bringing the instance to a standstill. However it does not provide the complete control that you might assume is implied from the name.