How How to find fragmentation of a single table?
How are things going?
DECLARE @db_id INT; DECLARE @object_id INT; SET @db_id = db_id(N'Database_Name'); SET @object_id = OBJECT_ID(N'Table_Name'); SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED'); GO
The LIMITED mode is the fastest mode and scans the smallest number of pages. For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. For a heap, the associated PFS and IAM pages are examined and the data pages of a heap are scanned in LIMITED mode.
The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. Upto 10000 pages this scans all pages.
The DETAILED mode scans all pages and returns all statistics and and is considered most heavy scan.
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
16 people are following this question.