question

ChaitanyaKiran-2787 avatar image
0 Votes"
ChaitanyaKiran-2787 asked DevendraSahu-0306 answered

How to find fragmentation of a single table

Good Morning,

How How to find fragmentation of a single table?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DevendraSahu-0306 avatar image
1 Vote"
DevendraSahu-0306 answered

below the step you manually find the each table

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

147342-image.png


147327-image.png



image.png (44.1 KiB)
image.png (49.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.