New answers tagged fragmentation
What does Fragmentation Means in a Heap The fragmentation value in Heap which you get from column avg_fragmentation_in_percent by querying sys.dm_db_index_physical_stats DMV states that Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit. Further the same BOL says that This is the percentage of ...
I believe completely wiping out the data and doing an insert would be faster. As each update would require a DB scan to find the matching record and update it.
Since you are indicating insert performance is the primary concern, I'd take the recommendation of the DBA and make the clustering key the identity column since it is a unique, monotonically ascending number, which is guaranteed to (almost) never cause page-splits on the table. Also, don't store the GUID in an NVARCHAR(100) column, use the data type ...
For everyone interested, I have created a chart showing the index REBUILD duration of about 2500 index rebuilds within couple of weeks in relation to the fragmentation of the index and it's size in pages. This data is based on 10 SQL Servers, hundreads of tables and on Ola Hallengren's optimizing procedures. The general threshold for rebuilding is set to ...
Top 50 recent answers are included