Made Simple : MS SQL: Understanding Indexes in SQL Server: A Complete & Comprehensive Guide

Outline of the article:

1. Introduction of Indexes

a. Definition and purpose of indexes

b. Importance of indexes in optimizing database performance

2. Advantages and Disadvantages of Indexes

a. Advantages:

i. Improved query performance

ii. Faster data retrieval

iii. Efficient sorting and grouping

b. Disadvantages:

i. Increased storage requirements

ii. Additional overhead during data modifications

3. Benefits of Using Indexes

a. Enhanced query performance

b. Efficient data retrieval

c. Improved scalability and concurrency

4. Usage of Indexes

a. Query optimization

b. Sorting and grouping operations

c. Joining tables efficiently

5. Components of Indexes

a. Key columns

b. Included columns

c. Index pages and data pages

6. Types of Indexes

a. Clustered Indexes

b. Non-Clustered Indexes

c. Unique Indexes

d. Filtered Indexes

e. Columnstore Indexes

7. Internal Architecture of Indexes

a. B-Tree structure

b. Leaf and non-leaf nodes

c. Index fragmentation and its impact

8. Differences between Clustered and Non-Clustered Indexes

a. Storage structure

b. Key columns and data order

c. Impact on data modification operations

9. Understanding Fragmentation

a. Definition and causes of fragmentation

b. Impact on performance

c. Script to get fragmentation details of a database

10. Rebuilding and Reorganizing Indexes

a. Benefits and purposes of index maintenance

b. Script and steps to rebuild or reorganize indexes

11. How to List All Indexes in SQL Server

a. Script to retrieve a list of indexes in a database

b. Filtering and sorting options

12. Columnstore Indexes

a. Definition and benefits

b. Usage Scenarios and considerations

13. Filtered Indexes

a. Definition and purpose

b. Creating and using filtered indexes effectively

14. Checking Index Usage and Last Maintenance

a. Script to check if an index is being used

b. Determining the last rebuild or reorganization date

15. Examples and Case Studies

a. Real-world scenarios showcasing the use of indexes

16. Conclusion

a. Recap of the importance of indexes in SQL Server

b. Recommendations for Effective Index Usage

17. FAQs with Short Answers

18. Related Articles

Introduction of Indexes:

An index in SQL Server is a database object that provides rapid and effective access to specified data inside a table, enhancing the performance of data retrieval processes. The database engine can find and retrieve rows based on the indexed columns thanks to this organized representation of the data.

A group of key columns and related data structures make up an index, which makes it easier to find data. The data is put into a predefined order when an index is constructed on one or more columns by SQL Server, making it simpler and quicker to search, sort, and filter the data.

SQL Server may significantly enhance speed by employing indexes to reduce the requirement to scan whole tables for the necessary data. By acting as a map of the data, indexes make it possible for the database engine to rapidly find the required rows using the values of the indexed columns.

We can establish unique or non-unique indexes on a single column or several columns. Non-unique indexes permit duplicate values, but unique indexes guarantee the uniqueness of data in the indexed columns.

Also, check below Articles:

Unlocking Performance and Efficiency with ColumnStore Indexes

Filtered Indexes in SQL Server

Clustered Index - For Superfast Search

Advantages of Indexes:

Indexes provide several benefits that boost database performance, including:

1. Faster Query Execution: Indexes enable database engines to rapidly discover and retrieve data, cutting down on query execution time. SQL Server can quickly seek up data using index structures, which improves query response times.

2. Improved Data Retrieval: By serving as a data road map, indexes allow the database engine to quickly find certain rows using the indexed columns. Data retrieval activities are accelerated by doing this, especially when working with huge datasets or challenging queries.

3. Reduced Disk I/O: Indexes minimize the amount of disc I/O used to respond to query queries. SQL Server may utilize the index to identify the pertinent data pages rather than scanning the full table, minimizing disc access and enhancing overall efficiency.

Disadvantages of Indexes:

While indexes provide many advantages, there are a few things to keep in mind as well:

a. Increased Storage Space: Index indexes need more storage space to store the index structures. This is a crucial factor to take into account, particularly when working with huge tables or a limited amount of storage.

b. Overhead during Write Operations: Indexes have a little overhead while performing write operations such as INSERT, UPDATE, and DELETE on data. There is a modest speed hit on write operations since indexes must be updated concurrently with data changes.

c. Maintenance Overhead: Indexes must be maintained regularly to work at their best. Index fragmentation caused by changing data might affect how queries are executed. Regular index maintenance actions like rebuilding or rearranging indexes are required to reduce fragmentation.

3. Benefits of Using Indexes

a. Enhanced Query Performance: By making it easier for the database engine to find and retrieve data, indexes significantly enhance query speed. Index-using queries can significantly increase their performance, which leads to quicker application response times.

b. Faster Sorting and Grouping: Indexes are essential for sorting and grouping activities since they speed up the process. By building an index on the columns that are used for sorting or grouping, SQL Server can carry out these actions more quickly and with less overhead.

c. Selective Data Retrieval: Indexes provide selective data retrieval, which enables speedy access to particular rows depending on the indexed columns. When data needs to be filtered according to certain criteria, this is quite helpful.

d. Join Operations Optimization: Indexing facilitates rapid access to the connected columns, which optimizes join processes. Indexes may be used by SQL Server to quickly aggregate data from several tables, cutting down on the time needed for complicated join queries.

e. Improved Concurrency: Indexes are essential to database concurrency control. Indexes lessen contention by reducing the amount of time needed to get data and improve the system's capacity to accommodate numerous concurrent user requests.

Usage of Indexes:

Indexes are essential for optimizing several uses for databases.

a. Query Optimisation: Indexes help the database engine identify and retrieve data more quickly, which considerably improves query speed. When a query is run, SQL Server may make use of indexes to rapidly reduce the search space and find the pertinent data based on the indexed columns. Indexes minimize disc I/O and speed up query response time by lowering the amount of data pages that must be retrieved. Queries can see significant speed increases with well-designed indexes, leading to quicker application performance.

b. Sorting and Grouping: The use of indexes in sorting and grouping operations is essential. Having an index on certain columns can significantly speed up the process when a query requires sorting or grouping based on those particular columns. SQL Server may take advantage of the index's sorted order to more effectively complete these tasks rather than having to do pricey sorting or grouping operations on the full dataset. Indexes facilitate quicker sorting, lessen the requirement for temporary storage, and boost the efficiency of procedures involving grouping or sorting.

c. Effectively Joining Tables: By enabling rapid access to the linked columns, indexes facilitate join operations. Indexes on the join columns can greatly improve the performance of the join process when connecting numerous tables depending on certain columns. To find matching entries between the connected tables, SQL Server can use indexes rather than complete table scans. Indexes simplify join operations and enhance query speed, particularly for complicated queries involving several tables, by reducing the amount of data that has to be processed.

Components of Indexes:

a. Key Columns: An index's basis is made up of key columns. They serve as the foundational columns upon which the index is built. SQL Server uses the key columns of the index to find and obtain the appropriate data when a query is run. Indexes provide for quicker search and retrieval activities by arranging the data based on these important columns. Depending on the intended index sorting order, key columns can be configured as either ascending or descending.

b. Included Columns: Indexes may also contain non-key columns known as included columns in addition to the key columns. The leaf nodes of the index contain these columns even though they are not a part of the structure of the index. The ability to do covering queries, which don't involve reading the underlying data pages, is made possible by adding extra columns to the index. In particular, for queries that fetch a subset of columns not contained in the index's key columns, this lowers disc I/O and enhances query performance.

c. Index Pages and Data Pages: Indexes are made up of two different sorts of pages: index pages and data pages. Index pages serve as a storage space for the index structure and a tool for quick data retrieval. They provide the information required for index navigation, such as index key values, references to the associated data pages, and other metadata. A B-tree structure is often used to organize index pages hierarchically, enabling quick data retrieval depending on the indexed columns.

Types of Indexes:

a. Clustered Indexes:

A clustered index establishes the physical arrangement of the data rows in a table. There can only be one clustered index per table, and it establishes the actual storage layout of the table. By defining the sequence of data storage in the key columns, it is possible to efficiently retrieve data using the key values of the index. For queries that often do range-based searches or demand that the data be returned in a specified order, clustered indexes are very beneficial.

