How to Optimize Indexes in SQL Server

SQL Server automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. These modifications cause the information within the index to become scattered in the database. Fragmentation exists when indexes within the index to become scattered in the database.

Fragmentation exists when indexes have pages where the logical ordering does not match the physical ordering within the data file. Heavily fragmented indexes can degrade the query performance and cause your application to respond slowly.

Fragmentation normally occurs when a large number of insert and update operations are performed on the table. Fragmented data can cause the SQL Server to perform unnecessary data reads. This affects the performance of the query. Therefore, index defragmentation is necessary to speed up the query performance.

The first step in deciding which defragmentation method to use is to determine the degree of index fragmentation. You can detect index fragmentation by using the sys.dm_db_index_physical_stats system function.

The following statement displays a list of all the indexes on the HumanResources.Employee table with their fragmentation level:

SELCET a.index_id AS IndexID, name AS IndexName,
Avg_fragmentation_in_percent AS Fragmentation
FROM sys.dm_db_index_physical_stats (DB_ID (N’AdventureWorks’),
OBJECT_ID (‘HumanREsources.Employee’), NULL, NULL, NULL ) AS a
JOIN sys.indexes AS b ON  a.object_id = b.object_id AND a.index_id = b.index_id ORDER BY Fragmentation desc

In the preceding output given by this query, you can notice that the AK_Employee_LoginID index shows a high level of fragmentation.

After the degree of fragmentation is known, the fragmentation needs to be corrected. The following table lists the actions to be taken at different fragmentation levels to defragment the index.

Fragmentation Level                         Action to be Taken
>5% and <=30%  ALTER INDEX REORGANIZE
>30%  ALTER INDEX REBUILD WITH (ONLINE – ON)

Method of Degragmentation

You can execute the following command to defragment the AK_Employee_LoginID index:

ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD
After executing the preceding command, the degree of fragmentation is reduced.

Managing indexes with pre-defined Query: SQL Server

In addition to creating indexes in sql server, database developer also need to maintain them to ensure their continued optimal performance. The common index maintenance tasks include disabling, enabling, renaming, and dropping an index. As a database developer, you need to regularly monitor the performance of the index and optimize it.

Disabling Indexes

When an index is disabled, the user is not able to access the index. If a clustered index is disabled then the table data is not accessible to the user. However, the data still remains in the table, but is unavailable for Data Modification Language (DML) operations until the index is dropped or rebuilt.

To rebuild and enable a disabled index, use the ALTER INDEX REBUILD statement or the CREATE INDEX WITH DROP_EXISTING statement.

The following query disables a non-clustered index, IX_EmployeeID, on the Employee table.

ALTER INDEX IX_EmployeeID
ON Employee DISABLE

Enabling Indexes

After an index is disabled, it remains in the disabled state until it is rebuilt or dropped. You can enable a disabled index by rebuilding it through one of the following methods:

• Using the ALTER INDEX statement with the REBUILD clause
• Using the CREATE INDEX statement with the DROP_EXISTING clause
• Using the DBCC DBREINDEX

By using one of the preceding statements, the index is rebuilt and the index status is set to enable. You can rebuild a disabled clustered index, when the ONLINE option is set to ON.

Renaming Indexes

You can rename the current index with the help of the sp_rename system stored procedure.
The following statement renames the IX_JobCandidate_EmployeeID index on the JobCandidate table to IX_EmployeeID.

EXEC sp_rename
‘HumanResources.JobCandidate.IX_JobCandidate_EmployeeID’,
‘IX_EmployeeID’, ‘index’

Dropping Indexes

When you no longer need an index, you can remove it from a database. You cannot drop an index used by either a PRIMARY KEY or UNIQUE constraint, except by dropping the constraint.

The following statement drops the IDX_Employee_ManagerID index on the Employee table:

DROP INDEX IDX_Employee_ManagerID
ON Employee

How to Create Partition Scheme and Clustered Index: SQL Server

After creating the partition function, programmer needs to create a partition scheme to associate it with the partition function. Based on the boundary values defined in the partition function, there will be five partitions. The data of each partition is stored in a filegroup. You should have the same number of filegroups as partitions. If there are five partitions, you need to create five filegroups: fg1, fg2, fg3, fg4, fg5.

The following statements create the PSOrderDate partition scheme, associating it with the PFOrderDate partition function:

CREATE PARTITION SCHEME PSOrderDate
AS PARTITION PFOrderDate
TO (fg1, fg2, fg3, fg4, fg5)

