Voice of the DBA

Category: Blog

  • I Did It – PASS Nomination Application

    I send my board of directors nomination application to PASS and asked for a confirmation. I always worry that something like this might get “lost in the mail,” so hopefully I’ll get a response today. I announced it on Twitter as well, just to be safe.

  • It’s the Journey

    I saw this on the 37 Signals blog as “I’ve already got the prize" and found it to be both true, and a little annoying. It refers to the Nobel Peace Prize, who Richard Feynman is apparently in consideration for.

    The speaker comes across a little arrogant, and a bit of an ass, but what he’s saying makes sense. He’s not necessarily a fun guy to be around, but I think many brilliant people, especially teachers and researchers, aren’t.

    However he’s saying that the journey, the fact that you are learning and figuring things out, is what’s important. It’s not the award, it’s not being a part of some group that recognizes your efforts that’s important.

    I agree with that. It is nice to be recognized, to get kudos, maybe some reward for your effort, but if you shoot for that reward and it’s the best part of your work, then I think you are going to be constantly disappointed.

    Enjoy the journey, smell the roses, take pride in your effort and what you do. If there is some recognition or reward later, that should be a small part of you enjoying what you do.

  • The Wiffle-Waffle, or Why I’m Running for the PASS Board of Directors

    I’ll announce it here first, along with some reasons why I’m waffling from an earlier post:

    I’m going to run for the PASS Board of Directors in 2010.

    I am finalizing my application and getting reference letters before I send it to the Nominating Committee.

    Why

    I’ve always admired my wife for her passion with horses. If she could quit her job tomorrow and train horses (and their riders) full-time she would. She dreams about getting there, and spends as much time as she can with the big beasts. I support that effort, sometimes with a shovel, and hope she gets there some day.

    However I haven’t felt any great passion in the past. When I was 35, my wife and I talked about retiring for 6 months when we each reached 40. When I sold SQLServerCentral, she asked me if I wanted to still try retirement. I declined since I didn’t have anything I really wanted to do. No great passion drove me. I enjoy hobbies, and I enjoy life, but nothing seemed to really move me enough to want to do it full-time.

    I was wrong.

    Without realizing it, something had crept up on me. Recently my wife and I were discussing whether I should run for the PASS board, and she was asking my reasons for doing so. What would I gain? Does it help my brand? What can I do? Why bother? As I tried to give her answers, I let my mind randomly wander across the last decade and my involvement with PASS. The things I’ve enjoyed at the Summit. The numerous times I’ve been ready to throw my hands in the air and completely divorce myself from PASS. The way that I’ve tried to influence and push PASS in ways that would benefit the community. When I finished talking to her, she told me this.

    “It’s a no brainer. Run”

    She told me that I had displayed a passion that she hadn’t seen before either. A real drive to make a difference that shone through in my words. She had concerns over the time commitment, but she did think we could manage that this was important.

    I slept on our conversation and then made the decision to move forward the next day.

    If my application is approved, it will be posted on the PASS site, but if not, I’ll post it here so you can judge for yourself if I am someone that is a good candidate for the PASS board of directors.

    I am both excited, and a little nervous, about the this process moving forward. I look forward to meeting a few of you on the campaign, err, SQL Saturday trail.

  • SubQuery Performance

    Why would you do this?

    select distinct(hostname),
    (    select count(*) as criticalCnt 
     from @temp where severity_guid='0168A833-1732-411E-8205-C2F6CD91737D' 
         and hostname=t.hostname
     group by hostname),
    (    select count(*) as criticalCnt 
     from @temp where severity_guid='CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5' 
         and hostname=t.hostname
     group by hostname),
    (    select count(*) as criticalCnt 
     from @temp where severity_guid='C4CF8A23-A106-4617-BAB0-94DA3CA74EF1' 
         and hostname=t.hostname
     group by hostname)
         from @temp t

    I ran into this on a post where someone had asked about how to basically call a CASE statement. The posted didn’t know how and someone posted this as a way to tally the various counts of alerts.

    I had glossed over it when I saw it, but when someone else replied with this statement, saying performance was better, I decided to look at see how much better.

    select hostname,
    sum(case when severity_guid='0168A833-1732-411E-8205-C2F6CD91737D' 
         then 1 else 0 end) as [Count_Of_0168A833-1732-411E-8205-C2F6CD91737D]
    ,sum(case when severity_guid='CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5' 
    then 1 else 0 end) as [Count_Of_CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5]
    ,sum(case when severity_guid='C4CF8A23-A106-4617-BAB0-94DA3CA74EF1' 
         then 1 else 0 end) as [Count_Of_C4CF8A23-A106-4617-BAB0-94DA3CA74EF1]
     from @temp 
    group by hostname

    I set the statistics on for these queries and found these results.

    Query 1:

    Table ‘#45544755’. Scan count 52, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Query 2:

    Table ‘#473C8FC7’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    That’s a huge difference. If this were to be run with any significant frequency on a system, you’d be using (4 x the number of rows) as many scans of the data than you needed to. Even though they are logical scans, it’s still CPU and memory movement you are requiring, and if this were a significant amount of data.

    You can also see a drastic difference in the execution plans:

    ScanPlan1

    ScanPlan2

    I’ll let you figure out which plan goes to which query.

    This is a simple example, but it shows where someone really can get poor performance over time with badly written SQL. It’s nice to have various ways to solve problems, but you also want to choose the appropriate tool. Subqueries make sense at times, but this isn’t one of them.