Voice of the DBA

Author: way0utwest

  • Plan for Capacity

    In many applications, it seems that performance often falls off a cliff at some point because no one is planning for capacity increases. Everyone talks about the potential load on the system when it’s first being built. However since we often over-buy hardware, we rarely monitor the load until something goes wrong.

    I was reminded up this with a write-up from Simon Sabin recently that examined a lesson on capacity planning from FourSquare. Apparently FourSquare had some capacity issues with their MongoDB server. Despite the realization that they were growing and the addition of a second node to their database, they didn’t learn the lesson and didn’t add a third or fourth node until they were over capacity.

    Most of us don’t experience this type of hyper-growth in our systems. The majority of applications I’ve seen have undergone a quick growth in load at some point, but then leveled off to a steady, or slowly growing load on the system. However that doesn’t necessarily change the planning needed for growth. It just means that you need to grow your system at different times than if you were doubling in size every few months.

    A solid monitoring plan, and buy-in from management that you will periodically need to upgrade your servers, is necessary. Typically you will add storage over time, perhaps RAM, and potentially new CPUs or servers. You might not be able to exactly predict when you will have performance issues, but you can plan for issues. You can proactively let management know that space is running low, or that you may run into issues if data sets continue to grow in size.

    Funding is often hard to get, but by preparing people in advance that your database will need improvements over time, you are more likely to be able to respond quickly when there are performance issues.

    Steve Jones

  • Boulder SQL Server User’s Group – Common SQL Server Mistakes

    The first iteration of my presentation on Common SQL Server Mistakes, based on the various things I’ve seen in the forums over the years as well as written about on this blog, was last night.

    It went fairly well, though not a lot of questions from the group and I ended up running ahead of schedule. Afterwards we had a nice debate on triggers, which I need to blog about as well.

    Good getting up to Boulder, or Broomfield in this case, for the meeting. It’s at Level 3, which is a great facility, and I’m hoping that we can hold another event there at some point.

    I’ve uploaded the presentation to SQLServerCentral if you are interested in seeing the deck.

  • Common SQL Server – Not Indexing FKs

    This series looks at Common SQL Server mistakes that I see many people making in SQL Server.

    Foreign Keys

    It’s way too often that I see people building databases without including declared referential integrity (DRI) in their databases. Even when I see people setting a primary key on tables, it seems that often they ignore foreign keys and creating linkages between tables that link them together.

    However, even when people have declared a FK, they often don’t create an index on that column. Perhaps they assume that SQL Server will create the index like it does for PKs, but it does not.

    If I create these two tables and join them with a FK:

    CREATE TABLE [dbo].[Products](
        [ProductID] [int] NOT NULL,
        [ProductName] [varchar](50) NULL,
    CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
    (
        [ProductID] ASC
    )
    ) ON [PRIMARY]

    GO
    CREATE TABLE [dbo].[ProductDetails](
        [ProductDetailID] [int] NOT NULL,
        [ProductID] [int] NULL,
        [SKU] [varchar](50) NULL,
        [Price] [numeric](18, 2) NULL,
    CONSTRAINT [PK_ProductDetails] PRIMARY KEY CLUSTERED
    (
        [ProductDetailID] ASC
    )
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[ProductDetails]  WITH CHECK ADD  CONSTRAINT [FK_ProductDetails_Products] FOREIGN KEY([ProductID])
    REFERENCES [dbo].[Products] ([ProductID])
    GO

    ALTER TABLE [dbo].[ProductDetails] CHECK CONSTRAINT [FK_ProductDetails_Products]
    GO

    If I go and check indexes on ProductDetails, I find that there is only one index, the index for the PK.

    FKIndex_a

    Why is this a problem? It’s because of performance. We should realize that indexes speed up performance by reducing the amount of work that SQL Server has to do.

    With FK columns, what I’ve often found with child tables is that I know the value of the FK column I am searching for and don’t need to join with the parent table. However without an index on the FK column, this query requires a table scan.

    select
    sku
    , price
    from ProductDetails pd
    where pd.ProductID = 3

    If you are creating FKs in your database, don’t forget to index them where appropriate.

    Auto Creation

    I’ve seen some people ask why SQL Server doesn’t automatically create indexes on those FK columns. I am torn on this, but I like the 80/20 rle. If 80% of the tables would benefit from it, I think it should be done. I am leaning towards some intelligent mechanism to do this.

    The main issue is that you might not want just an index on the FK column. You might want some sort of covering index that includes columns in addition to the FK column to prevent key/bookmark lookups to the clustered index. If you can avoid those, you can drastically increase performance.

    There is also the chance that with your query load, you never use these indexes. That can be horrible for performance as well since there is overhead to maintain these indexes on all insert/update/delete operations.

    The Advice

    Look at the queries that are coming into your database. Check the missing index DMVs and if you find that the FK columns are being used, index them.

    If you’re not sure, or don’t know how to look for missing indexes, here’s a reference.

  • Database Mirroring Connection Strings – Automatic Failover

    I saw a post recently from Hugo Shebbeare that reminded me of something that I’ve seen asked often on the forums. One thing that I preach to people is that they should use their blog to show what they know, and in this case I want to do that. Also, I remind people to give credit to the inspiration, as I’ve done with the link to Hugo above.

    Database mirroring has automatic failover if you have newer SQL Server clients. Those of you with Vista or Windows 7 should be fine, though XP might need an update. SQL Server 2005 started distributing a client that would handle automatic failover, and it does this through the connection string. Here’s a typical one:

    Server=DBServer01;Database=Sales;Connection Timeout=30;Integrated Security=SSPI;Failover Partner=DBServer02

    In this string we have our main database instance (DBServer01) and the database (Sales). Our mirror server is called DBServer02, and the database name on this server would be the same. Note that you can use IP or named instances as in DBServer01\Sales as well for the connection strings.

    If the client has issues connecting to the primary, when the Connection Timeout passes, it will try to connect to the mirror server and start working there. Note that if you had a transaction in progress when the failure occurred, your application has to reconnect to the other server and resubmit the batch.