Best solution to fixing database design with GUID as primary key

Question

I am after some confirmation of this idea to fix a badly performing database or a better suggestion if any one has one. Always open to better suggestions.

I have a very large database (20+ million records growing by about 1/2 million per day) which are using GUID as PK.

An oversight on my part but the PK is clustered on SQL server and is causing performance issues.

The reason for a guid - this database is partially synchronised with 150 other databases so the PK needed to be unique. The synchronisation is not managed by SQL Server, rather there is a custom process built which keeps the data in sync for the requirements of the system - all based on that GUID.

Each of the 150 remote databases don't store the full data as stored in the central SQL Database. they only store a subset of the data they actually require, and the data the require is not unique to them (10 out of the 150 database may have some of the same records from other sites databases for example - they share). Also - the data is actually generated at the remote sites - not at the central point - hence the need for the GUIDs.

The central database is used not only for keeping everything in sync, but queries from 3000+ users will be executed against that very large fragmented database. Already this is a big problem in initial testing.

Fortunately we are not live yet - so I can make changes and take things offline if required which is at least something.

The performance of the remote databases is not a problem - the data subsets are pretty small and the database usually never gets above 1GB in size in total. The records are fed back to the main system quite regularly and removed from the smaller BD's when no longer required.

The performance of the central DB which is the keeper of all records is woeful - due to a clustered GUID as a primary key for that many records. The index fragmentation is off the charts.

So - my thoughts to fix the performance issue is to Create a new column - Unsigned BIGINT IDENTITY(1,1) and then change the Clustered PK of the table BIGINT column.

I would create a Unique Non Clustered index on the GUID field which was the primary key.

The smaller remote 150 databases don't need to know about the new PK on the Central SQL Server database - its purely going to be used for organising the data in the database and stop the bad performance and fragmentation.

Would this work and improve the performance of the central SQL database and prevent future index fragmentation hell (to a degree of course)? or have I missed something very important here which is going to jump up and bite me and cause even more grief?

Answer 1

You certainly need NOT to cluster on the GUID. If you have something which would allow you to uniquely identify records other than that GUID, I'd suggest you look at building an unique index on that other field & making that index clustered. If not, you are free to cluster on other fields, even using nonunique indexes. The approach there would be to cluster however best facilitates splitting your data up and querying - so, if you've got a "region" field, or something, that might be a candidate for your clustering scheme.

The problem with changing to a BIGINT would be additions to the data from other databases & integrating their database into the central store. If this is not a consideration - and will never be a consideration - then, yes, the BIGINT would solve the index rebalancing issue nicely.

Behind the scenes, if you don't specify a clustered index, SQL Server does much the same thing: it creates a row ID field & maps all other indexes into that. So, by doing it yourself, you're solving it just as SQL would solve it.

Answer 2

That's a tall order.

Let me suggest a middle man approach.

I was having issues with System.Guid.NewGuid() generating random guids. (I was allowing the client to create their own guid, instead of relying on the database to create a sequentialid).

Once I moved to a UuidCreateSequential on the client side, my performance became MUCH better, especially on INSERTs.

Here is the DotNet client code voodoo. I'm sure I pawned from somewhere:

using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;


namespace MyCompany.MyTechnology
{
  public static class Guid
  {


    [DllImport("rpcrt4.dll", SetLastError = true)]
    static extern int UuidCreateSequential(out System.Guid guid);


    public static System.Guid NewGuid()
    {
      return CreateSequentialUUID();
    }


    public static System.Guid CreateSequentialUUID()
    {
      const int RPC_S_OK = 0;
      System.Guid g;
      int hr = UuidCreateSequential(out g);
      if (hr != RPC_S_OK)
        throw new ApplicationException("UuidCreateSequential failed: " + hr);
      return g;
    }


  }
}














    /*

Original Reference for Code:
http://www.pinvoke.net/default.aspx/rpcrt4/UuidCreateSequential.html


*/

