jarmendadmin - Monday, December 4, 2017 10:49 AMHi everyone,I am fighting with a SQL Server 2014 table [IR_RecordingMedia] (5.5GB data, 6.8GB Index, 11.6 million rows) that is running well, but my vendor for the software using the table says it needs to be reindexed due to results of the following query showing >98% avg_fragmentation_in_percent. I have SSMS maintenance plans re-indexing this table and SSMS properties show <10% for this same IR_RecodingMedia table's indexes. I have read a lot of articles, but most point to the issue being tables with low page count where reindex has no impact, but my table has 5,995 pages on the PK_IR_RecordingMedia clustered index as shown in query results. Please provide any info on how I can lower the avg_fragmentation_in_percent value of this table's indexes or am I trying to acheive the impossible? Thanks for any help!
Select object_name(b.object_id) as Table_Name, D.name as index_name, A.index_type_desc,A.avg_fragmentation_in_percent, A.fragment_count, A.avg_fragment_size_in_pages, A.page_count
From sys.dm_db_index_physical_stats
(DB_ID(N' I3_IC'), NULL, NULL, NULL, 'LIMITED') A
INNER JOIN sys.objects B ON A.object_id = B.object_id
INNER JOIN sys.indexes C ON C.object_id = B.object_id AND C.index_id = A.index_id
INNER JOIN sys.sysindexes D ON D.id = B.object_id AND D.indid = A.index_id
ORDER BY A.avg_fragmentation_in_percent DESC
According to the table name of "IR_RecordingMedia", this appears to be for an "I3" telephone system and it looks like it might be for version 4 (ICV4 is the change if I recall).
Unless I've missed my guess, the PK for that table is on the "RecordID" column and its datatype is UNIQUEIDENTIFIER... which means it uses a GUID. As much as I hate GUIDs for PKs, they actually do work very well for inserts being fast because they avoid the "hot spot" that tables with an "ever increasing" PK provide. Logical fragmentation also doesn't matter so much on today's large memory systems and disks, especially since there are so many users and the Read/Write heads are bouncing all over the disk anyway.
FYI, there are also two types of fragmentation... logical, which is what you folks are looking at, and physical, which is the only important part on such a system because, the rows in this table are generally accessed only one at a time whether it's for a query (Select), and INSERT, or an UPDATE. Further, be advised that with such a low rowcount on a GUID PK, it doesn't take many inserts to very quickly reach 99.9999% logical fragmentation and it's harmless in this case especially since you page fullness (the physical fragmentation I spoke of) is at 94%. If you let this go for another 3 or 4 years (about the same amount of time since we upgraded to ICV4 and haven't ever rebuilt indexes on this box), the PK will "settle out" at about 69-70% page fullness which is exactly what you'd want to set your FILL FACTOR to if you made the mistake of rebuilding the index.
I also just happen to be conducting experiments using 3.6million individually inserted GUIDs using various FILL FACTORs and have proof that this matters little. Yes, you can make it so it almost never does another unexpected page split (rebuild at 70% Fill Factor, defrag at 5% logical fragmentation or at the very first sign of additional pages being added) but the number of page splits per day are miniscule even in my environment where we handle about 4000 calls per day. My experiments use 10,000 inserts per logical day and they're miniscule there, as well.
The bottom line here is that having a logical fragmentation of 99.9999% on this index is NOT a problem and you shouldn't waste your time either rebuilding it or reorganizing it and you have the proof of that... as you said in your original post, "that is running well".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)