Voice of the DBA

Author: way0utwest

  • Internal Marketing

    I heard this recently at my company. Our group, which is relatively removed from the main business of writing software, doesn’t always have the best relationship with everyone else. I think some of it might be that they don’t understand what we do, and some might be that they don’t see us doing anything often.

    My boss was talking about how we might want to do more internal marketing. In other words, presenting some information about what we do, and how we help the business, to other groups.

    That sounds strange, and if you have a small company, you might think that everyone knows what each person does. And they understand it.

    However that’s not necessarily true. Often people get caught up in their work, and when they’re stressed or overworked, they might feel that others aren’t working as hard. Or that they are doing more than their share, even if everyone does a different job.

    It’s natural that anyone feels that way. Just go do some manual labor, like moving boxes. If you work fast and hard, after a short time you’ll be a little annoyed if your partner doesn’t keep up.

    Internal marketing is something that you ought to do periodically. If people get along well enough to do this informally, it is more helpful. Venting about your work, issues, or even successes over a drink after work goes a long way. If the workers don’t do this enough, think about buying lunch for everyone and having some informal presentations from different groups. Or even just letting them chat, but teach them to market their job.

    It doesn’t have to be impressive or spectacular, but just show how the efforts are relevant to the business. I need to work on some sort of presentation to show what I mean.

  • Getting a Page of Results

    The other day I was looking over a couple of articles on paging, looking to see if I could learn something new in T-SQL. I’ve implemented some SQL2000 era paging systems, none of which performed wonderfully, so I checked out Jacob Sebastian’s basic Server Side Paging and Paul White’s Optimizing Paging Part 1.

    I’ve done systems similar to Jacob’s, but he had an interesting use of the OVER clause in his code. He had this code:

    ;WITH emp AS (
  SELECT 
    CASE 
      WHEN @SortOrder = 'Title' THEN ROW_NUMBER()OVER (ORDER BY Title) 
      WHEN @SortOrder = 'HireDate' THEN ROW_NUMBER()OVER (ORDER BY HireDate) 
      WHEN @SortOrder = 'City' THEN ROW_NUMBER()OVER (ORDER BY City) 
          -- In all other cases, assume that @SortOrder = 'LastName' 
      ELSE ROW_NUMBER()OVER (ORDER BY LastName) 
     END AS RecID,   , LastName
   , FirstName
   , Title
   , HireDate
   , City
   , Country
   , PostalCode
 FROM employees

    This is a great solution in SQL Server 2005. It’s much different than what I had done in SQL 2000, where I’d typically approach the problem by using the sort key to get the next page.

    So say I had this data in a table (Customers):

    CustomerID    Customer

    ———–   ————

    1             Jones

    2             Smith

    3             Johnson

    4             Allen

    5             Gates     

    Then suppose I wanted page 1, 2 results per page, ordered by Customer. I would want to see “Allen, Gates” on page 1. I’d use this code.

    select top 2 Customer

    from Customers

    Order By Customer

    If I wanted page 2, I’d go here:

    select top 2 Customer

    from Customers

    where Customer > ‘Gates’

    Order By Customer

    And this would get me “Johnson” and “Jones” since the WHERE clause would reset results. If you have control of the application code, and you can pass in the previous values, you can easily build pages like this.

    If you switch orders, say to the CustomerID, then you can easily pass that in as well, and use that for ordering.

    There is a downside, however. Any ideas? I’ll post that in my next look at paging.

  • CLR Stability

    Recently I was at a UK user group meeting and Simon Sabin of SQL Skills presented a short “SQL Nugget” on UDFs and some of the issues with performance. He then showed a better way using a TVF and mentioned that in many cases the CLR can perform on par with system functions for simple code.

    A person in the audience mentioned that it wasn’t that simple. Many companies might have concerns over deploying CLR code in assemblies to their SQL Servers. He cited that there was concern about actually having someone write “code” that runs inside SQL Server.

    I’m concerned about the CLR, but not necessarily from a stability standpoint. As much as I think there could be performance issues, I don’t think that the code written by a developer or DBA and installed in SQL Server means that the core engine is necessarily less stable. Sure there may be issues with their code, but are they more serious than the issues of crappy client code? Or poorly written queries?

    After all, we deploy code all the time on our servers, and it might be just as poorly written. I’ve seen sp_prepareXML cause issues, and I’ve seen XPs, provided by Microsoft, lower the stability of servers.

    So is it just CLR being a black box that worries people? Do they have CLR concerns about .NET assemblies being deployed on IIS?

    And if they are, then shouldn’t that mean that better training, code reviews,and extensive testing are needed?

    The CLR can be a great choice in some cases. It’s a specialized tool, one worth looking at, but not necessarily the one for all situations. Just be aware that you may have a bit of a battle if you choose to use it on your SQL Servers.

    If you have deployed the CLR, let me know how it went and what challenges you faced.

  • A Glimpse of the Future

    I’m not sure what happened here. I walked up to my desktop, wiggled the mouse, and nothing happened. In the true definition of insanity, I then did it a few more times and achieved the same result. Hitting the keyboard didn’t help and it appeared frozen.

    Weird, but no cause for concern immediately. I clicked the power button a couple times, which tends to shut things and and when that didn’t work, held it in for a power down. When the computer restarted, it came back up as though it had been in hibernate mode. I had a full desktop, which surprised me. However I was more concerned when it was operating very, very slow.

    Everything seemed to take 30-40seconds or more, even opening a mail message. Since I have a quad core machine, x64, 8GB RAM, that’s unusual. Things usually fly around the screen. Then I happened to notice a reminder pop up from Outlook that said it was 2,000-some days overdue.

    Huh? Like an episode of the twilight zone, I opened the time applet to see this:

    twilight

    I snapped a picture, changed the time back (which is interesting) and then rebooted. Things sped up and everything was back to normal, but I was a little concerned. How did my desktop move 29 years into the future? This picture still has the 2039 timestamp, so it’s always at the top of my picture list.

    Very weird. Not something I’d expect in Windows 7, but I’ll keep an eye on it. This machine has been rock solid for over six months, so I’m not looking forward to any more surprises like this.