{"id":1299,"date":"2012-02-28T00:00:00","date_gmt":"2012-02-28T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/no-significant-fragmentation-look-closer\/"},"modified":"2021-08-24T13:40:14","modified_gmt":"2021-08-24T13:40:14","slug":"no-significant-fragmentation-look-closer","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/no-significant-fragmentation-look-closer\/","title":{"rendered":"No Significant Fragmentation? Look Closer…"},"content":{"rendered":"
\n

In this article I’ll be describing an edge case related to logical and internal fragmentation within a specific index branch that may cause performance issues, and also I’d like to contribute to the debate about the use of “global” thresholds for your maintenance plans.<\/p>\n

Let’s suppose you have a table with a structure that holds 5 rows per page and leaves almost no space to accommodate changes. After a complete index rebuild with a fillfactor of 100%, the pages would be almost full and you should see a minimal logical fragmentation in your index. Here is the script to generate the initial state of our database.<\/p>\n

Script <\/em>0<\/em>1<\/em> – Create database and tables with records<\/em><\/p>\n

CREATE DATABASE SimpleTalk\r\nGO\r\n\r\nUSE SimpleTalk\r\nGO\r\n\r\nIF EXISTS (SELECT [name] FROM sys.tables WHERE [name] = 'HistoricalTable')\r\n\u00a0\u00a0 DROP TABLE HistoricalTable\r\nGO\r\n\r\nCREATE TABLE HistoricalTable (\r\n\u00a0\u00a0 ID INT IDENTITY NOT NULL CONSTRAINT PK_ID PRIMARY KEY \r\n\u00a0\u00a0 , ColumnA VARCHAR(1590) NULL\r\n\u00a0\u00a0 , ColumnB VARCHAR(1000) NULL\r\n\u00a0\u00a0 , EventDate DATETIME NULL\r\n)\r\nGO\r\n\r\n-- Insert some records to simulate our history\r\nINSERT INTO HistoricalTable (ColumnA) VALUES (REPLICATE('SimpleTalk', 159))\r\nGO 100000\r\n\r\nUPDATE HistoricalTable\r\n\u00a0\u00a0 SET EventDate = DATEADD(HOUR, ID - 100000, GETDATE())\r\nGO\r\n\r\nALTER TABLE dbo.HistoricalTable\r\nREBUILD WITH (FILLFACTOR = 100)\r\nGO <\/pre>\n
Great! As many other DBAs would do, you take care of your indexes and deploy a maintenance plan that checks the fragmentation in your pages: If a certain threshold is met, and a 30% fragmentation is commonly mentioned, you would start a task to rebuild your indexes (let’s put reorganization aside for the sake of simplicity). Your routine runs every night and you have the time window that is necessary to accommodate the entire maintenance task.<\/p>\n
After the index-rebuild is over, a quick analysis of this index using sys.dm_db_index_physical_stats<\/strong> shows low levels of logical (ordering of pages) and internal fragmentation (page density); 0.03499% and 99.72% respectively. You can check it by running the following statement:<\/p>\n
Script <\/em>02 – Checking fragmentation<\/em><\/p>\n
SELECT *\r\nFROM sys.dm_db_index_physical_stats(DB_ID('SimpleTalk'), OBJECT_ID('HistoricalTable'), NULL, NULL, 'DETAILED')\r\nGO <\/pre>\n
A more detailed analysis would show the b-tree+ to be very well organized at the rightmost branch of the index (red branches in figure 01). We can accomplish this detailed checking by using DBCC PAGE and navigate thru the tree structure (script 02).<\/p>\n
\"1453-image001.jpg\"<\/p>\n
(Figure 01 – B-tree+ rightmost branches)<\/p>\n
Starting from the root, and verifying the page ordering at the last two non-leaf index pages, it’s possible to check that all the pages are ordered and that no logical fragmentation is seen. At the leaf level, all the pages are fully allocated with 21 bytes free (in the page header, “m_freeCnt = 21”).<\/p>\n
Script <\/em>0<\/em>3<\/em> –<\/em> Analyzing the<\/em> rightmost branch<\/em><\/p>\n
-- Get the root page for our index: 0xAA5000000100 in this sample\r\n-- Doing byte swap: 0x000050AA (page number: 20650) at file 0x0001\r\nSELECT \r\n\u00a0\u00a0 AU.root_page\r\nFROM sys.system_internals_allocation_units AS AU\r\nINNER JOIN SYS.Partitions AS P\r\nON AU.Container_id = P.Partition_id\r\nWHERE OBJECT_ID = OBJECT_ID('HistoricalTable')\r\nGO\r\n\r\n-- Checking root page and get references to the two rightmost non-leaf pages (figure 02)\r\nDBCC TRACEON(3604)\r\nDBCC PAGE ('SimpleTalk', 1, 20650, 3)\r\nGO <\/pre>\n
\"1453-image002.png\"<\/p>\n
(Figure 02 – Index root level)<\/p>\n
-- Non-leaf index page (figure 03)\r\n-- Note that all the child pages are ordered\r\nDBCC PAGE (36, 1, 20972, 3)\r\nGO <\/pre>\n
\"1453-image003.png\"<\/p>\n
(Figure 03 – Index non-leaf level)<\/p>\n
-- Non-leaf index page (figure 04)\r\n-- Note that all the child pages are ordered\r\nDBCC PAGE (36, 1, 20973, 3)\r\nGO <\/pre>\n
\"1453-image004.png\"<\/p>\n
(Figure 04 – Index non-leaf level)<\/p>\n
-- Looking at a leaf page (choose one from the level above)\r\n-- The last page should show some space left, waiting for the next inserts in the clustered index\r\nDBCC PAGE (36, 1, 40048, 3)\r\nGO<\/pre>\n
Now let’s suppose that this table hold many years of historical records and the most recent records can get updated, because that’s the way your business works. After some updates happening in the most recent records (script 04), you again check your index fragmentation and it is worse, showing 3.95% of logical fragmentation and 97.78 for page density: But those values are far from the threshold defined by your rebuild routine.<\/p>\n
Script <\/em>04 – Updates in action and new fragmentation<\/em><\/p>\n
-- Business rules and application in action\r\nUPDATE HistoricalTable\r\n\u00a0\u00a0 SET ColumnB = 'Update bigger then 21 bytes free in each page.'\r\nWHERE ID >= 98000\r\n\u00a0\u00a0 AND (ID % 5) = 0\r\nGO\r\n\r\nSELECT *\r\nFROM sys.dm_db_index_physical_stats(DB_ID('SimpleTalk'), OBJECT_ID('HistoricalTable'), NULL, NULL, 'DETAILED')\r\nGO <\/pre>\n
A small fragmentation means nothing to do, right? Not so fast…<\/p>\n
If we re-execute the same steps to analyze the rightmost branch of your index (Script 05) you will notice something very different from the first execution. Since the rows that get updated didn’t fit in the space available in each page, SQL Server has to execute a series of page splits to organize the index to respect the order of the index key.<\/p>\n
Script <\/em>05 – Analyzing the rightmost branch after fragmentation<\/em><\/p>\n
-- Checking root page and get references to the rightmost non-leaf pages (figure 05)\r\n-- Note that new pages (out of order) are shown...\r\nDBCC TRACEON(3604)\r\nDBCC PAGE ('SimpleTalk', 1, 20650, 3)\r\nGO <\/pre>\n
\"1453-image005.png\"<\/p>\n
(Figure 05 – Index root level with fragmentation)<\/p>\n
-- Non-leaf index page (figure 06)\r\n-- Note that all child pages are NOT ordered\r\nDBCC PAGE ('SimpleTalk', 1, 45464, 3)\r\nGO\r\n\r\n-- Non-leaf index page (figure 07)\r\n-- Note that all child pages are NOT ordered\r\nDBCC PAGE ('SimpleTalk', 1, 20973, 3)\r\nGO\r\n\r\n-- Looking at a leaf page (choose one from the level above)\r\n-- The page now shows some space left. In this page, 4866 bytes (m_freeCnt = 4866).\r\nDBCC PAGE ('SimpleTalk', 1, 40048, 3)\r\nGO <\/pre>\n
\"1453-image006.png\"<\/p>\n
(Figure 06 – Index non-leaf level with fragmentation)<\/p>\n
\"1453-image007.png\"<\/p>\n
(Figure 07 – Index non-leaf level with fragmentation)<\/p>\n
Checking the non-leaf level starting from row with ID 98000, we can clearly see that the logical fragmentation for this b-tree branch should be bigger than 90%, since the physical order of pages in the leaf level is not the same as the logical order. This means that the most accessed pages are out of order (potentially avoiding read aheads, but if they are hot they will be in the data cache anyway), leading to a greater logical fragmentation than represented by the DMV.<\/p>\n
Maybe this doesn’t seem that bad, but another aspect also worries me. If you check the details of a page in the fragmented part of the leaf level, it will show that, on average, only 50% of the page is used (m_freeCnt<\/strong> in the page header). Since the most accessed pages are about half empty, this means that you are wasting space in your data cache, and if your table has a significant size and the updates touches GBs of data, you are wasting half of the space used by those pages. In this case it doesn’t seem that bad due to the low number of pages, but we’re working on a small set of data, probably your SQL Server has a lot more GBs that can be wasted.<\/p>\n
You can check for the space used (and available) inside the data cache by using sys.dm_os_buffer_descriptors<\/strong>. In the script below I show the average space free (or wasted) for this databasein each page loaded in cache. This is without grouping by object, something worth monitoring in your environment.<\/p>\n
Script <\/em>06 – Checking the data cache from free space<\/em><\/p>\n
-- Clean the data cache\r\nCHECKPOINT\r\nDBCC DROPCLEANBUFFERS\r\nGO\r\n\r\n-- Bring fragmented pages to memory\r\nSELECT COUNT(*)\r\nFROM dbo.HistoricalTable\r\nWHERE ID >= 98000\r\nGO\r\n\r\n-- In average, 3895 bytes are wasted in each page.\r\nSELECT AVG(free_space_in_bytes)\r\nFROM sys.dm_os_buffer_descriptors \r\nWHERE database_id = DB_ID('SimpleTalk')\r\nGROUP BY database_id\r\nGO <\/pre>\n
To check for the real fragmentation in this branch (without using DBCC PAGE), you can re-execute the script but as well as the cluster index, you can create a filtered non-clustered index (script 07) that mimics your original data structure and check for fragmentation after all the updates are made. This non-clustered index showed me a logical fragmentation of 99.75% and page density of 50.02%.<\/p>\n
Script <\/em>07 – Filtered non-clustered index creation<\/em><\/p>\n
CREATE NONCLUSTERED INDEX idxNCL_Filtered\r\nON HistoricalTable (ID)\r\nINCLUDE (ColumnA, ColumnB, EventDate)\r\nWHERE ID >= 98000\r\nGO <\/pre>\n
Conclusion<\/h1>\n
A considerable fragmentation in specific branches of an index, not seen as a representative change in the overall index fragmentation, may be happening to your servers and frankly, you can’t always prevent those from happening. Partitioning your index and using a different fill factor for each partition would probably give you the best results, but this isn’t the main concern in this article.<\/p>\n
I want to alert you to the potential problem on relying on thresholds – like 20% or 30% – to rebuild and reorganize all your indexes. This usually won’t suffice and may lead to degradation of performance, especially for large tables; the one you normally care about the most. Even when working with partitions, keeping the data from the current year in the “hot partition” can make your year have a great start and be a problem during Christmas.<\/p>\n
In this simple case a small table shows 3% logical fragmentation and 97.7% of page density for the whole index, and that’s correct for the whole table, but for the most-used pages you would see a huge logical fragmentation and 50% of page density.<\/p>\n
That’s one of the reasons I worry when people take those thresholds as being the absolute truth and don’t think about the patterns of data access and manipulation in their own environment, not mentioning the collateral effect and real impact that those may be causing to your hot spots in the database. You, as the DBA responsible for your data should know better than anyone the behavior of your SQL Server and databases.<\/p>\n
Of course that is better having a threshold and maintenance plan than to have nothing, but as Paul Randall (said in his blog<\/a>), don’t treat those numbers as absolute truth.<\/p>\n
Take care, and remember, there is always more than meets the eye…<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
If you are relying on using ‘best-practice’  percentage-based thresholds when you are creating an index maintenance plan for a SQL Server  that checks the fragmentation in your pages, you may miss occasional ‘edge’ conditions on larger tables  that will  cause severe degradation in performance.  It is worth being aware of patterns of data access in particular tables when judging the best threshold figure to use.…<\/p>\n","protected":false},"author":121884,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[],"coauthors":[96248],"class_list":["post-1299","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1299","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/121884"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1299"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1299\/revisions"}],"predecessor-version":[{"id":84304,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1299\/revisions\/84304"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1299"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1299"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1299"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1299"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}