b. Non-Clustered Indexes:

Non-clustered indexes, in contrast to clustered indexes, produce a structure distinct from the data rows. They are made up of the indexed columns and a reference to the associated data rows. By enabling quicker access to particular data depending on the indexed columns, non-clustered indexes are beneficial for enhancing query performance. Non-clustered indexes are useful for accelerating searches, joins, and sorting operations, and a database can contain numerous of them.

c. Unique Indexes:

With the help of unique indexes, data in one or more columns are guaranteed to be distinct. They stop the indexed columns from receiving duplicate values. A table can have several unique indexes thanks to SQL Server, giving you flexibility in enforcing uniqueness on various groups of fields. Maintaining data integrity and accelerating queries that demand verification for uniqueness are two benefits of using unique indexes.

d. Filtered Indexes:

A non-clustered index type that only contains a portion of the data rows according to a filter condition is known as a filtered index. These indexes are made to improve searches that access a certain subset of data, such as rows that satisfy certain requirements or have a certain value for a given column. Filtered indexes enhance query efficiency and lower storage needs by condensing the index size and emphasizing pertinent facts.

e. Columnstore Indexes:

Workloads for data warehousing and analytics are catered to by Columnstore indexes. Columnstore indexes, as opposed to conventional row-based indexes, store data column-wise, allowing for effective compression and enhanced query performance for huge datasets. For analytical queries that entail aggregations, reporting, and data analysis, columnstore indexes are very useful.

Internal Architecture of Indexes:

a. B-Tree structure:

A popular data structure for constructing indexes in databases is the B-tree (Balanced Tree) structure. It offers a useful method for sorting and efficiently organizing data. All leaf nodes are at the same level in the B-Tree, which provides effective search operations.

The information is kept in nodes, which are arranged into tiers of a B-Tree. Except for leaf nodes, any node can have more than one child node. The "order" of the B-Tree is commonly defined as the number of child nodes in a node.

The B-Tree's root node, which is the top-level node, holds references to its child nodes. The child nodes may be leaf nodes or internal nodes. Keys and references to their child nodes are stored in internal nodes. Leaf nodes keep the actual data records and the keys that go with them.

Within each node of a B-Tree, the keys are kept in sorted order. This enables effective search operations utilizing binary search and other methods. The B-Tree structure ensures that the keys are distributed evenly across the tree and offers search, insertion, and deletion operations with logarithmic time complexity.

b. Leaf and non-leaf nodes:

There are two categories of nodes in a B-Tree: leaf nodes and non-leaf (internal) nodes.

Leaf Nodes: In a B-Tree, leaf nodes are the lowest-level nodes. They include the actual data records as well as the keys that go with them. A linked list, formed by connecting each leaf node to its nearby leaf nodes, enables effective range searches and sequential access.

Non-leaf (Internal) Nodes: Between the root node and the leaf nodes, there are non-leaf nodes. They keep pointers to their child nodes as well as keys. These keys serve as dividers or separators for the key ranges kept in the child nodes. No actual data records are stored by non-leaf nodes.

A B-Tree's internal nodes are explored according to the search key value until a leaf node is reached during a search operation. Then, a search is conducted within the leaf node to identify the closest or exact match.

c. Index fragmentation and its effects:

Index fragmentation is the state in which the information contained in an index physically disperses or fragments on the storage media. Numerous things, including frequent record additions, changes, and removals, might cause it.

The performance of database operations will be affected by index fragmentation. The following are a few consequences of index fragmentation:

Increased disk I/O: Fragmented indexes need additional disc I/O operations to access the data. The database system must read from the disc more often to get the required data pages when the index grows dispersed, which can increase query execution time.

Reduced caching effectiveness: Because the data pages in a fragmented index are not contiguous, memory caches are not used as effectively. The database system can regularly need to load and remove pages from the disc, which lowers the effectiveness of caching techniques like buffer pools.

Reduced index scan performance: Index scan operations (such as range queries) may execute less quickly if an index is extensively fragmented. Because the index is dispersed, slower scans might be the result of additional disc searches.

Differences between Clustered and Non-Clustered Indexes:

a. Storage structure:

Clustered Index: Using the key values of the index, a clustered index arranges the table's data rows physically before storing them on a disc. Each table can only include one clustered index, and this index establishes the data's physical order. In other words, the storage structure of the table is defined by the clustered index.

Non-Clustered Index: Contrarily, a non-clustered index has a distinct storage structure from the actual data rows. The key values and references to the associated data rows are contained in the non-clustered index. The data rows are kept separately and are typically kept in the order that they were added or changed.

b. Key columns and data order:

Clustered Index: The columns that determine the index's sorting order are the key columns of a clustered index. These important fields are used to physically sort and save on disc the data rows in a clustered index. A clustered index's key columns, which define the arrangement of the data rows, must be distinct.

Non-Clustered Index: The order of the index is likewise determined by the key columns in a non-clustered index. The key columns of the non-clustered index are not used to physically organize the data rows. Instead, references to the matching data rows are contained in the non-clustered index, enabling effective retrieval based on the key values.

c. Impact on data modification operations:

Clustered Index: Any changes that impact the key values may necessitate rearranging the data rows as the data rows of a table with a clustered index are physically arranged depending on the index's key values. For instance, relocating the current data rows to make room for a new row with a key value that falls between two existing key values may be necessary. Rearranging the impacted data rows may be necessary when changing or removing rows. Performance-wise, these procedures can be expensive, especially for big tables.

Non-Clustered Index: Data update activities have a different effect on non-clustered indexes than they do on clustered indexes. The non-clustered index itself does not require any data rearranging while inserting, updating, or removing data rows. However, changing the relevant entries in the non-clustered index can be necessary if the key values of the data rows are changed. During data alteration activities, this additional maintenance of the non-clustered index may cause some overhead.

Understanding Fragmentation:

a. Definition and causes of fragmentation:

Fragmentation is the circumstance when data become dispersed or disorganized inside a database or file system. Its definition and causes are listed in section a. Disc fragmentation, file fragmentation, and index fragmentation are just a few of the levels at which it can happen.

1. Disk fragmentation: Files on a storage medium can get fragmented into non-contiguous chunks, which is known as disc fragmentation. As files are generated, changed, and destroyed over time, inefficient disc space allocation results.

2. File fragmentation: When the contents of a file are dispersed across non-contiguous disc blocks, file fragmentation occurs. It might happen as a result of repeated file updates, appends, or deletions, which physically separate the file into many pieces.

3. Index fragmentation: Database systems experience index fragmentation when the data pages in an index become disorganized or dispersed. The index may become fragmented and ineffective for query operations as a result of frequent data alterations (insertions, updates, and removals).

Frequent data alterations, bad disc allocation methods, inappropriate maintenance, and a lack of sufficient disc capacity are the main reasons for fragmentation.

b. Impact on performance:

Fragmentation may have a detrimental effect on the performance of database systems and file systems in several ways.

1. Increased disc I/O: Data or files that have been fragmented need to be accessed and retrieved, which involves more disc read/write operations. As a result, read and write rates are slower, and disc I/O overhead is increased.

2. Slower sequential access: Data or files that are fragmented on a disc are not physically contiguous, which slows down sequential access. Sequential access patterns that require moving the disc head between non-contiguous blocks, such as scanning huge files or doing table scans in databases, become less effective.

3. Reduced cache utilization: Fragmentation can cause memory caches, such as buffer pools in databases, to be used less effectively. The cache might not be able to keep all the required blocks of data or files dispersed, which would lead to more frequent disc accesses.

4. Increased file or index size: Due to the unused space between non-contiguous blocks, fragmentation can increase the size of files or indexes. As a result, backup and restoration processes may take longer and use more capacity.

c. Script to get fragmentation details of a database:

To get fragmentation details of a database, you can use the following SQL script in Microsoft SQL Server:

-- Fragmentation details for all indexes in a database

USE [AdventureWorks2016];

-- Get fragmentation details for all indexes

SELECT @@ServerName AS [ServerName], GETDATE() AS [ReportPreparedOn],

OBJECT_NAME(ps.object_id) AS [TableName],

i.name AS [IndexName],

ps.index_type_desc AS [IndexType],

