Voice of the DBA

Author: way0utwest

  • The Charles River – SQL in the City Boston

    headI once rowed in the Head of the Charles Regatta. I had the 3d seat, starboard, in a 4 man boat and it was quite exciting to come up to Boston and row down the famed Charles River. It was hard, and the river was rough, tossing waves in our boat at times, but it was a great experience. This year I won’t be rowing in the Charles, but I am looking forward to a chilly run along the Charles on Oct 7. I’ll get into Boston around 4:30pm, race to the hotel, and then have an evening run. Follow me on Twitter if you want to join me. I’ll probably do a couple miles that evening, and again around 6:30am on Monday.

    The SQL in the City tour comes to Boston on Monday, Oct 8, 2012 with another great lineup of speakers. In addition to myself, g, and a number of our Red Gate developers, we have Adam Machanic (b | t) joining us to deliver a full day of free training to SQL Server professionals.

    Free. Just register and come down to the Joseph B. Martin Conference Center at the Harvard Medical School on Oct 8, 2012. It’s training The Red Gate Way at a great location, with fantastic material and plenty of refreshments. The staff is there to make sure you have a great day, as we do at every stop.

    staff

    booksWe’re looking to teach you a few things, give you tips and tricks for working with SQL Server, debate you, answer questions, and have an inspiring day. Stop any of us, and ask a question, pick up a book for your library, and maybe even win a prize as we sip a beverage at the end of the day.

    It’s a great time and I’m sure you’ll find it worth your time to come spend a day with us talking about SQL Server.

    Register today, and come shake my hand in Boston on Oct 8.

    This is part of my SQL in the City series, covering a few thoughts on the US tour.

  • Labor Day 2012

    This weekend is Labor Day in the US. It’s a holiday set aside to recognize the workers and their contributions to the economy. It’s a holiday here that marks the end of summer and the beginning or school for many people. Hopefully it’s a relaxing and enjoyable day for you.

    It’s a day off for me from work but probably one where I’m laboring. With a fence to build and a horse shelter, I’m going to be busy for the next few weeks. I’ve included a blooper reel for you to enjoy. Whether you’re working or relaxing, hopefully you enjoy it.

    Steve Jones

    View the WMV bloopers (31.9MB)

    View the MP4 bloopers (27.4MB)

  • Quick T-SQL Performance Comparison

    I’m not a T-SQL guru. When I have something that will run often, or I have performance concerns, I’ll ask someone like Jeff Moden or Wayne Sheffield to help me write a solution.

    However I have a few tricks to check things out quickly and determine what’s a better solution. Recently I ran across a thread asking for a solution to a problem that needed to sum data, but also pick values from a certain row. I posted a quick solution, and a few minutes later there were two others.

    I didn’t think mine was great, using a CTE and a subquery felt slightly inefficient, but was it really inefficient? I grabbed the third solution, which was similar to mine, and put both in SSMS. I then ran both pieces of code together, after clicking CTRL+M (include Actual Execution Plan).

    ; WITH MyCTE (acc_no, c_name, cnt)
AS
( SELECT acc_no
       , c_name
       , COUNT(c_name)
   FROM #testing a
   GROUP BY acc_no
          , c_name  
)
SELECT 
  t.acc_no
, c.c_name
, number_sum = SUM( t.number) 
, r_value_sum = SUM( t.R_Value) 
 FROM #TESTING t
   INNER JOIN mycte c
     ON t.acc_no = c.acc_no
 WHERE c.cnt = (SELECT MAX(d.cnt)
                 FROM MyCTE d
                 WHERE d.acc_no = c.acc_no
               )
 GROUP BY t.acc_no
        , c.c_name
;

with cte1 as (
select acc_no,number,c_name,
       sum(R_Value) over(partition by acc_no) as R_Value,
       sum(time_spent) over(partition by acc_no) as time_spent,
       count(*) over(partition by acc_no,c_name) as cn
from #TESTING),
cte2 as (
select acc_no,number,c_name,R_Value,time_spent,
       row_number() over(partition by acc_no order by cn desc,number desc) as rn
from cte1)
select acc_no,number,c_name,R_Value,time_spent
from cte2
where rn=1
;

    With all this code, I ran it and got this in the execution plan window (the results were the same and correct).

    comapretsql

    If you look at the top of each section, where it says “Query 1” and “Query 2”, and then look to the right, you’ll see the relative percentage of cost of the batch. With two queries in this batch, but solution was only slightly worse than the other solution (52% to 48%). That quickly tells me these are similar solutions.

    Now this isn’t an end-all, be-all way to look at queries. This is limited data, and unindexed tables. You’d want to test this with a few loads, and examine the details more closely if you are trying to tune these queries, but as a quick check, this helps to decide if you should think about abandoning one solution quickly.

    When I ran all three solutions (mine first, the 48% one above last), I got this:

    comapretsql2

    The second solution is much worse, almost twice as bad here, so I’d give that up and look at both of the other solutions in more detail if I wanted the optimum solution.

    And probably ask Jeff or Wayne for their opinion in the SSC forums. Winking smile

  • Finding a Balance

    lego USB
    Are devices like this a problem?

    This editorial was originally published on Nov 19, 2007. It is being republished as Steve is on vacation.

    When I started in IT things were much simpler. We had smaller data sets, but hardware was larger. There just wasn’t a good way to transfer large amounts of data on 3 1/2″ floppy drives. I remember CDs coming into being and CD writers became a concern, but since few people had them and we knew who they were, it wasn’t a big problem. The zip drives created a cause for concern briefly with their 100MB capacity, but again, they were somewhat bulky and easy to spot.

    However with the growing sizes of flash drives, storage in cell phones, and the monstrous capacity of iPods and other music players, it seems that data has little chance of being contained by IT within the walls of the organization.

    I saw an interesting discussion about managing all these technology devices and the problems that come with so much storage being in reach for so many people.

    So we’re data people, we get beat up to ensure our SOX procedures work well, and we’re in the trenches. With that in mind, the poll this week is…

    Does it make sense to ban personal storage devices?

    I know it’s not practical to actually try and prevent the iPods and other devices from coming into the building. You for sure cannot take away all the cell phones from people. But does it make sense to prevent these devices from connecting to your network? Ban USB and Bluetooth; don’t purchase rewriteable drives except for admins who can be monitored. Some other ideas?

    That might not even work. Recently a report surfaced about some executives in Korea that stole nearly $2billion worth of trade secrets with USB drives and taking them to a new company.

    Think about the past experiences we”ve had. Suppose you”d locked down floppy drives and CD burners a decade ago. All of a sudden MP3 players appear and get mounted as removable drives. It”s a new twist you hadn”t considered, so you shut down all the serial, parallel, and USB ports. You get a new laptop and realize Bluetooth is now available and works just as well and your controls have been circumvented again. What do you do now? It’s a tough balance to strike. Are you trying to be a control freak and make it an hostile work environment or do you trust your employees and deal with the occasional problems that come with them?

    I’m not sure what the best solution is, but I’d venture to guess that banning the technologies won”t work. Someone will always come up with a new way to get around your controls, and more importantly, you won”t be as vigilant if the controls “appear” to work.

    I’d adopt the Counterpane approach to security, which is what security expert Bruce Schneier believes in. Assume you”ll get compromised and attacked and put systems in place to detect and respond to issues rather than trying to prevent all attacks.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available atsqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). We’ve upped the quality a little on the Quicktime files, so if it’s better from your side, let us know. Comments are definitely appreciated and wanted, and you can get feeds from there.

    The RSS Feed:  or now on iTunes!