Alter the Fill Factor on all GUID Columns
This is somewhat one of my favorite subjects because you see, GUIDs suck in SQL Server land.
Developers love them because they make the id's of related records easy
to determine - because the developer can generate the GUID for a header record, insert the record and then insert a detail record with the
header's GUID (because they we both generated in app-space).
But on the flip-side, they are bad news for your fledgling SQL Server database. Besides being 16-bytes and storage heavy (and therefor also IO heavy), and besides being 36 characters and impossible to remember/note-down they are also random and (inherently) non-sequential. This means that they cause significant index fragmentation and split pages all to hell.(Now don't get me wrong, they are perfectly acceptable for small applications/databases - just not for medium to large implementations).
Fortunately, if you've fallen into this pit - there are steps you can
take other than ripping them out and replacing them with their
high-performing counterpart (integers). You can slightly decrease the
fill factor, thereby leaving a bit of space in your pages for these
random monsters to slip in without the need to relocate page-data.Before decreasing the fill factor, I'd recommend:
- Taking a baseline of the page-splits that your database is experiencing.
- Decreasing the fill factors on your GUID columns by 5 to 10.
- Re-running your baseline to determine if your splits have decreased.
- Repeat (See #1), until your page splits have drastically decreased.
This handy script will generate "index alter" scripts for your GUID indexes, but will not directly modify your database.
'ALTER INDEX ' + Ind.name
+ ' ON ' + Schema_Name(Obj.schema_id)
+ '.' + Obj.name
+ ' REBUILD WITH (FILLFACTOR = 90);',
Ind.Name AS [IndexName],
Ind.fill_factor AS CurrentFillFactor
sys.objects AS Obj
INNER JOIN sys.indexes AS Ind
ON Ind.object_id = Obj.object_id
sys.index_columns as IdxCols
INNER JOIN sys.columns as Cols
ON Cols.object_id = IdxCols.object_id
AND Cols.column_id = IdxCols.column_id
Lower(Type_Name(Cols.system_type_id)) = 'uniqueidentifier'
) AS GUIDColumns
ON GUIDColumns.object_id = Obj.object_id
AND GUIDColumns.index_id = Ind.index_id
Obj.Type = 'U'
AND Obj.is_ms_shipped = 0
--AND Ind.type_desc = 'CLUSTERED' --Optionally, only alter (or do not alter) clustered indexes.
Is this code snippet, product or advice warrantied against ill-effect and/or technical malaise? No. No it's not! Not expressed - Not implied - not at all.