How to Rebuild and Reorganize SQL Server Index for xPortalNet Database

Problem (Issue) Statement:	How to Rebuild and Reorganize SQL Server Index for xPortalNet Database
Description:	This article will guide users to rebuild and reorganize SQL Server Index for xPortalNet Database. Indexes are a key element to improve xPortalNet database performance. Users are recommended to maintain indexes to make sure best performance from the SQL database. There are two maintenance routines that can be performed, either an index rebuilding or index reorganizing in order to maintain index and improve database performance. An index rebuild simply drops and recreates the index which means that index rebuild will solve both the internal and external fragmentation. An index reorganize only solves external fragmentation by moving pages around.
Related Software:	SQL Server Management Studio
Related Hardware:	Desktop computer
System Application:	NIL
Symptom and Finding:	NIL
Cause:	For first time guide to rebuild and reorganize SQL Server Index for 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 perform rebuild index for a single table. To perform reorganize index for a single table. . Recommendations Indexes should be rebuilt when index fragmentation percentage is great than 40%. Indexes should be reorganized when index fragmentation is between 10% - 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has an option to keep the database resource ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding process. . Step-by-step Guide: To perform rebuild index for a single table: Step 1: Launch the SQL Server Management Studio. . Step 2: The Microsoft SQL Server Management Studio window will be shown. In the Object Explorer panel, expand the Databases folder. Figure 1: Expanding Databases Folder . Step 3: Select XPNDB to expand the Table folder. Figure 2: Selecting XPNDB to Expand the Table Folder . Step 4: Expand the specific table requiring rebuild operation. Figure 3: Expanding the Table Requiring Rebuild Operation . Knowledge Base Link User can refer How to Check Index Fragmentation on Indexes in a xPortalNet Database* to identify which table is recommended to perform Rebuild or Reorganize.* . . Step 5: Right click the Index folder and select Rebuilt All. Figure 4: Right-clicking Index Folder and Selecting Rebuild All . Step 6: The Rebuild Index window will be shown. User can analyse specific table information for the indexes to be rebuilt and total fragmentation, click OK and wait for the process to complete. Figure 5: Rebuild Indexes Window . . To perform reorganize index for a single table: Step 1: In the Object Explorer panel, expand the Databases folder. Figure 6: Expanding the Databases Folder . Step 2: Select XPNDB to expand the Table folder. Figure 7: Expanding the XPNDB Folder . Step 3: Expand the specific table requiring reorganize operation. Figure 8: Expanding the Table Requiring Reorganize Operation . Step 4: Right-click the Index folder and select Reorganize All. Figure 9: Right-clicking the Index Folder and Selecting Reorganize All . Step 5: The Reorganize Indexes window will be shown. User can analyse specific table information for the indexes to be reorganize and total fragmentation, click OK and wait for the process to complete. Figure 10: Reorganize Indexes Window
Common Issue	NIL
Date Documentation:	22/5/2020 (Rev 1.0)