How to find fragmentation of a single table

Question

Good Morning,

How How to find fragmentation of a single table?

Answer 1

Hi ChaitanyaKiran-2787,
How are things going?

Please try:

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.

Best Regards,
Amelia

---

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.

Answer 2

below the step you manually find the each table

Go to table expend table-> Expend Indexes-> Right Click->
Go to Rebuild or Reorganize

Answer 3

sys.dm_db_index_physical_stats