/*



Text From URL above:

UuidCreateSequential (rpcrt4)

Type a page name and press Enter. You'll jump to the page if it exists, or you can create it if it doesn't.
To create a page in a module other than rpcrt4, prefix the name with the module name and a period.
. Summary
Creates a new UUID 
C# Signature:
[DllImport("rpcrt4.dll", SetLastError=true)]
static extern int UuidCreateSequential(out Guid guid);


VB Signature:
Declare Function UuidCreateSequential Lib "rpcrt4.dll" (ByRef id As Guid) As Integer


User-Defined Types:
None.

Notes:
Microsoft changed the UuidCreate function so it no longer uses the machine's MAC address as part of the UUID. Since CoCreateGuid calls UuidCreate to get its GUID, its output also changed. If you still like the GUIDs to be generated in sequential order (helpful for keeping a related group of GUIDs together in the system registry), you can use the UuidCreateSequential function.

CoCreateGuid generates random-looking GUIDs like these:

92E60A8A-2A99-4F53-9A71-AC69BD7E4D75
BB88FD63-DAC2-4B15-8ADF-1D502E64B92F
28F8800C-C804-4F0F-B6F1-24BFC4D4EE80
EBD133A6-6CF3-4ADA-B723-A8177B70D268
B10A35C0-F012-4EC1-9D24-3CC91D2B7122



UuidCreateSequential generates sequential GUIDs like these:

19F287B4-8830-11D9-8BFC-000CF1ADC5B7
19F287B5-8830-11D9-8BFC-000CF1ADC5B7
19F287B6-8830-11D9-8BFC-000CF1ADC5B7
19F287B7-8830-11D9-8BFC-000CF1ADC5B7
19F287B8-8830-11D9-8BFC-000CF1ADC5B7



Here is a summary of the differences in the output of UuidCreateSequential:

The last six bytes reveal your MAC address 
Several GUIDs generated in a row are sequential 
Tips & Tricks:
Please add some!

Sample Code in C#:
static Guid UuidCreateSequential()
{
   const int RPC_S_OK = 0;
   Guid g;
   int hr = UuidCreateSequential(out g);
   if (hr != RPC_S_OK)
     throw new ApplicationException
       ("UuidCreateSequential failed: " + hr);
   return g;
}



Sample Code in VB:
Sub Main()
   Dim myId As Guid
   Dim code As Integer
   code = UuidCreateSequential(myId)
   If code <> 0 Then
     Console.WriteLine("UuidCreateSequential failed: {0}", code)
   Else
     Console.WriteLine(myId)
   End If
End Sub




*/

ALTERNATE IDEA:

If your main db and remote db's are "linked" (as in, sp_linkserver)......then you could use the main db as the "uuid generator".

You don't want to get uuid's "one by one", that's too much chattiness.

But you could grab a set of uuid's.

Below is some code:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
 OBJECT_ID(N'[dbo].[uspNewSequentialUUIDCreateRange]') AND type in (N'P',
 N'PC'))

 DROP PROCEDURE [dbo].[uspNewSequentialUUIDCreateRange]

 GO



 CREATE PROCEDURE [dbo].[uspNewSequentialUUIDCreateRange] (

 @newUUIDCount int --return

 )

 AS

 SET NOCOUNT ON

 declare @t table ( dummyid int , entryid int identity(1,1) , uuid
 uniqueidentifier default newsequentialid() )

 insert into @t ( dummyid ) select top (@newUUIDCount) 0 from dbo.sysobjects
 so with (nolock)

 select entryid , uuid from @t

 SET NOCOUNT OFF

 GO

/*

--START TEST

 set nocount ON

 Create Table #HolderTable (entryid int , uuid uniqueidentifier )

 declare @NewUUIDCount int

 select @NewUUIDCount = 20

 INSERT INTO #HolderTable EXEC dbo.uspNewSequentialUUIDCreateRange
 @NewUUIDCount

 select * from #HolderTable

 DROP Table #HolderTable

 --END TEST CODE

*/

Answer 3

Based on your description, go with BIGINT. However, the index for GUID can be non-unique, since GUIDs are supposed to be globally unique anyway.

Answer 4

If GUID is stored properly as uniqueidentifier should not have any performace issues... and if you can use Sequential GUID even better...

Also @mattytommo has Good point about 11.5 years with using INT...