Home
  Home
Home
Search
Articles
Page Tag-Cloud
  Software
Software Tag-Cloud
Submit Patch
Building from Source
Open Source Definition
  Popular Tags
C++
Source Code
Class
Legacy
Console
  Members
Login
Web-Email
Notable Members
  Official
Our Company
Copyright Information
Software EULA
GPL EULA
LGPL Eula
Pre-Release EULA
Privacy Policy
  Support
Make Contact
 
 
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:
  1. Taking a baseline of the page-splits that your database is experiencing.
  2. Decreasing the fill factors on your GUID columns by 5 to 10.
  3. Re-running your baseline to determine if your splits have decreased.
  4. 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.


SELECT
	'ALTER INDEX ' + Ind.name
		+ ' ON ' + Schema_Name(Obj.schema_id)
		+ '.' + Obj.name
		+ ' REBUILD WITH (FILLFACTOR = 90);',
	Obj.*,
	Ind.Name AS [IndexName],
	Ind.fill_factor AS CurrentFillFactor
FROM
	sys.objects AS Obj
INNER JOIN sys.indexes AS Ind
	ON Ind.object_id = Obj.object_id
INNER JOIN
	(
		SELECT DISTINCT
			IdxCols.object_id,
			IdxCols.index_id
		FROM
			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
		WHERE
			Lower(Type_Name(Cols.system_type_id)) = 'uniqueidentifier'
	) AS GUIDColumns
	ON GUIDColumns.object_id = Obj.object_id
	AND GUIDColumns.index_id = Ind.index_id
WHERE
	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.




Tags:
 Fill Factor    GUID    Index    Performance    Snippet    SQL Server    Tuning  

Created by Josh Patterson on 2/9/2013, last modified by Josh Patterson on 2/17/2013

No comments currently exists for this page. Why don't you add one?
First Previous Next Last 

Login or signup to leave a comment.
 
Copyright © 2014 NetworkDLS.
All rights reserved.
 
Privacy Policy | Our Company | Contact