SQL Server Fragmentation Problems

Question

I have a couple of tables (User & UserRecord) in my database that get extremely fragmented (like 99%) and cause the whole database and therefore the website to grind to a halt.

UserRecord is kind of like a snapshot of that user at a point in time. User is like the master record for that user. User has 0 to many UserRecords. User has around a million rows, UserRecord has around 2.5 million. These tables get written to a lot. They're also being searched a lot. They're both going to get a lot bigger. The main indexes getting badly fragmented are the primary keys of the User and UserRecord tables.

The DB is SQL Server 2012 and I'm using Entity Framework and I'm not using any stored procedures.

Tables look something like this:

USER
UserName string PK ClusteredIndex
FirstName string
LastName string
+SeveralMoreRows

USER_RECORD
UserRecordId int PK ClusteredIndex
ListId int FK(List)
UserName string FK(User) NonClusteredIndex
Community string NonClusteredIndex
DateCreated datetime
+LotsMoreRows

LIST 
ListId int PK & ClusteredIndex
Name string
DateCreated datetime

(not sure if List this is important or not but thought I'd include it as it's related to User_Record. List has 0 to many UserRecords)

We've set a SQL Maintenance plan to rebuild the indexes daily which does help, but is sometimes not enough.

A friend has suggested we use two databases, one for reading, one for writing, and we sync the read DB from the write DB. Not that I know anything about doing this, but the first problem I see with this solutation is that we need up to date data when viewing the site. For example if we update a User details or a UserRecord, we want to see those changes straight away.

Does anyone have any suggestions on how I can fix this problem before it spirals out of control?

Answer 1

Clustered indexes control the order of the data on the DISK. This is one of the main reasons why it's usually recommended that you set up a always increasing integer key to act as the clustered index. This way as more data is added to the table, they're added to the end of the currently existing data.

If it's not an autoincreasing number and new rows may contain values that would be ordered somewhere between existing values, then SQL Server will basically push the data onto the disk where it belongs (to retain the order of the clustered index key values), producing fragmentation and potentially severe overhead as IO writes further slowing down the database.

I suspect you have the same problem with your UserRecord values.

So what I would do, is add a separate clustered autoincreasing primary key to each table and rework your FK references & queries where necessary.