Voice of the DBA

Author: way0utwest

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

  • Enjoy Your Job

    I was listening to Mike and Mike on the radio a few Mondays ago and Mike Greenberg was returning on a Monday morning after being gone for a week’s vacation. He mentioned that he was happy to return to work and looked forward to it. Not that he didn’t enjoy vacation, but he likes his job and said he was thankful that he didn’t have a job that he dreaded returning to after time off.

    That’s how I feel, and it’s how I’ve tried to pick my jobs over the last decade or so. That wasn’t always the case, and early in my career I picked jobs that would be good for me growth and development, would pay me more, or provide some exciting challenge. I was willing to put up with long hours, or lots of demands from the company. I even took jobs with people that I didn’t like, or wouldn’t bother to ever socialize with.

    These days I feel differently, and the advice that I’ve given to people lately is “Don’t take a crappy job.” You can define that however you like, but I’d really think hard about whether you’d look forward to going to work. Whether it’s the people you want to meet or the challenges of doing the work you do, I hope that you do look ahead positively and not with trepidation.

    Life is short, and remember that when you pick the place and position where you’ll spend a lot of time in the future.

    Steve Jones

  • Common SQL Server Mistakes – GUID as a Clustered PK

    I haven’t been thrilled with GUIDs as primary keys, mainly because I think that it’s hard for humans to work with GUIDs. A GUID, or uniqueidentifier, looks like this:
    ECB6ECB4-ACCB-4382-84D1-19990D59CA2F
    Not exactly something I want to try and type or include in a query. Cut and paste works, but it’s cumbersome. Much easier for me to work with integers.
    I understand that GUIDs have some good advantages. They can reduce round trips, allowing the client to build a primary key and send it to the server. That’s a nice performance trick, and one I’d encourage.
    The real issue, however, is when you make a GUID a primary key on your table, using the defaults. Most people use the defaults, and that’s typically OK. However in this case the defaults cause a problem.
    The default setting for a primary key is a clustered index. For an integer, especially with the identity property, this is OK. All new rows are added to the end of the index, in new space allocations. This creates a hot spot for heavy insertions, but SQL Server handles those OK.
    For a GUID, if I create new rows, I get values like this. These are three new GUIDs I created on my local instance.
    ECB6ECB4-ACCB-4382-84D1-19990D59CA2F
    3406A5AE-A963-48A6-B2FC-03197DC72478
    C5D75C4F-D9EA-4355-A025-2FCC541D6E1E
    If you examine these values, you’ll see that they appear to be random. That’s OK, and it can be a good thing. But for inserting new values, that means that item 3 would be inserted before item 1, and that can cause page splits.
    Page splits are bad for performance. Data has to be moved to a new page, so not only are you inserting xx amount of data onto a page, you might be moving yyy data to a new page. It’s entirely possible that yyy > xx, which could be really bad.
    There are a number of more technical explanations in the references below, but there really is a penalty there. This is in addition to the extra space (16 bytes v 4 bytes for an int). That’s less of an issue, but it’s still an issue.
    The other thing is that all this page splitting creates fragmentation. So not only are your inserts slower, but potentially your read queries are also slower.

    What can you do?

    I think that the first thing you ought to do is read some of the articles below, and consider if you really want to use a GUID as a PK. If you do this…
    GUID_a
    then do this:
    GUID_b
    That will at least minimize some of the performance issues that you might have.
    The other thing you can do on the server, if you are generating the keys with SQL Server, you can use NewSequentialID, which should generate sequential GUIDs, in the same manner that the identity property builds sequential numbers. There are some potential issues, so don’t assume these will always be sequential, especially if you generate some on .NET, but this is better than a clustered index on a GUID.
    Be careful when using defaults, and if you use GUIDs, make sure that it is a good choice for you.

    References:

    A few posts from around the web on the issues of GUIDs as clustered primary keys.