Bobcares

For every $500 you spend, we will provide you with a $500 credit on your account*

BLACK FRIDAY SPECIAL

*The maximum is $4000 in credits, Offer valid till November 30th, 2024, New Customers Only, Credit will be applied after purchase and expires after six (6) months

For every $500 you spend, we will provide you with a $500 credit on your account*

BLACK FRIDAY SPECIAL

*The maximum is $4000 in credits, Offer valid till November 30th, 2024, New Customers Only, Credit will be applied after purchase and expires after six (6) months

How To Check Index Fragmentation In SQL Server | 3 Methods

by | Jun 7, 2023

Let’s explore the methods on how to check index fragmentation in sql server. Bobcares, as a part of our Server Management Service offers solutions to every SQL query that comes our way.

How To Check Index Fragmentation In SQL Server?

The tables and indexes of databases become fragmented over time as a result of records being added, updated, and deleted. This fragmentation may cause the SELECT queries as well as the INSERT, UPDATE, and DELETE operations to perform poorly.

how to check index fragmentation in sql server

Method 1

By running a query on the built-in sys.dm db index physical stats DMV, we can discover index fragmentation. We must combine the query to additional DMVs like sys.tables and sys.indexes in order to obtain understandable, practical data.

The easy query shown below will list the indexes, record counts, and fragmentation percentage for each table in a database.

USE YourDatabase

GO

SELECT s.[name] +‘.’+t.[name]  AS table_name

,i.NAME AS index_name

,index_type_desc

,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent

,record_count AS table_record_count

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘SAMPLED’) ips

INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]

INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]

INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)

ORDER BY avg_fragmentation_in_percent DESC

Method 2

Run the script below to get the index fragmentation for the X3 db. We must insert the code into a new query in SQL Server Management Studio and direct the db connection to the X3 db.

SELECT
 Database_ID,
 CONVERT(VARCHAR(35),DB_NAME(idx.database_id)) AS [DB Name],
 sch.name [Schema Name],
 idx.[Object_ID],CONVERT(VARCHAR(35),OBJECT_NAME(idx.[OBJECT_ID])) AS 'OBJECT NAME',
 idx.Partition_Number [Partition Number],
 idx.Index_ID [Index ID],
 idx.Index_Type_Desc [Index Type Desc],
 idx.Alloc_Unit_Type_Desc [Alloc Unit Type Desc],
 idx.Index_Depth,Index_Level [Index Depth Level],
 idx.Avg_Fragmentation_In_Percent [Avg Frag Percent],
 idx.Fragment_Count [Frag Count],
 Avg_Fragment_Size_In_Pages [Avg Frag Size In Pages],
 idx.Page_Count [Page Count],
 idx.Avg_Page_Space_Used_In_Percent [Avg Page Space Used In Percent],
 idx.Record_Count [Record Count],
 Ghost_Record_Count [Ghost Record Count],
 idx.Version_Ghost_Record_Count [Version Ghost Record Count],
 idx.Min_Record_Size_In_Bytes [Min Record Size In Bytes],
 idx.Max_Record_Size_In_Bytes [Max Record Size In Bytes],
 idx.Avg_Record_Size_In_Bytes [Avg Record Size In Bytes],
 Forwarded_Record_Count [Forwarded Record Count]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') idx
 INNER JOIN sys.tables t
 ON idx.object_id = t.object_id
 INNER JOIN sys.schemas sch
 ON sch.schema_id = t.schema_id
 WHERE idx.page_count > 100
 AND idx.avg_fragmentation_in_percent > 15
 AND idx.Index_ID > 0
ORDER BY idx.avg_fragmentation_in_percent desc
Method 3

We can find the Index Fragmentation status using the T-SQL statement. On a single table, several columns joins to form a number of indexes, each of which can have a varied index fragmentation percentage. Users must now find that threshold value from the db before making it appropriate or adding an index to maintenance. To locate it with object information, use the T-SQL command below.

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
How to fix it?

We can execute the index maintenance commands, REBUILD or REORGANIZE with the ALTER INDEX statement. Users can perform this command using SSMS as well.

[Need assistance further? Get in touch with us.]

Conclusion

To sum up, our Support team provides three methods to check index fragmentation in SQL server in this article.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Never again lose customers to poor
server speed! Let us help you.

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie
_clck, _clsk, CLID, ANONCHK, MR, MUID, SM

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

_reb2bgeo - The visitor's geographical location

_reb2bloaded - Whether or not the script loaded for the visitor

_reb2bref - The referring URL for the visit

_reb2bsessionID - The visitor's RB2B session ID

_reb2buid - The visitor's RB2B user ID

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid
_reb2bgeo, _reb2bloaded, _reb2bref, _reb2bsessionID, _reb2buid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF