August 24, 2012 at 12:13 pm
Recently, I inherited a server where an existing maintenance process only looks at index_level 0 to determine if a reorg\reindex is required. The database has dozens of tables where index_level zero doesn't meet the threshold (10%) but index_level 1 does and its page_count > 1000 (see attached graphic).
I've done some reading but haven't found a definitive answer if fragmenation at index_level 1 and beyond impacts performance.
I'd appreciate any input or links on the topic.
August 24, 2012 at 12:22 pm
Maybe. Not likely, but it is possible.
Requires that the table get large enough range scans that the read-ahead has to drive the scan from two levels up the index (probably a full table scan) and that the upper levels of the index not be in memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply