Voice of the DBA

Tag: sql server

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

  • Developer Deployment Frustrations

    Why don’t developers like SQL Server? Probably a few reasons, but I’m sure this is one that really frustrates them. I found a Connect Item that was titled:  Why is Deploying SQL Server 2008 R2 sooooo FRUSTRATING?!! There really is a question there, asking for guidance on  which versions of SQL Server are available and recommended for developers to include in their applications.

    When SQL Server MSDE was released, it seemed that Microsoft was looking for it to be included in small applications that might then be upsized to a Standard or Enterprise edition of SQL Server. It seems to me that this is really the market for Exsoftpress (the evolution of MSDE) and that it ought to be simple for a developer to not only deploy this with their application, but also setup basic maintenance easily.

    I sometimes think that the software developers at Microsoft get lost in their own specialty and forget just how frustrating it can be for the rest of us trying to use their product in new ways. They forget that many of us want to deploy simple solutions easily, and not spend a lot of time working out the nuances of software setup.

    I’d like to see Express not only have a very simple setup that works across multiple versions of Visual Studio, but also baic maintenance plans built in that allow full and log backups (if needed), along with index rebuilds with a simple switch set as a part of setup. A few registry keys or XML config changes could set paths or frequencies.

    Making life simpler for developers is a worthwhile investment for the SQL Server team. It makes them more likely to include it in their applications. If you can add a one-switch replication to sync to a Standard or Enterprise SQL Server, they might think Express is required in every application.

    Steve Jones

  • Virtual Labs – A Great Resource for SQL Server

    I had someone send me a note recently asking some questions about how to get set up to work with SQL Server. This was a person that had used SQL Server in the past, but had become a manager and then lost their job. So they wanted to start working with SQL Server and get a new job, however they didn’t have a server or many resources.
    My first recommendation is that you grab the SQL Server developer edition for US$50. You can get it from Microsoft, Amazon, or many other places, but this is essential. It gives you a good basic point from which to start and test features.
    However if you don’t have a spare machine, or you don’t want to put SQL Server on what you have for some reason, you have another option.
    TechNet Virtual Labs
    There are a whole variety of labs available, including a series on SQL Server 2008 and other versions. These allow you to RDP to a virtual instance of SQL Server and actually practice working on things.
    There are other labs for Windows, Exchange, etc. You can spend time working on these technologies, either guided or unguided, and get some hands on practice. You can’t necessarily save your work, but this is a great way for you to get started on some technology that you want to add to your skillset and resume.

  • Appliances – Just Add Data

    The first release of a large data appliance from Microsoft is the SQL Server 2008 R2 Parallel Data Warehouse Edition in which you buy a rack of pre-configured servers that you access with tools and perform limited management on. I was reminded of this as I read about Teradata’s new product strategy, it seemed that they have integrated an appliance in there as well.

    Appliances bring some value to customers by supposedly reducing some of the tuning, configuration, setup, etc. costs in setting up a system. Hopefully the also reduce some ongoing costs that might come from mis-configuration, things running slowly, etc. How many times has an inexperienced person setup a server in a way that causes issues for an application?

    However appliances tend to be expensive. They have to be if there is any level of support from the vendors in custom tuning. That limits how many places will purchase them, and there is also some level of distrust and fear from IT workers that worry about being displaced by a machine. Almost funny when you think about many of our systems displacing other jobs.

    Appliances haven’t had great success in the past, outside of network devices, but I wonder if we might be better off examining them more in the future. Rather than trying to have general database instances put together by IT groups, should we have small, modular appliances that handle various database services and then just write the code and supply the data. For this Friday, let me know what you think:

    Would you like to see small, modular data appliances that you connect to storage and then just add data?

    Steve Jones