ps.avg_fragmentation_in_percent AS [Fragmentation (in %)],

ps.fragment_count AS [Fragment Count],

ps.page_count AS [Page Count], ps.Record_Count AS [Record Count]

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ps

INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id

WHERE ps.database_id = DB_ID();

This script uses the dynamic management function sys.dm_db_index_physical_stats to get information on index fragmentation over the whole database given. The table name, index name, index type, average fragmentation in percent, fragment count, and page count are among the details included. By executing this script, you may evaluate the degree of index fragmentation in your database and take the necessary steps to remedy it, such as rebuilding or reorganizing the indexes.

Rebuilding and Reorganizing Indexes:

a. Benefits and purposes of index maintenance:

Index maintenance, which involves rebuilding or reorganizing indexes, has various advantages and fulfills crucial functions in a database system. These include:

1. Enhances query performance: As data is added, modified, or removed over time in a database, indexes may become disorganized and ineffective. Index upkeep contributes to the structure's optimization, minimizing fragmentation and enhancing query performance. Faster query execution and data retrieval are made possible by properly managed indexes.

2. Reduces disc I/O: Accessing data using fragmented indexes results in additional disc I/O operations. Index rebuilding or rearranging can reduce disc I/O and boost system performance as a whole. As the index structure gets more organized, sequential I/O operations become more effective.

3. Optimises storage space: Indexes tend to take up a lot of space, particularly if they are fragmented or have a lot of overhead. Compacting the index structure during index maintenance procedures helps to recover unused space and maximize storage use.

4. Maintains data integrity: Data integrity is maintained via routine index maintenance, which involves verifying and fixing index structures. It aids in finding and fixing index discrepancies or corruption, avoiding problems with data access and potential mistakes.

b. Rebuilding or reorganizing indexes:

Depending on the database management system you are using, the specific rebuilding or reorganizing indexes script and stages will differ. To rebuild or rearrange indexes in Microsoft SQL Server, follow this example:

1. Rebuilding Indexes: Rebuilding an index entails deleting and establishing a new version of the index, which completely rebuilds its structure. Despite requiring additional resources, this can improve index optimization.

-- Rebuild all indexes in a table

ALTER INDEX ALL ON TableName REBUILD;

-- Rebuild a Particular Index

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee REBUILD;

2. Reorganizing Indexes: To lessen fragmentation, reorganizing an index entails physically rearranging the index's leaf-level pages. Compared to rebuilding, this process uses fewer resources and is appropriate for modest degrees of fragmentation.

-- Reorganize all indexes in a table

ALTER INDEX ALL ON TableName REORGANIZE;

Note: If you wish to rebuild or rearrange the order of the indexes in a table, replace TableName with the table's real name.

Index rebuilding or reorganization procedures:

1. Determine which indexes require upkeep. To assess index fragmentation levels and spot fragmented indexes, utilizing scripts or database monitoring tools.

2. Ascertain whether each index has to be rebuilt or reorganized. For significantly fragmented indexes (example: fragmentation > 30%), rebuilding is advised, but reorganizing is appropriate for moderately fragmented indexes (example: fragmentation between 5% and 30%).

3. For each index you want to keep, run the relevant script (ALTER INDEX REBUILD or ALTER INDEX REORGANISE). For each index, you may execute the script separately, or we can use a loop to work on several indices at once.

4. Monitor the progress and verify the completion of the index maintenance operation.

The below line of code will generate an index rebuild or reorganization script of a database:

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,

ind.name AS IndexName, indexstats.index_type_desc AS IndexType,

indexstats.avg_fragmentation_in_percent AS [Fragmentation in %],

'ALTER INDEX ' + QUOTENAME(ind.name) + ' ON ' +QUOTENAME(object_name(ind.object_id)) +

CASE WHEN indexstats.avg_fragmentation_in_percent>30 THEN ' REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON, MAXDOP = 16, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'

WHEN indexstats.avg_fragmentation_in_percent>=5 THEN 'REORGANIZE'

ELSE NULL END as [IndexRebuildReOrgScript]

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats

INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id

AND ind.index_id = indexstats.index_id

WHERE ind.Name is not null

AND avg_fragmentation_in_percent>5

