Voice of the DBA

Author: way0utwest

  • Improving SQLServerCentral

    A better database for SQLServerCentral: BigTable

    We have some big changes coming to SQLServerCentral over the next few months. In response to a large number of comments about the performance of the site, we are looking to implement some sweeping changes that will drastically improve performance, and make the site much more interesting.

    We send out nearly a million newsletters a day, which places a substantial load on our system. Our hardware upgrade last year improved the performance, but as we continue to grow, we are stressing the hardware. When we add in the global reach of our forums, we have once again begun to noticed a slowdown on the systems.

    Fortunately we have a solution. One of the distinguished engineers at Red Gate has a brother that works for Google and they have agreed to help us out. We received a license to implement the BigTable technology at SQLServerCentral and will be moving our primary database systems to this NoSQL storage. This will allow us to scale out effectively with a large number of small, single socket, dual core servers, each of which will handle a portion of our load.

    In addition we have decided to also scale out our articles using the cloud. With a combination of the BigTable technology and GoogleDocs, we will be moving all our publications to the cloud, enabling you to get access to our content with the power of the Google network. We will have analytics built in and the OpenID framework will authenticate you with a Google ID.

    Of course, this is all a big joke give that today is April Fools day in the US. We like Google’s technology, but we *love* SQL Server and have no desire to move off that platform for any other. Now if we could get Resource Governor in our Standard Edition, all would be great.

    Enjoy your day and hopefully you liked this editorial.

    Steve Jones

    The Voice of the DBA Podcasts

  • Hanging with an Inspiration

    A long, long time ago, in a state far away, I started writing about technology. At the time, in the late 90s, I was looking to share some of my knowledge, and hopefully advance my career along.

    It was intimidating to think that I could produce articles anywhere near the quality of the books I was reading to learn more about SQL, and I needed some advice. I looked at the covers of a few of the titles on my bookshelf, back when I used to collect physical books, and reached out to one of the very well known authors.

    I asked a few questions about being a writer, asked for tips, and asked about how feasible it was to make a living at writing. Needless to say I was surprised when the author of about 10 books at the time responded with some candid advice and information about the publishing side of technology. I got some advice, some realistic warnings about what to expect, and most importantly, some encouragement.

    The author was Mark Minasi, a fellow Virginia Beach resident. Well, fellow at the time. He’s still there, but I’ve moved to Denver

    Over the years I have seen Mark a couple of times at conferences and shaken his hand. Paul Randal is a mutual friend and has introduced us, though it’s always been a “nice to meet you, big fan” from me since one of us is usually walking away to some other commitment.

    However last night I had the chance to sit and talk with Mark over a few drinks. It was the first time I could properly thank him for his advice and encouragement over a decade ago. We shared a few drinks, some laughs, and I got to hang with someone I admired. I got a few complements back about what I had achieved and it was one of the highlights of my trip.

  • The Chance of Failure

    I’ve always worked on the notion that my hardware rarely fails, and while I protect it with backups RAID, etc., I don’t expect failures. That seems to be how most of the RDBMSes are structured, with this idea that things will work well and we have protection mechanisms when they don’t.

    Contrast that with the way Google viewa the world. To them,failure is inevitable at scale, as noted by  Jeremiah Peschaka in The Promise and Failure of Federated Data. At large scales of deployment, Google and other companies assume that there will be a percentage of failures and they have to account for them. This is the same type of accounting that restaurants use (spoilage) and retailers (shrinkage) use for allow for some amount of loss.

    In technology, we account for potential failures with RAID, with HA or DR technologies, and hopefully with substantial testing to ensure that we have properly accounted for potential failures. However it seems that most technology people account for failure as a possibility and not a probability. Many people seem to assume that a serious disaster is not likely in their career.

    I think that a catastrophic, we lost the whole data center, event is unlikely for most of us. As Hurricane Katrina and the recent earthquake in Japan have shown, that is possible. However it’s unlikely for most locations, which is a good thing.

    Failures are inevitable and whether it’s a disk corruption, a server crash, or a building power failure, we have to assume we will experience one and plan for the event. We also have to expect hardware will fail, which means regular checks and monitoring to detect these failures as soon as possible.

    Hope for the best, but plan for the worst.

    Steve Jones

    The Voice of the DBA Podcasts

  • Dude, where’s my memory?

    A session I have wanted to see. Marciej Pilecki has done this before with high ratings and I have missed until today

    He starts with a description of how memory is allocated in the 32 and 64 bit systems. One interesting thin he noted is that the lock Pages in Memory setting on 64 bit still uses the AWE API though the awe setting doesn’t make sense and isn’t used.

    SQL server 2005+ is NUMA aware. Worth noting if you upgrade hardware for those old SQL 2000 instances

    One lazy writer thread per NUMA node.

    TCP NUMA affinity. Hadn’t heard of that but it is something to look up and check on. Marciej calls this “poor man’s Resource Governor”

    The buffer pool is not just for data? Also does single page allocations for other caches. Interesting

    An interesting demo to show the allocation of memory as the server starts and you run queries. dbcc freeproccache
    Might clear buffers but the memory remains allocated.

    Changing the Max memory live shown in Perfmon. Both target and total server memory adjust in real time.

    Memory pressure can be virtual or physical and either internal or external. Inside SQL server, your physical memory pressure could be global across the instance or local to some smaller cache.

    External pressure is a notification from the OS. Clock hands in SQL will sweep cache stores (need to read about this) and asks lazy writer to sweep buffer pool. This external pressure can lead to internal pressure.

    Every memory cache has two clock hands: internal and external. Externals move together. Internal caches move separately from each other

    Demo showing this by querying sys.dm_os_memory_cache_clock_hands

    LRU algorithm used to clear caches

    Procedure cache aging is based on cost of compilation, not cost of execution. This cache can also steal memory from the buffer pool.

    Check on your caches with dbcc Memorystatus. Never have used that before but it shows nicely the “stealing” of buffer pool by the procedure cache

    Good session and very interesting to get a little more insight into the memory of a server.