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?
intin 4255 days (11.5 years). If he did that, he'd only blame you in 11.5 years ;)