AND page_count>100

ORDER BY indexstats.avg_fragmentation_in_percent DESC

How to List All Indexes in SQL Server:

a. A script to obtain a database's index list:

We may use the following script to get a list of indexes in a SQL Server database:

-- List all indexes in a database

USE AdventureWorks2016;

SELECT @@ServerName AS [ServerName], GETDATE() AS [ReportPreparedOn],OBJECT_NAME(i.object_id) AS TableName,

i.name AS IndexName,i.type_desc AS IndexType,c.name AS ColumnName

FROM sys.indexes i

INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id

INNER JOIN sys.columns c ON i.object_id = c.object_id AND ic.column_id = c.column_id

WHERE i.is_primary_key = 0 -- Exclude primary key indexes if desired

AND i.is_unique_constraint = 0 -- Exclude unique constraint indexes if desired

ORDER BY TableName, IndexName;

The system catalog views sys.indexes, sys.index_columns, and sys.columns are used by this script to acquire details about the indexes in the chosen database. It retrieves information such as the name of the table, the index, the kind of index, and any related column names. According to our needs, we may change the script by adding or removing other conditions.

b. Filtering and sorting options:

In the script above, we can use the ORDER BY clause and other criteria to filter and order the list of indexes. Here are a few typical choices:

Filter by schema or table name: We may add extra criteria to the "WHERE" clause to filter indexes based on a particular schema or table. For instance:

-- Filter indexes by schema and table name:

SELECT <Required Column Name>

FROM <TableName>

WHERE

SCHEMA_NAME(o.schema_id) = <SchemaName>

AND OBJECT_NAME(i.object_id) = <TableName>

Filter by index type:

We may add a condition to the WHERE clause to filter indexes based on their type (e.g., clustered, non-clustered). For instance:

-- Filter indexes by index type

SELECT <Required Column Name> FROM <TableName>

WHERE i.type_desc = 'CLUSTERED' -- or 'NONCLUSTERED'

Indexes with unique constraints or main keys are excluded:

We can include criteria in the WHERE clause to remove a main key or unique constraint indexes from the list. For instance:

-- Exclude primary key and unique constraint indexes

SELECT <Required Column Name>

FROM <TableName> WHERE i.is_primary_key = 0

AND i.is_unique_constraint = 0

Options for sorting:

The ORDER BY clause may be used to order the list of indexes. For instance, to sort first by an index name, then by a table name:

-- Sort indexes by a table name and index name

SELECT <Required Column Name> FROM <TableName>

ORDER BY TableName, IndexName;

To get the appropriate list of indexes, we may change the filtering and sorting parameters based on our own needs.

Columnstore Indexes:

Unlocking Performance and Efficiency with ColumnStore Indexes

Columnstore indexes are an SQL Server feature that manages and stores data by column rather than by row, which significantly improves performance for analytical workloads. Columnstore indexes store and retrieve data column by column, as opposed to conventional rowstore indexes that do the opposite.

Advantages of Columnstore index :

Better query performance: Columnstore indexes are designed for analytical queries and offer significant performance improvements for complicated joins, filtering, and aggregations.

Reduced storage requirements: Storage needs are reduced because it employs compression techniques created particularly for columnar data, which results in considerable storage savings.

Faster data loading: It shines in circumstances involving massive data loading.

Execution in batch mode: This feature allows batch mode execution, which improves CPU utilization and query efficiency by processing a group of rows at once.

FAQs:-

Q1: What are indexes in SQL Server?

Ans: By enabling quicker access to table rows, indexes are database objects that boost the efficiency of data retrieval processes.

Q2: How do indexes benefit database performance?

Ans: Indexes speed up data retrieval, decrease disc I/O, and increase query performance.

Q3: What are the types of indexes in SQL Server?

Ans: Filtered, Columnstore, Clustered, and Non-Clustered indexes.

Q4: How can I check if an index is in use?

Ans: To obtain data on the utilization of an index, we can query Dynamic Management Views (DMVs).

Q5: When should I rebuild or reorganize indexes?

Ans: When indexes become fragmented and negatively impact query performance, they should be rebuilt or reorganized.

Q6: How do I rebuild or reorganize indexes in SQL Server?