Voice of the DBA

Author: way0utwest

  • Searching for Plans

    I was re-reading Conor Cunningham’s “Does Join Order Matter?” recently and found this quote in the post that stood out. As an aside, read this post. It’s fantastic.

    “…SQL Server’s QP is actually not set up to give you the best plan.  Instead, it is set up to give you a good enough plan quickly (where good enough is very close to “best”).”

    I get that, and it makes sense. Why spend 4 hours optimizing a query that runs in 2 minutes to get it down to 1:50 (Conor’s example) when the optimizer can spend 1 sec and have it execute in those two minutes. If the optimizer looked for the best plan, I’d be very angry as a customer. Especially if this happened with any frequency.

    Or would I?

    I’m not suggesting that we ought to optimize every query, or that I’d prefer the optimizer didn’t try to normalize queries, or that I want to see the internal tree and try to figure out if I can rewrite the query. I might, however, want to do that for some queries. There are queries that I might want to find the “best” plan for, either because they take a long time to run, or they are run often. I could then use that in something like a USE PLAN.

    Computers are cheap these days, as is disk. If I were to somehow able to copy my database onto an instance of SQL Server on a cheap PC, wouldn’t it be cool if I could set that optimizer loose on my query and let it try ALL possible plans? I’d be happy if it spent that 4 hours and gave me back an optimal plan that I could carry back over to my expensive, busy server, and shave that 10sec off a query that I run 10,000 times a day.

    I could see some value in a “test” instance of SQL Server that just looked for optimal plans. Now if I could just get Microsoft to build it.

    Steve Jones

  • Is Your Database “Web-Scale”?

    It’s not safe for work (NSFW), so be sure that you’ve implemented a cone of silence as appropriate for your environment.

    It’s a parody of the Evo v iPhone video (also NSFW), this time looking at NoSQL and MongoDB v RDBMS systems.

    http://www.xtranormal.com/site_media/players/jwplayer.swfhttp://www.xtranormal.com/site_media/players/embedded-xnl-stats.swf

  • 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.