0

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.

  • What type of replication are you using? Merge? Perr-to-Peer? Uniqueidentifiers as a primary key should not be a big deal in terms of fragmentation, given that you can guarantee that the GUIDS are sequential. What's the bigint nightmare you're talking about? You mean range allocation, right? – spaghettidba Nov 12 '14 at 10:11
  • It would be great to have Merge replication so we can have multiple working db instances to share a load. Essentially, we want to accomplish improve scalability and availability. With current implementation we can't guarantee that GUIDS are sequential without major DAL rewrite. I am new to replication so any advice would be good. As I can see from this article msdn.microsoft.com/en-us/library/ms152543.aspx it can be tricky to implement replication with identity columns and from what I have read general accepted rule is to use GUIDs with replication. – Nihad Nov 12 '14 at 12:12
  • I'm afraid you want to use replication for the wrong reasons. Merge replication will likely slow down your database because it introduces triggers on all published tables. Moreover, replication is a data distribution technology, not something you want to use for high availability. It will likely not help you scale out the load either. I strongly suggest that you rethink your strategy. – spaghettidba Nov 12 '14 at 13:42
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.

0

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.

  • We can't use newsequenceid() because it can't be generated before insert, we are using c# Guid generation. We would have to use output.inserted for every insert and that means major refactoring of c# side. – Nihad Nov 12 '14 at 18:36
  • Another thing you could try which would be in the same vein would be to have the C# developers generate per-node sequential IDs using the UuidCreateSequential API. It should be a minor code change for them to implement. You'd still have different sequences per node, but since you have a relatively small amount of nodes, your fragmentation would be greatly lessened. stackoverflow.com/questions/211498/… – Dave Markle Nov 12 '14 at 18:40
  • If we decide to use c# method for sequence GUIDs, should we worry about Guid repetition after restart? Additionally, with sequence GUIDs implementation we need to rebuild existing fragmentwd cluster indexes and that will fix the problem? – Nihad Nov 12 '14 at 19:09
  • I would not worry about that. As MHSQLDBA said, you're only going to possiblyy run into collisions if you have two computers without NICs, which by definition you won't have. You should rebuild your fragmented indexes once, and you should find that they will not fragment like they do now. This is what I would try first as it requires the least amount of effort. – Dave Markle Nov 12 '14 at 19:56
0

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!

0

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)?

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.