How to move away from guid fields as primary keys

Question

I am in a big mess and see if I can get out of it. I have a database with all the primary keys in the tables defined in the uniqueidentifier columns. This was imposed on us, giving "security" as one of the reasons. Another, in my opinion, relatively worthy reason is that some of the tables are involved in replication.

I am browsing the database and I feel that one easily avoidable future performance bottleneck is the ability to add bigint increment bigint columns in all tables and make them primary keys (clustered). And somehow “attach” the pk-fk relationship properly. But keep the old columns for future reference.

Any suggestions / comments / denunciations in this regard? Our C # / MSSQL Server R2 / Linq environment.

EDIT: Looking at the comments, I realize that I left a few important details. All primary key fields of Guid are Clustered, and I do not use newsequentialId (we use Linq to SQL, primary keys are generated on the client side. When using replication, we were not sure about the ability to correctly generate sequential identifiers from different client environments without conflicts).

My "feeling" is due to the well-known fact that a clustered index in the guid column will lead to a high degree of fragmentation and will only worsen the situation as the database grows.

Also, I'm not trying to optimize now, but trying to fix a bad design to avoid future headaches when the database gets too big. I wanted to know if it's worth it now.

A useful discussion is also related to the problem here in this post , and more

Answer 1

Tuning database performance is actually simple, but it is difficult. First, you need to compile a list of statements that execute in the database, running a long profile for at least one business day, but ideally two.

Save this profile in the database so that it can be requested, and you can easily find the DISTINCT queries that are executed in your database.

After identifying those that are most executed, analyze their execution plans, probably it has nothing for the GUID and everything to do with the requests themselves (i.e. they are just awful), or you need a different index.

Beware:

• Views that are heavily filtered using the WHERE . These are fantastic candidates for stored procedures or parameterized views.
• Statements that JOIN for very large tables can sometimes be good candidates for subqueries. It depends on the implementation plan.
• Statements that appear are executed several times. This is often a good sign that the application itself simply cannot cope with how often it makes circular trips to the server. I saw applications that will run the same request 10 times.

Answer 2

A stop measure can be the use of NEWSEQUENTIALID () instead of NEWID (). At least this way you won’t get so much fragmentation.

Creates a GUID that is larger than any GUID previously generated by this function on the specified computer since Windows started. After restarting Windows, the GUID may start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using the NEWSEQUENTIALID may be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to populate data and index pages completely.

http://technet.microsoft.com/en-us/library/ms189786.aspx

Please note that this will not necessarily help solve your performance problems depending on what they are (maybe you can clarify - INSERT? SELECT filtering, etc.).

Answer 3

Take a look at Kimberly Tripp's advice http://www.sqlskills.com/blogs/kimberly/disk-space-is-cheap/

She recommends the following steps to convert to a key that does not contain a GUID:

If your CL key is PK, then follow these steps:

 Take the database offline (sorry, I'm just the messenger!!) Disable the FKs Disable the nonclustered indexes Drop the clustered PK (alter table) Optionally, add an identity column? Create the new clustered index Create the PK as nonclustered Enable the nonclustered indexes (alter index…rebuild) Enable the FKs with CHECK (this is very important) Bring the database online