DB performance vs replication

Question

I am working on a db optimization (planing for future project growth) and need some help.

Currently, every table is using uniqueidentifier as PK (clustered index) and we have high index fragmentation (99%). For the new tables we started using bigint as pk but I don't want a nightmare when bidirectional replication phase comes.

I did my research and uniqueidentifier is not a huge problem (except memory), problem is clusters index on that column (http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/).

Can we do this to solve our fragmentation problem and any replication nightmare:

1. Add ROWGUIDCOL to PK column
2. Add another identity column in every table and move that cluster index (not pk) to that column

Would this new identity column cause the same replication problems as if that was bigint PK? I know SharePoint db primary keys are GUIDs but I am not happy with its performances and probably security has some part in that decision. We could reduce fragmentation by using seq. GUIDs but we can't create it on application side or return it with SCOPE_IDENTITY(). OUTPUT inserted.ID hack would be time consuming because we need to rewrite complete application DAL.

Finally, is there any valid solution for this problem? Can we use bigint without any replication problem.

Answer 1

In a previous role at a different Organisation to where I am now employed, we faced a similar decision whereby the GUID issue (as pointed out in the link) was a factor for us (both Width and poor choice regarding Index fragmentation) - but we needed to move to Transactional Replication (Peer-to-Peer) to allow our Sites to be Geographically separated.

In our Dev environment we worked through converting most of the GUID's to Identity columns, set up the replication to match the intended Production system and ensured that the Identity columns had separate ranges per node (as per http://technet.microsoft.com/en-us/library/ms146907(v=sql.105).aspx - this also gives suggestions for Automatic Identity Range Management, which may work for you).

Sounds good so far? NO...

The Identity columns were a nightmare to manage, through our Dev testing and working on automating the separation of the ranges, we gave up on them (never made it into production) - in my opinion, it is more trouble than it's worth - we settled back on the GUID's that were already in the database and settled with the known issues of a bit of wasted memory and Index Fragmentation.

For us, this wasn't that detrimental a solution - as almost all of the usage are Write operations with fairly low reads - obviously we have a maintenance plan that tries to take care of the Fragmentation as best it can (there are some Update operations - so Read and then Write but the delay is within threshold).

My 2 pence are - avoid Identity Columns (unless you already have a great plan for them - or you can add a static unit (i.e. Server 1 always has a preceding letter A1, A2 etc. Server 2 = B1, B2, etc)) when replicating and test the life out of it - assuming you can set up a replica Dev system or whatever. Of course it depends on the business whether you can put up with the poor read access that GUIDs provide because of their Index issues!

Good Luck.

Answer 2

Instead of simply eschewing GUIDs as your clustering key, why not change how you're assigning them? Are you using NEWID() to generate your key? If so, use NEWSEQUENTIALID() and you'll likely do a lot better on fragmentation.

Answer 3

Good suggestion Dave, but wouldn't that give Nihad problems with keeping the ID's unique between his Nodes:

I.e. Node one uses ID 101, Node 2 uses ID 101 before it has replicated transaction from Node one from the Shipped Transaction log (depends on setup, but in the peer to peer situation I described, it was extremely busy at all Nodes as the Client app wrote to the closest Geographical Node - add in some latency and this could happen a lot !)

I think this could be the case in Nihad's situation as he mentions requirement for Bidirectional replication and also mentions Merge in his Comment.

The beauty (and uglyness!) of the non-sequential ID is that it should always be unique (I say should because of course there is always possibility of duplication but it's slim-

There are 3.4 × 10^38 possible outputs, so even that's understated. If one distinct value was 1 centimetre, all the distinct values would equal 3.6 quadrillion (million billion) Milky Way Galaxies (assuming 100,000 light years for the size of the Milky Way). http://db-pub.com/forum-80207706/uniqueidentifier-as-a-primary-key.html

Don't forget also that a NEWSEQUENTIALID can start again from a lower range when Windows is restarted which may have a bearing:

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. http://msdn.microsoft.com/en-us/library/ms189786(v=sql.105).aspx

It also states:

Each GUID generated by using NEWSEQUENTIALID() is unique on that computer. GUIDs generated by using NEWSEQUENTIALID() are unique across multiple computers only if the source computer has a network card.

Just pointing that out as a consideration as it suggests that combining a Hardware Issue at one of the Nodes, a Network Issue and some incredible bad luck could result in duplicated ID's, maybe!

Answer 4

I did some testing last night related to this problem. My test on Sample table (2044398 record, 95% fragmentation) shows that 10,000 rows are returned in about 6s. After I rebuilded clustered index (fragmentation 0.35%) query was executed in 2.5s.
Next, I have added 10000 records to that same table and again fragmentation was 70.33 %.

Finally, same query has executed on 4 different table structures:
BASELINE
Primary KEY: TableID CLUSTERED - GUID Non-Sequence (NEWID) Fragmentation 70.33 %
(10000 row(s) affected)
SQL Server Execution Times:
CPU time = 2091 ms, elapsed time = 5164 ms.

TEST 1:
Primary KEY: TableID GUID Clustered Sequence - Fragmentation 0.69 %
(10000 row(s) affected)
SQL Server Execution Times:
CPU time = 1779 ms, elapsed time = 2450 ms.

TEST 2:
Primary KEY: TableID NONCLUSTERED key GUID Sequence - Fragmentation 0.73%
NEW identity column bigint column
(10000 row(s) affected)
SQL Server Execution Times:
CPU time = 2354 ms, elapsed time = 2140 ms.

TEST 3:
Primary KEY: TableID NONCLUSTERED key - GUID Non-Sequence (NEWID) - Fragmentation 85.88 %
NEW identity column bigint column
(10000 row(s) affected)
SQL Server Execution Times:
CPU time = 2559 ms, elapsed time = 2648 ms.

If we decide to use last table structure TEST 3 because it has relatively good performance for minimal effort, what would be the consequences for replication? For the new column will we have the same problem as if that was primary key (identity range for every server problem)?