Voice of the DBA

Author: way0utwest

  • Goal Update – Misc accomplishments

    I have been trying to better track my goals this year, and find myself failing at some, exceeding at others. I’m not sure if that means that I did a poor job setting goals, or that I’m not so good at following through. I’ll have to evaluate that in December.

    In terms of goals, this is a milestone for me. Not really a big goal for this year, but it’s an accomplishment that I’m proud of, and it exceeds a goal I set two years ago.

    As of today, I’ve been running continuously for two years. Every day I’ve run at least a mile every day for 730 days. Yesterday was my 730th day, and today, Sept 10, 2010, starts my third year. I track this on my other blog (The Daily Runner) and I’m listed on the Running Streak Association’s site as #210 on the active list.

    Getting through these past two years has given me new insight into my health, and also kept me going in other areas. I learn to better manage my life and keep on a more even keel over time as well as motivate me.

    So I looked at how other things are going. I checked my posting for the year, and I have these counts:

    Month  Post Count
    8        410
    7        453
    6        377
    5        352
    4        442
    3        277
    2        372
    1        280

    That’s an average of 370 a month, which is above my goal of 300/month. Pretty good, and I’m enjoying answering questions again, which is a bonus.

    I also checked my blog count. I was trying to post every day, and I know I missed a few days, but I had a few double posts as well.

    Month Posts
    1       39
    2       37
    3       26
    4       37
    5       28
    6       44
    7       37
    8       30
    9        8

    With an average of 22 days a month, and an average post count of 34, I think I’m doing well here.

    I did miss one newsletter, and started two in the morning when I woke up, but otherwise doing well there as well. Now if I could just find some time to work with SSRS….

  • Cloning Virtual Box Images in Windows 7

    I use VirtualBox from Sun to do SQL testing since it supports 64-bit guest images, which I want need for some things, like Windows 2008 R2, which only comes in x64.

    However I hate reinstalling stuff that I don’t need to. And with a multitude of SQL versions, and a question today on eval edition (which I never install), I wanted to be sure I could quickly setup a new image when needed. In older VM software, I’ve just copied a VHD disk image and then added a new VM with it. However it’s not as simple with Virtual Box.

    I found a nice procedure from Stuart’s Notes on cloning and copying images, so I started there, but found a few differences on my machine, so here’s what I did.

    Found my Virtual Box files, which are located at: C:\Users\Steve\.VirtualBox\HardDisks (note, this is different than Stuart’s procedure. This is where things were installed on my Windows 7 x64 machine. I don’t have a “VDI” folder.

    In Windows, you can right click this in the Explorer and get the path. I then pasted that with a “CD” in front of it in a command prompt.

    VirtualBox1

    Next I grabbed the path to the Virtual Box installation, since I need to run the “vboxmanage.exe” program. For me, the path was “C:\Program Files\Sun\VirtualBox”

    VirtualBox2  At this point, I essentially needed to run

    vboxmanage clonevdi  “Old Disk" "new disk"

    That, however, doesn’t really work easily in Windows. Instead, you need pathing, quotes, and other things to ensure that you get a copy. I had to run this:

    C:\Users\Steve\.VirtualBox\HardDisks>"C:\Program Files\Sun\VirtualBox\vboxmanage
    " clonevdi  "Win7 64.vdi" "Win7 SQL 2K.vdi"

    This completed, with these results:

    VirtualBox7

    From there, I fired  up Virtual Box, and created a new machine. I named it picked the old OS, and then got to the Virtual Disk dialog:

    VirtualBox3

    I clicked the button that has the arrow in the image. This brings up the disk selection dialog. I clicked “Add”

    VirtualBox4

    From there, the next step was to select my new disk image:

    VirtualBox5

    I picked it, then closed the dialog and I saw this image listed for my VM.

    VirtualBox6

    Complete the wizard, click start, and I had a clone of my virtual machine, same state, guest additions installed, and same pwd. I changed the background for my guest and then installed the software I needed on this one.

  • Instrumentation

    In a discussion recently someone mentioned that they built in timing mechanisms into their application so they were aware of how long certain modules or functions were executing. Using this data, they could easily determine if the system was performing poorly by comparing the average of timings to the current performance.

    I thought that was a great idea, and it shows some proactive, forward thinking on the part of the developer. I haven’t often seen this in SQL Server, though it does seem that many good DBAs maintain some type of overall baseline for their database servers in order to help them response to performance issues.

    For a Friday poll, I was curious how many of you actually think about monitoring your systems. I decided to ask this in a general way to get an idea of what others might be doing inside SQL Server as well as in the applications that connect to SQL Server.

    Do you build instrumentation into your applications and code to measure the performance?

    I’m wondering if you actually collect, log, and even analyze data on how long processes take, or how much data you work with. Do you capture row counts from data loads or processing? Do you maintain an average time for code to execute?

    I haven’t done a lot of this in code. There was one application which watched for files and loaded them every day. We built code into SQL Server that tracked how long the load took, and how many rows were processed, mostly as a way to prepare for future hardware upgrades. As a production DBA, I have typically kept an average on size and time of backups for the same reason. It helps me capacity plan, and alerts me to growing loads for the server.

    If you do anything differently, or if you’ve found this to be valuable, or even a waste of time, let us know this Friday.

    Steve Jones

  • T-SQL – Is Saving Space Worth It?

    I ran across this blog post on Joe Celko’s Restaurant Seat Assignment Problem that found a way to implement the smallest amount of storage for a classic problem. It’s more of a challenge than a real solution.
    Or at least I hope so.
    The solution actually uses some bitmasking to save space and cram more information into a single field. It’s creative, and interesting to look through, but is it the type of solution that you might want to use in your actual code?
    Personally I like seeing some elegant solutions in code, but not so esoteric or obfuscated that the average developer can’t understand it. Once something gets too complex, it can require more time to support and understand than it might be worth.
    Not that we shouldn’t be educating or improving the skills of other developers in your company, but if there is something that is an order of magnitude more compelx than most other code, I think that a meeting, a brown bag, or even a blog post to explain it is warranted.
    Back to the title of the post. Is saving space worth it? At times it is. I don’t advocate using INT for all fields when a tinyint or smallint will do, but I’m not sure that I find bitmasking to be worth it. In general, I think that creates more confusion than it solves problems or increases performance.