Interview Questions
1. What all modes are available to get a report about Index fragmentation?
DMF “sys.dm_db_index_physical_stats ” has three modes -
1. DETAILED – reads all data and index pages. Be careful with using this options since it causes the entire index be read into memory and may result in IO/Memory issues
2. SAMPLED: reads 1% of the pages if more than 10,000 pages
3. LIMITED: only reads the parent level of b-tree (same as DBCC SHOWCONTIG WITH FAST). Limited option doesn’t report
page density, since it does not read the leaf level pages
Submit an interview question
Submitted questions and answers are subject to review and editing, and may or may not be selected for posting, at the sole discretion of w3Sniff.
Get Started
Comments
Leave a Comment