How to Check Index Fragmentation on Indexes in a xPortalNet Database

Owned by Tan Rong Wei (Unlicensed)
Last updated: Jun 04, 2020
2 min read

Problem (Issue) Statement:

How to Check Index Fragmentation on Indexes in a xPortalNet Database

Description:

This article will guide users to check index fragmentation on indexes in a xPortalNet database.

Fragmentation happens when the logical order of pages in an index does not match the physical order in the data file. Because fragmentation can affect the performance of some queries, you need to monitor the fragmentation level of your indexes and, if required, perform re-organize or rebuild operations on them.

This will affect the overall query performance and you may notice a query behaving slower than normal.

Related Software:

  • SQL Server Management Studio

Related Hardware:

  • Desktop computer

System Application:

NIL

Symptom and Finding:

NIL

Cause:

For first time guide to check index fragmentation on indexes in a xPortalNet database.

Index fragmentation is usually caused by xPortalNet database being frequently updated via INSERT, UPDATE, or DELETE statements. Over time, these modifications can cause the information in the index to become scattered in the database (fragmented).

Solution:







Summary:

  • To check the Index Fragmentation in SQL Server.

.

Step-by-step Guide:

  • To check the Index Fragmentation in SQL Server:

Step 1: Launch the SQL Server Management Studio.

.

Step 2: In the Object Explorer panel, right click on the Database and select Reports > Standard Report > Index Physical Statistics.
Index Physical Statistics in Standard Reports Menu 
Figure 1: Index Physical Statistics in Standard Reports Menu

.

Step 3: Please wait while data for the report is retrieved from the server.
Retrieving Data from Server 
Figure 2: Retrieving Data from Server

.

Step 4: SQL Server Management Studio will generate Index Physical Statistics report as below. This provides details on fragmentation of index within the selected Database
Index Physical Statistics for the Selected Database 
Figure 3: Index Physical Statistics for the Selected Database

.

Step 5: In the report provided, users can refer to the Operation Recommended column to see the status of the index. If the column is shown as "-", it means that the index is in good shape.
If the column is shown as Reorganize or Rebuilt, users should monitor the fragmentation index, which is shown in Step 6.
Tables Highlighted have Operation Recommended as Rebuild or Reorganize 
Figure 4: Tables Highlighted have Operation Recommended as Rebuild or Reorganize


Tips

User should perform Index Rebuild and Reorganize on any table based on the Operation Recommended column.

.

.

Step 6: Expand the #Partitions field to see the % of fragmentation for any given index.
 
Figure 5: Expanding #Partitions to see the % of Fragmentation

.

The result is shown as below.
 
Figure 6: % of Fragmentation Result

.

Step 7: User can then perform rebuilt and reorganize based on the Index Physical Statistics report in the respective tables.

Knowledge Base Link

User can refer How to Rebuild and Reorganize SQL Server Index for xPortalNet Database to complete the task.

Common Issue

SQL Queries taking longer than normal to complete.

Date Documentation:

21/5/2020 (Rev 1.0)

PROOF-READ

.

Related Articles.

Containing the label "system-maintenance"

Filter by label

There are no items with the selected labels at this time.

.

.

© MicroEngine Technology Sdn Bhd (535550-U). All rights reserved.