6

I have an existing table with about 6 million rows. The relevant columns are:

ID int not null PK
Key uniqueidentifier not null

Read/write on this table by Key lookup is probably something like 100 to 1.

The existing rows were all created using newid() so they are not in sequence. An index exists for Key.

CREATE NONCLUSTERED INDEX [idx_Robert] ON [dbo].[Aleksander]
(
    [Key] ASC
) WITH (PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , FILLFACTOR = 80
    ) ON [PRIMARY]

Given that there are already millions of rows in the table, is there any benefit to changing the GUID generation to sequential using newsequentialid()?

Going forward it would generate better data, but since it's unknown where within the index the new sequential GUID series would land, would it just screw up the index faster? Would it be better to leave the GUID generation non-sequential so that the remaining page space in the index fills more evenly?

As far as the growth rate of the table, it represents about 3 years of data.

Year    NumRows
2012    3962660
2013    1661189
2014    711241

Where 2014 is halfway through the year as of this question. There were some code changes at some point that reduced the rate of inserts, so I believe 2013 would be a typical annual row count.

  • What are you attempting to resolve by having the uniqueidentifier column sequential? Is it the clustering key? – Max Vernon Jul 29 '14 at 19:59
  • It's non-clustered. Joins happen on the integer PK, but lookup/criteria/filtering always happen on the GUID. I would like to keep the index as useful as possible for the longest time period possible, to reduce the frequency of reorg operations. Is it better to have all new values inserting into a single page in the middle of the index, and leaving the rest of the index untouched? Or equally distributing all the values across all pages of the index? – ulty4life Jul 29 '14 at 20:17
  • What type of IO subsystem is this table stored on? If it is locally attached storage on HDD's, then you could optimize index inserts and maintenance by changing to a sequential guid, however if storage is on a SAN, or locally with SSDs or some other abstraction layer, you likely won't notice a lot of difference since your read:write ratio is so heavily read-oriented. – Max Vernon Jul 29 '14 at 20:24
  • Assuming you have around-the-clock inserts, you are averaging 7 inserts per minute for 4,000,000 rows annually. This is NOT a lot of insert activity. – Max Vernon Jul 29 '14 at 20:25
  • 1
    oooooh, you lost me at 'cloud'. – Max Vernon Jul 29 '14 at 20:38
5

You may be able to reduce index rebuild/reorg times by moving to NEWSEQUENTIALID().

You are currently inserting around 4,500 rows into the table per day. The table has around 6.3 million rows at present. That's around 0.7% of the table per day. Assuming every insert on the table results in an index-page split, this will result in 9,000 pages per day written to the storage system. This will create some unnecessary I/O pressure both for inserts into the index, and for rebuild/reorgs. Switching the Key field to sequential ID generation will vastly reduce this I/O pattern.

If you switch to NEWSEQUENTIALID(), the code that performs INSERTs on the table will need to be sensitive the possibility that it will attempt to insert a duplicate Key. This is a remote possibility, however it is still a possibility, and is the same for both NEWID() and NEWSEQUENTIALID() - since nothing is truly random that is generated by a computer. Queries against the index could gain some benefit by making the index unique, as in:

CREATE UNIQUE NONCLUSTERED INDEX [idx_Robert] ON [dbo].[Aleksander]
(
    [Key] ASC
) WITH (
    PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , FILLFACTOR = 80
    ) ON [PRIMARY];

This change would make index scans less likely. Paul White has an excellent article about this at https://sqlkiwi.blogspot.com/2011/02/seeking-without-indexes.html

Since you haven't given any indication of the type of queries you run against this table, I will assume you are at some points scanning the entire index nothing about your situation. If your data is truly stored on a single spinning disk, then having your index non-fragmented should reduce the time needed for queries to return information.

Having said that, you could likely get a whole lot more for your money (time is money) by improving the IO subsystem so index rebuild/reorgs are unnecessary. See http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/ .

If you are interested in measuring the effects of the index settings you are using for the Key column, you could monitor page free percentage (among other things) for pages used by the index by using the system dmv, sys.dm_exec_index_physical_stats. For example:

SELECT o.name, ps.avg_page_space_used_in_percent, ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('KeyTable','Table'), NULL, NULL, 'DETAILED') ps
    INNER JOIN sys.objects o ON ps.object_id = o.object_id;

(You may want to adjust the parameters passed to only show the index on the Key column.)

Once the avg_page_space_used_in_percent crosses some boundary value such as 95% (or more), you would then rebuild the index, which both defragments it, and with an appropriate FILL_FACTOR setting, leaves some space free on each page for new rows with randomly generated NEWID() values.

If you did decide to use NEWSEQUENTIALID() your index could use a FILL_FACTOR of 100% since index pages would not longer be split on a frequent basis. This has the benefit of reducing the number of pages required to hold the index by 20% (against your current settings), thereby reducing the IO required to rebuild the index when that does become necessary.

Since you are only inserting new rows at a rate of about 0.07% of the total table per day, it may take a very long time before the system automatically triggers a histogram update on the statistics for the table involved. You could use the STATS_DATE() function to ensure the statistics are never more than x days old. Up-to-date statistics are vital for the query engine to produce optimal query plans.

  • Also, you may wish to look at using a fantastic maintenance solution available for free at ola.hallengren.com – Max Vernon Jul 29 '14 at 21:37
  • Isn't newsequentialid() guaranteed just as unique as newid() ? MSDN Reference "Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique." "Each GUID generated by using NEWSEQUENTIALID() is unique on that computer." – ulty4life Jul 29 '14 at 22:17
  • Notwithstanding Murphy's Law, it appears the practical possibility of a GUID collision is rather a tiny bit small. See stackoverflow.com/questions/1705008/… for some interesting discussions of this. – Max Vernon Jul 30 '14 at 3:47
-3

"…is there any benefit to changing the GUID generation to sequential using newsequentialid()?"

No.

Sequential GUIDs are only appropriate when there is a clustered index on the GUID column and you want to avoid page splits caused by inserts.

Edit to address the comment below: All nonclustered indexes suffer from page splits when data is inserted. For example, when you enter a record for Homer Simpson, it gets entered into the "S" leaf pages for the LastName index, possibly causing a page split. You don't however, require that customers join in strict alphabetical order.

Additionally, the latch system used for index leaf and non-leaf pages means that page splits require less processing time and resource than page splits on data pages.

Further to this, what would it require for the OP to change to a sequential id? They would have to replace the Default constraint on the Key column to NEWSEQUENTIALID(). This will not affect any existing rows in the table (which is good because there are foreign keys using those keys) - just the new rows. From that point on, inserted rows will have increasing keys but those increasing keys are not necessarily going to be greater than the existing data in the table (NEWSEQUENTIALID() only guarantees that the GUID is greater than any other GUID generated by NEWSEQUENTIALID() on that computer since it restarted). This means whose inserts are still going to cause page splits in the nonclustered index!

  • 2
    Clearly the index, whether it is a clustered index or not, will suffer from page splits if you're inserting NEWID() values. – Max Vernon Jul 29 '14 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.