18

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?

11
  • 2
    @mattytommo I agree. Mar 21, 2013 at 19:37
  • 2
    Are you running index defragmentation at least once a week?
    – Andomar
    Mar 21, 2013 at 19:39
  • 1
    Do you have anything that is meaningful to cluster on? Ie, what query should be fast? It's definitly not going to be range scan on the guid, so instead of just choosing an autoincrement, consider if there is some query-time optimal clustering you can choose. If not, then go ahead and use the bigint
    – carlpett
    Mar 21, 2013 at 19:41
  • 2
    @Borik Not a great idea, based on what he has and his growth rate, he would exhaust int in 4255 days (11.5 years). If he did that, he'd only blame you in 11.5 years ;) Mar 21, 2013 at 19:44
  • 1
    A contrary view: Why do you think the GUID datatype is an issue? It is a 128-bit integer. Why do you think replacing it with a 64-bit integer (bigint) or a 32-bit integer (int) is going to make a noticeable difference in speed? I think you should definitely change the clustering key to something else, to avoid all the page splitting that leads to the fragmentation, but I don't think you should change the datatype unless you are very sure that the datatype is the issue. Mar 25, 2013 at 4:54

4 Answers 4

8

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.

6
  • The only genuinely unique field in the table is the GUD - the other columns are not unique and there are combinations of columns together that may be unique to start with - but over time there is a slight chance that they would generate a duplicate record. Very remote but it is possible given the nature of the data. I have read that all other non clustered indexes reference the clustered index to improve search performance etc. Would not having a clustered PK as the GUID cause a performance impact? I am aware of the space and while a concern - performance is paramount.
    – Roddles
    Mar 21, 2013 at 20:18
  • The performance hit, if you don't specify a clustered index, is that SQL will create one behind the scenes for you and map all other indexes into that one. So, in your case, you'd get a performance improvement by letting SQL do that, because right now you're constantly shuffling all of your data around on disk to preserve the sort order when the sort order isn't important. You'll need more storage space, but will see a vast improvement in storage and minimal / no impact on retrieval. Mar 21, 2013 at 20:25
  • SO the question I guess is if I don't do the BIGINT Clustered PK, and just change the PK to a Non Clustered GUID, what are the performance implications? There are other non clustered indexes on the table which will be searched frequently. Would this impact on the performance of those searches?
    – Roddles
    Mar 21, 2013 at 22:10
  • +1 I would also suggest to stay with the GUIDs. Is very hard to replace them in distributed systems. Your big table clustered index should be evident based on how you query the data. Mar 21, 2013 at 22:30
  • 1
    Hi Guys - Just an update - I made the modifications and made the PK an Non Clustered on GUID and the SQL Server is busy inserting the 2+ million records into the database. At the same time the data was being inserted, I was able to query the database for information and the queries which at times prior to the change timed out at 10 minutes, completed in a matter of 1-2 seconds. So - making the PK non clustered and not worrying about the BIGINT appears to have worked Nicely. Many thanks for everyone's input and assistance.
    – Roddles
    Mar 22, 2013 at 6:46
1

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

*/

3
  • Interesting - and approach I had not considered - I'll examine this more closely as this looks nice and run up some test projects. If we had 150 databases generating sequential guids which are reported back to the central database, would this not still cause fragmentation as the guids would still be fairly random when inserted into the central database. Unless of course you mean drop the clustered PK and have the non clustered PK?
    – Roddles
    Mar 21, 2013 at 22:08
  • Are the 150 "remote" databases inserting one at a time? Or are they moving data in bulk-sets at night or something? So you're kinda between a rock and hard place. Using bigint will eventually run out of room (maybe) and you still have to get unique value across the many db's. So here is my radical idea. Can the 150 remote databases get their UUID's from a central service? That's one idea. Are the 150 remote databases "linked" (as in sp_addlinkedserver) to the main database? Then I have a UDF that might be considered. Let me see if I can find it. Mar 22, 2013 at 13:22
  • Here is an article that talks about sequentialid's (not related to what I already wrote, I think its interesting) codeproject.com/Articles/388157/… Mar 22, 2013 at 13:24
0

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.

-1

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

3
  • Yes - but the guid is generated at the remote 150 databases, not on SQL Server database - so I cant use sequentialguid - but thanks for the response.
    – Roddles
    Mar 21, 2013 at 20:14
  • In that case your plan in my opinion is a sound one, I have done similar thing on one of DBs that i manage, I created an INT DENTITY(1,1) and set it as Clustered PK as well as humane readable identifier for data pull up and i kept GUID(Index) as tracker to be able to trace where it originated. But My motivation was more from space saving...
    – Borik
    Mar 21, 2013 at 20:21
  • Many thanks and much appreciated for your answers and insights. :)
    – Roddles
    Mar 21, 2013 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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