NEWSEQUENTIALID() with EC2

  • Hello

    I have a db running on an EC2 (Amazon AWS) instance. I have a table that has a uniqueidentifier column as the primary key. There is also a clustered index on that column. To create the uniqueidentifier I use NEWSEQUENTIALID().

    This has been working great. However I just noticed that now new rows are not getting added at the end. They're getting inserted in the middle. Not good for a clustered index. After looking into it I found that the reason this is happening is because the underlying network interface has changed. When an EC2 instance gets rebooted, it will get a new network interface. Which affects NEWSEQUENTIALID().

    The reason uniqueidentifier is the primary key and not an int (and use identity) is because there are multiple dbs with the same structure that will need to be copied to a master db.

    So

    I could create my own version of NEWSEQUENTIALID(). Or I could take the clustered index off the primary key, make it a non clustered index and put the clustered index on something else. There's a column that has the timestamp of when the row was created, that may be were I'd put the clustered index.

    Has anyone run into something similar? Thoughts? Suggestions?

    Thanks!

  • cgreathouse (6/6/2013)


    Hello

    I have a db running on an EC2 (Amazon AWS) instance. I have a table that has a uniqueidentifier column as the primary key. There is also a clustered index on that column. To create the uniqueidentifier I use NEWSEQUENTIALID().

    This has been working great. However I just noticed that now new rows are not getting added at the end. They're getting inserted in the middle. Not good for a clustered index. After looking into it I found that the reason this is happening is because the underlying network interface has changed. When an EC2 instance gets rebooted, it will get a new network interface. Which affects NEWSEQUENTIALID().

    The reason uniqueidentifier is the primary key and not an int (and use identity) is because there are multiple dbs with the same structure that will need to be copied to a master db.

    So

    I could create my own version of NEWSEQUENTIALID(). Or I could take the clustered index off the primary key, make it a non clustered index and put the clustered index on something else. There's a column that has the timestamp of when the row was created, that may be were I'd put the clustered index.

    Has anyone run into something similar? Thoughts? Suggestions?

    Thanks!

    I would drop the clustered index from a uniqueidentifier column. I would put the clustered index on a different column. What datatype is your "timestamp" column? If it is a datetime that might be a good candidate for your clustered index.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • The type on the timestamp column is datetime2(2)

  • NEWSEQUENTIALID() is only sequential since Windows was started.

    If your EC2 server has been restarted, then the functions gets reseeded.

    Also, I am not sure what the behavior is for VMs, when they move from host to host (which does happen in AWS).

    I second the opinion that you should avoid GUIDS in clustered indexes, if at all possible.

  • cgreathouse (6/6/2013)


    The type on the timestamp column is datetime2(2)

    Then that column could be a good candidate for a clustered index. It would certainly be better than on a GUID column. :w00t:

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply