We have a database that uses GUIDs for most of their columns in a data (non sequential). Does anyone have any best recommendations for fill factors on a GUID database in an hgih insert/update database?
Regarding maintenance windows, we have downtime every night, and are usually closed on weekends.
There really is no "ideal" Fill Factor since there are too many other factors to consider.
If you have a table with a row size of 3000 bytes, you would only ever be able to fit 2 of those rows on a data page in the first place. Setting the Fill Factor to 50 will initially build the pages out to have only 1 row per data page, but that only leaves room for one row to be added. In a high-insert environment, how long does that really last prior to a page-split occurring on that page? Probably not long, even with "random" placements due to using a GUID.
The only "ideal" here is to rebuild the tables using an INT or BIGINT key fields using an IDENTITY or SEQUENCE. If you need the GUID values for referencing externally because they are known by the app and/or customers, then there should be a single instance of them in one table, and that field should have a Non-Clustered index on it (this is known as an Alternate Key).
It might be a bunch of work, not only in terms of scripting, but possibly also of convincing the "business", and possibly some app code changes, but given that the Clustered Index key are copied into all Non-Clustered Indexes, PLUS PKs are used to Foreign Key to other tables, so now there are FK columns in tables with GUID, the entire system is slower due to needing to manage 16 byte values to find things instead of 4 or 8 byte values (INT or BIGINT respectively). Meaning, even if it is difficult politically, the end result will be a faster system, and people rarely complain about that.
But if you have no choice but to keep the GUID PKs, then you have no choice but to suffer the consequences of said situation (unfortunately).
Oh, and regarding using NEWSEQUENTIALID() those help because they are not as fragmented, but not fully sequential either since the initial value gets reset whenever the SQL Server NT service is restarted, so the new value could be "below" the existing set.
AND, even if you rebuilt all indexes every night, you would still be left with overly large Key values (again, copied into the NonClustered indexes) that take up 2x - 4x more space than the INT / BIGINT options, PER EACH TABLE, PER EACH ROW, PER EACH INDEX. The net-effect is that it takes longer to read everything from disk, longer to write things to disk, takes up more memory for all operations (since everything goes through the Buffer Pool), backups take longer, restores take longer, you can keep fewer backups (per the same storage), etc, etc. Meaning: this is not merely an issue of idealism like so many debates about which method or thingy is better; these are very real consequences if you have data volumes of anything but rather small databases.
Considerations:
Keep in mind that even though the update pattern will be sporadic, a lower fill factor can come at a cost to Page Life Expectancy, because it will take more pages in memory to return the same amount of results to the application.
Are these the clustered index keys, or just for non-clustered indexes?
