Voice of the DBA

Tag: syndicated

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

  • Copyright

    This is actually amazing. A talk on what YouTube does with copyright and how they view it, from TED. It’s interesting, and there are two things in there to watch. Note, that I caught this link from Jeff Atwood (@CodingHorror) on his blog.

    The first thing is that YouTube scans over 100 years of video every day. 100 years! That’s freaking amazing. That is an incredible amount of video processing that’s taking place, and a low of power being spent to enforce copyright. Forget about the legal issues for a minute and think about the technology. That is just amazing.

    The second thing in the talk is that content owners can, and are choosing, to not block this content, but rather use it to generate some benefits from them. They ought to be able to receive some benefits, and I agree, but knocking down some kid’s video because he used a copyrighted song seems silly. Especially as in the case of the wedding video, you never know what will go viral and what benefits you’ll get. Including selling more copies of your stuff.

    However the thing that worries me is that we are now not looking out for Fair Use. If a copyright holder wants to block their content, can we still get our 30sec of use of it in a video we make? Or a mashup?

    7231[1] The copyright office doesn’t necessarily define the fair use guidelines, but I think they need to. We ought to have some idea of what portion of audio, video, imagery, and text can be used, with citation. Even with automatic citation, that may benefit the copyright holder.

    We publish books here through Simple Talk Publishing. We typically offer books for free as marketing materials here on the site, and also sell them on Amazon and other retailers. Regularly I find these republished, or even for sale, on the Internet on other sites. I understand people wanting to share, but ultimately it’s a problem for us if all our books become too widely distributed for free. I know there are a lot of debates on piracy, and I’m not completely sure where I stand. I don’t actively worry about piracy, but I do act to have things removed when I find it.

    We need to do something here to both protect the copyright holders, but also allow people to reuse ideas and build on them. I certainly wouldn’t mind someone republishing portions of my work, or building their own knowledge publications based on it, just don’t wholesale copy what I’ve done without giving me any credit.

    I think YouTube has a good balance here, though I’d like to see more efforts from content owners to share and allow others to build on their work.

  • Kindle v iPad

    I don’t have either, but I ran across this comparison on the devices. It says get both, if you’re a consumer of media and culture. To some extent I do think that it’s got a perspective that makes sense, and if you can afford a $500 iPad (or more), then you can probably add in a $140 Kindle without worrying about it.

    I had a Gen 1 Kindle, and I have an iPhone that is my primary way to read. I read on flights, and while I do take breaks and play some games, I think it’s a matter of attention span. Books that are really, really good hold my attention, and I won’t move on to anything else.

    Books that I struggle with, or require more thinking, I switch off at times. When I read business books, I sometimes need to think about something, so I’ll close the Kindle app for a minute, maybe play a game, and let my mind wander with what I’ve read.

    There definitely is a difference in reading on an LCD and e-ink. It doesn’t bother me, or it doesn’t appear to, but I understand that it might bother some people, and if that’s the case, a Kindle/Nook/eReader makes more sense.

    I’m not sure if I’ll get an iPad. It has to function in my life and add something to it, and I’m not sure it’s that much better than an iPhone. Talking with someone recently that carries an iPhone, iPad, and laptop, they tended to use the iPad regularly, but it didn’t remove the need for the other devices. We’ll see if I make a case in my mind to get one.

    However you do it, go read. It’s good for you.