The partition scheme, PSOrderDate, created in the preceding statement directs each partition to a separate filegroup.

Creating a Clustered Index

After creating the partition scheme, you need to associate it with a clustered index. As the clustered index is created on the attribute having unique and non-null values, you can create the index on the SalesOrderID column of the SalesOrderHeader table. To create the partitioned index, you need to associate the clustered index with the partition scheme as follows:

CREATE CLUSTERED INDEX ix_SalesOrderID
ON Sales.MySalesOrderHeader (SalesOrderID)
ON PSOrderDate (OrderDate)

The preceding statement will distribute the table data into five filegroups based on the yearly data of orders stored in the OrderDate column.

How to Create Partitioned Indexes in SQL Server

In SQL Server, indexes can also be partitioned based on the value ranges. Similar to the partitioned tables, the partitioned indexes also improve query performance. Partitioning enables you to manage and access subsets of data quickly and efficiently. When indexes become very large, you can partition the data into smaller, more manageable sections as the data is spread across file groups in a database.

Consider an example. In the Adventure Works database, the SalesOrderHeader table contains the details about the order received by Adventure Works, Inc. As the data in this table is large, the query takes a long time to execute. To solve this problem, you can create a partitioned index on the table. Partitioning an index will distribute the data in the table into multiple filegroups, thereby partitioning the table. This will enable the database engine to read or write data quickly. This also helps in maintaining the data efficiently.

Partitioning is allowed only in the Enterprise Edition of SQL Server. To create a partitioned index, you need to perform the following tasks:

• Create a partition function
• Create a partition scheme.
• Create a clustered index.

Creating a Partition Function

Similar to creating a partitioned table, you need to create a partition function to create a partitioned index. The partition function will determine the boundary values for creating partitions.

Consider an example. The queries on the SalesOrderHeader table are mostly base on the OrderData column. The sales manager of Adventure Works requires the details of the orders received, on a yearly basis. The table contains the details of orders for the last five years beginning from 2001. Based on this information, you can create a partition function as follows:

CREATE PARTITION FUNCTION PFOrderDate (date time)
AS RANGE RIGHT FOR VALUES (‘2002-01-01’, ‘2003-01-01’, ‘2004-01-01’, ‘2005-01-01’)

The preceding statement creates a partition function, PFOrderDate, by using the date time data type. It specifies four boundary values. Therefore, there will be five partitions. As range right is specified for partitioning, the first partition will contain data less than the first boundary value, 2002-01-01. The second partition will contain data greater than or equal to 2002-01-01 and less than or equal to 2003-01-01. Similarly, other partitions will store data.

Types of Secondary XML Indexes in SQL Server

These are non-clustered index of the primary XML index. There must be a primary xml index before each secondary xml index. Following are some types of secondary xml indexes:

Path Indexes

The path index is built on the path value columns of the primary XML indexes. This index improves the performance of queries that use paths and values to select data.

For example, if you execute a query that checks for the existence of a product model ID using an XQuery expression as /PD:ProductDescription/@ProductModelID[.=”19”], you can create a path secondary index on the CatalogDescription column of the ProductModel table. In this path index, you can use the primary index created previously.

The following statement creates a Path index on the CatalogDescription column:

CREATE XML INDEX PIdx_ProductModel_CatalogDescription_PATH ON Production.ProductModel (CatalogDesctiption)USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH

The preceding code create a path index, Pldx_ProductModel_CatalogDesctiption_PATH

Value Indexes

The value indexes contain the same items as path indexes but in the reverse order. It contains the value of the column first and then the path id. This index improves the performance of queries that use paths to select data.

For example, if you execute a query that checks the existence of a node in an XQuery expression such as//Item@ProductID=”1”], you can create a value secondary index by using the primary index created previously.

The following statement creates a value index on the CatalogDesctiption column:

CREATE XML INDEX PIdx_ProductModel_CatalogDesctiption_VALUE ON Production.ProductModel (CatalogDesctiption)
USING XML INDEX PXML_ProductModel_CatalogDescription
FOR VALUE

The preceding code creates a value index,
PIdx_ProductModel_CatalogDescription_VALUE on the CatalogDescription column of the table.

Property Indexes

The property index contains the primary key of the base table, path id, and the clause columns of primary XML indexes. This index improves the performance of queries that use paths to select data.

For example, if you execute a query that returns a value of the node in an XQuery expression, such as /ItemList/Item/@ProductID)[1], you can create a property secondary index on the CatalogDescription column of the ProductModel table by using the following statement:

CREATE XML INDEX PIdx_ProductModel_CatalogDescription_PROPERTY ON Production.ProductModel (CatalogDescription)
USING XML INDEX PXML_ProductModel_CatalogDescription FOR PROPERTY

The preceding code creates a property index, PIdx_ProductModel_CatalogDescription_PROPERTY, on the CatalogDescription column of the table.

You need to consider the following guidelines while creating an XML index:

• XML indexes can be created only on XML columns.
• XML indexes only support indexing a single XML column.
• XML indexes can only be added to tables, views, table-valued variables with XML column or XML variables.
• XML indexes created on a table do not allow you to modify the primary key. To do so, you first need to drop all the XML indexes on the table.

Types of XML Indexes used in SQL Server: Speed up Execution

When a query is based on an XML column, the query processor needs to parse the XML data each time the query is executed. In SQL Server, and XML data value can be of a maximum of two gigabytes (GB).

Therefore, the XML values can be very large and the server might take time to generate the result set. To speed up the execution of the query based on the XML data type, SQL Server allows you to create an index that is based on columns storing XML data values. Such indexes are called XML indexes.

Primary XML Index

This is a clustered B-Tree representation of the nodes in the XML data. When an index is created on a column with the XML data type, an entry will be created for all the nodes in the XML data. Therefore, the index creates several rows of data for each XML value in the column.

You can create XML indexes on XML columns by using the CREATE PRIMARY XML INDEX and CREATE XML INDEX T-SQL commands. For example, the ProductModel table contains the CatalogDescription column that stores XML values. You can create a primary XML index on this column by using the following statement:

CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDesctiption ON Production.ProductModel (CatalogDescription)

The preceding statement will create an index for all the nodes in the XML data stored in the CatalogDescription column.

Secondary XML Index

This is a non-clustered index of the primary XML index. A primary XML index must exist before any secondary index can be created. After you have created the primary XML index, an additional three kinds of secondary XML indexes can be defined on the table. The secondary XML indexes assist in the XQuery processing.

The three types of secondary XML indexes are:

• Path indexes
• Value indexes
• Property indexes

Guidelines for Creating Indexes in SQL Server

Database Developer need to consider listed guidelines while creating indexes on a table. These guidelines explains about some special features or we can say requirements needed to create an index.

Earlier article was about to create an index with syntax and description of parameters. Each parameter have its own speciality discussed in that article, here we noticed some guidelines that may use in create in index:

• Create clustered indexes on columns that have unique or not null values.
• Do not create an index that is not used frequently. You require time and resources to maintain indexes.
• Create a clustered index before creating a nonclustered index. A clustered index changes the order of rows. A nonclustered index would need to be rebuilt if it is built before a clustered index.
• Create nonclustered indexes on all columns that are frequently used in predicates and join conditions in queries.

Consider an example of an organization that maintains employee details in the Employee table. You can create a clustered index on the EmployeeID attribute of the Employee table by using the following code:

CREATE CLUSTERED INDEX IX_EmployeeID
ON Employee (EmployeeID)
WITH FILLFACTOR = 10

In the preceding code, the FILLFACTOR value of 10 has been specified to reserve a percentage of free space on each data page of the index to accommodate future expansion.

The following example creates a nonclustered index on the ManagerID attribute of the Employee table:

CREATE NONCLUSTERED INDEX IDX_Employee_ManagerID
ON Employee (ManagerID)

When a PRIMARY KEY or UNIQUE constraint is created on a table, an index is created automatically with the same name as the constraint.

How to Create Index using sql Query: Sql Server

Database programmer should create indexes on the most frequently queried column in a table. However, at times, you might need to create an index based on a combination of one or more columns. An index based on one or more columns is called a composite index. A composite index can be based on a maximum of 16 columns. However, you need to consider that indexes with less number of columns use less disk space and involve fewer resources when compared to indexes based on more columns.

To create an index you can use the CREATE INDEX statement. The syntax of the CREATE INDEX statement is:

CRATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON [{database_name.[schema_name]. | schema_name.}]
{table_or_view_name} (column [ASC | DESC] [, …n])
[INCLUDE (column_name [, …n])]
[WITH (<relational_index_option>[, …n])]
[ON {partition_cheme_name (column_name [, …n]) | filegroup_name |DEFAULT) ]
<relation_index_option> : : =
{PAD_INDEX = {ON | OFF}
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = {ON | OFF}
| IGNORE_DUP_KEY = {ON | OFF}
| STASTISTICS_NO_RCOMPUTE = {ON | OFF}
| DROP_EXISTING = {ON | OFF}
| ONLINE = {ON | OFF}

Where,

• UNIQUE crates an index where each row should contain a different index value. CLUSTERED specifies a clustered index where data is sorted on the index attribute. NONCLUSTERED specifies a nonclustered index that organizes data logically. The data is not sorted physically.
• Index_name specifies the name of the index.
• Table_name specifies the name of the table that contains the attributes on which the index is to be created.
• Column specifies the column or columns on which the index will be created.
• Column_name specifies the name of the column or columns on which the index would be created.
• ON partition_scheme_name ( column_name ) specifies the partition scheme the specifies the filegroups in which the pattitioned index will be mapped.
• ON filegroup_name specifies the filegroup on which index is created.
• ON DEFAULT specifies that the specified index will be created on the default filegroup.
• PAD_INDEX = { ON | OFF } specifies the index padding, which is OFF, by default.
• FILLFACTOR = 1 to 100 specifies a percentage that indicates how full the leaf level of each index page should become during index creation or rebuild. The default value is 0.
• SORT_IN_TEMPDB = { ON | OFF } specifies about storing temporary sort results in the tempdb.
• IGNORE_DUP_KEY = { ON | OFF } specifies whether a duplicate key value can be inserted or not…
• STATISTICS_NO_RECOMPUTE = { ON | OFF } specifies about recomputing the distribution statistics.
• DROP_EXISTING = { ON | OFF } specifies that the pre-existing clustered, nonclustered, or XML index is dropped and rebuilt.
• ONLINE = { ON | OFF } checks whether the underlying tables and associated indexes are available to query and modify the data during the index operation.

Developer can create online indexes only in the SQL Server 2005 Enterprise Edition.

Non-clustered index in sql
Guidelines to create indexes

Search Value using Non-Clustered index: SQL Server

Similar to the clustered index, a non-clustered index also contains the index also contains the index key values and the row locators that point to the storage location of the data in a table. However, in a non-clustered index, the physical order of the rows is not the same as the index order.

Non-clustered indexes are typically created on columns used in joins and the WHERE clause. These indexes can also be created on columns where the values are modified frequently. The SQL Server creates non-clustered indexes by default when the CREATE INDEX command is given. There can be as many as 249 non-clustered indexes per table.

The data in a non-clustered index is present in a random order, but the logical ordering is specified by the index. The data rows may be randomly spread throughout a table. The non-clustered index tree contains the index keys in a sorted order, with the leaf level of the index containing a pointer to the data page and the row number in the data page.

• The SQL Server perform the following steps when it uses a non-clustered index to search for a value:
• The SQL Server obtains the address of the root page from the sysindexes table.
• The search value is compared with the key values on the root page.
• The page with the highest key value less than or equal to the search value is found.
• The page pointer is followed to the next lower level in the index.
• Steps 3 and 4 are repeated until the data page is reached.
• The rows are searched on the leaf page for the specified value. If a match is not found, the table contains no matching rows. If a match is found, the pointer is followed to the data page and row-ID in the table and requested row is retrieved. 

The figure displays a non-clustered index present on the Eid attribute o the Employee table. To search for any value, the SQL Server would start from the root page and move down the B-Tree until it reaches a leaf page that contains a pointer to the required record. It would then use this pointer to access the record in the table. For example, to search for the record containing Eid E006 by using the non-clustered index, the following steps would be performed by the SQL Server:

• The SQL Server starts from page 603, which is the root page.
• It searches for the highest key value on the page, the page with a key value less than or equal to the search value, that is, to the page containing the pointer to Eid E005.
• The search continues from page 602.
• Eid E005 is found and the search continues to page 203.
• Page 203 is searched to find a pointer to the actual row. Page 203 is the last page, or the leaf page, of the index.
• The search then moves to page 302 of the table to find the actual row.

In an index, more than one row can contain duplicate values. However, if you configure an index to contain unique values, the index will also contain unique values. Such an index is called a unique index. You can create a unique index on a columns that contain unique values, such as the primary key columns.

A unique index can be clustered or non-clustered depending on the nature of the column

Clustered Indexes in SQL

Types of Indexes provided by SQL Server

As described in the earlier article indexes can be managed easily. The SQL Server allows you to create the following types of indexes:

• Clustered index
• Noclustered index

Clustered Index

A clustered index is an index that sorts and stores the data rows in the table based on their key values. Therefore, the data is physically sorted in the table when a clustered index is defined on it. Only one clustered index can be created per table. Therefore, you should build the index on attributes that have a high percentage of unique values and are not modified often.

In a clustered index, data is stored at the leaf level of the B-Tree. The SQL Server performs the following steps when it uses a clustered index to search for a value:

• The SQL Server obtains the address of the root page from the sysindexes table, which is a system table containing the details of all the indexes in the database.
• The search value is compared with the key values on the root page.
• The page with the highest key value less than or equal to the search value is found.
• The page pointer is followed to the next lower level in the index.
• Steps 3 and 4 are repeated until the data page is reached.
• The rows of data are searched on the data page until the search value is found. If the search value is not found on the data page, no rows are returned by the query.

Consider the following figure where the rows of the Employee table are sorted according to the Eid attribute and stored in the table.

Working of a Clustered Index

The preceding figure displays a clustered index on the Employee table. To search for any record, the SQL Server would start at the root page. It would then move down the B-Tree and the data values would be found on the leaf pages of the B-Tree. For example, if the row containing Eid E006 was to be searched by using a clustered index (refer to the preceding figure), the SQL Server perform the following steps:

• The SQL Server starts from page 603, the root page.
• The SQL Server searches for the highest key value on the page, which is less than or equal to the search value. The result of this search is the page containing the pointer to Eid E005.
• The search continues from page 602. There, Eid E005 is found and the search continues to page 203.
• Page 203 is searched to find the required row.

A clustered index determines the order in which the rows are actually stored. Therefore, you can define only one clustered index on a table.

How to Create and Manage Indexes in SQL Server

Database developer is often required to improve the performance of queries. SQL Server allows implementing indexes to reduce the execution time of queries. In addition they can restrict the view of data to different users by implementing views.

The SQL Server also provides an in-built full-text search capability that allows fast searching of data. This article discusses how to create and manage indexes and views.

Creating and Managing Indexes

When a user queries data from a table based on conditions, the server scans all the data stored in the database table. With an increasing volume of data, the execution time for queries also increases. As a database developer, you need to ensure that the users are able to access data in the least possible time. SQL Server allows you to create indexes on tables to enable quick access to data. In addition, SQL Server allows you to create XML indexes for columns that store XML data.

At times, the table that you need to search contains voluminous data. In such cases, it is advisable to create partitioned indexes. A partitioned index makes the index more manageable and scale able as they store only data of a particular partition.

As a database developer, you need to create and manage indexes. Before creating an index, it is important to identify the different types of indexes.

Identifying the Types of Indexes

Before identifying the types of indexes, it is important to understand the need to implement an index.
The data in the database tables is stored in the form of data pages. Each data page is 8 KB in size. Therefore, data of the complete table is stored in multiple data pages. When a user queries a data value from the table, the query processor searches for the data value in all the data pages. When it finds the value, it returns the result set. With an increasing volume of data, this process of querying data takes time.

To reduce the data query time, the SQL Server allows you to important indexes on tables. An index is a data structure associated with a table that helps in fast search of data in the table. Indexes in the SQL Server are like the indexes at the back of a book that you can use to locate text in the book.

Benefits provided by using Indexes:

• Accelerate queries that join tables, and perform sorting and grouping
• Enforce uniqueness of rows, (if configured for that)

An index contains a collection of keys and pointers. Keys are values built from one or more columns in the table with which the key is associated. The column on which the key is built is the one on which the data is frequently searched. Pointers store the address of the storage location where a data page is stored in the memory, as depicted in the following figure.

When the users query data with conditions based on the key columns, the query processor scans the indexes, retrieves the address of the data page where the required data is stored in the memory, and accesses the information. The query processor does not need to search for data in all the data pages. Therefore, the query execution time is reduced.

The keys in the indexes are stored in a B-Tree in the memory. A B-Tree is a data-indexing method that organizes the index into a multi-level set of nodes. Each page in an index B-Tree is called an index node. Each index contains a single root page at the top of the tree. This root page, or root node, branches out into n number of pages at each intermediate level until it reaches the bottom, or leaf level, of the index. The index tree is traversed by following pointers from the upper-level pages down through the lower-level pages.

The key values in the root page and the intermediate pages are sorted in the ascending order. Therefore, in the B-Tree structure, the set of nodes on which the server will search for data values is reduced. This enables the SQL Server to find the records associated with the key values quickly and efficiently. When you modify the data of an indexed column, the associated indexes are updated automatically.