How do indexes improve performance

There are several important areas to consider when it comes to SQL Server indexes, as they can have a significant impact on both your performance tuning efforts and overall database performance. Below are some details on each and the critical roles they play. In an OLTP database, users perform frequent read-write operations, inserting new data and modifying existing data. They use data manipulation language queries Insert, Update, Delete along with Select statements for data retrieval and modifications.

Multiple indexes might have a negative performance impact and put stress on system resources. Instead, it is recommended to create the minimum number of indexes that can fulfil your indexing requirements.

In OLAP databases on the other hand, you use mostly Select statements to retrieve data for further analytical purposes.

In this case, you can add more indexes with multiple key columns per index. You can also leverage columnstore indexes for faster data retrieval in data warehouse queries. Sometimes, developers put one clustered index and a few non-clustered indexes on it without looking for the queries that use those indexes. There might be an index that does not satisfy the query optimizer requirement; therefore, you should properly analyze your workload and SQL queries stored procedures, functions, views and ad-hoc queries.

You can capture the workload using SQL profiler, extended events and dynamic management views, and then create indexes to optimize resource-intensive queries. By creating the best indexes for these queries, it will put the least amount of strain on your system. Since you can have multiple columns in a table, here are a few considerations for index key columns. You should examine the data distribution in the SQL Server index key columns.

A column with non-unique values might cause a delay in retrieving the data and result in a long-running transaction. You can analyze data distribution using the histogram in statistics. You should also consider the data sorting requirements in your queries and indexes.

By default, SQL Server sorts data in ascending order in an index. Suppose you create an index in ascending order, but your queries use the Order By clause to sort data in descending order. There will be hundreds of such entries coming in at random point.

Here a separate non clustered index enables for faster data search. Clustered: The index that is storing the datamap or pointers is reflected on hard disk. At times it also alters the physical location of data on hard disk, also called as block.

This give tremendous boost when making a lookup for search query. Practical Usage: Enterprise projects like SAP having very structured data and datamap, but the data size is very huge, will benefit from Clustered index.

The query searching the data knows exact disk sector to work on. Cluster: This should not be confused with above keyword. Cluster is created when multiple table and database are joined. The records for the tables sharing the value of a cluster key shall be stored together in the same or nearby physical location on hard disk.

This reduces latency and approves faster search over complex tables and database. Practical Usage: An application used to analyse the data or create a report from combination of two or more tables, a compound or cluster will provide this environment.

Typically used in data analysis or report generation from multiple tables and database. It is unique and special in a way because it stores the indexing information in bit arrays also called bitmaps. The query and search result is done by performing logical bitwise 0 or 1 operation.

This make it very fast. Database having tables with very few unique fields are available for indexing. Plus, the data stored is heterogenous. Also, the data is selected too often with very few insert and update options. Plus, the data stored is as stream of bit 0 or 1. Practical usage :. A weather station has a huge amount of data; however, it may not have data for every parameter each time. Whereas, Bitmap indexing speeds up the process by entirely removing the parameters where there are no entries.

In actuality, what happens is the index causes the database to create a data structure. The data structure type is very likely a B-Tree. While the advantages of the B-Tree are numerous, the main advantage for our purposes is that it is sortable. When the data structure is sorted in order it makes our search more efficient for the obvious reasons we pointed out above. When the index creates a data structure on a specific column it is important to note that no other column is stored in the data structure.

Database indexes will also store pointers which are simply reference information for the location of the additional information in memory.

The index will actually look like this:. Non-clustered indexes are sorted references for a specific field, from the main table, that hold pointers back to the original entries of the table. The first example we showed is an example of a non-clustered table:. They are used to increase the speed of queries on the table by creating columns that are more easily searchable.

Note: Non-clustered indexes are not new tables. Non-clustered indexes hold the field that they are responsible for sorting and a pointer from each of those entries back to the full entry in the table.

You can think of these just like indexes in a book. The index points to the location in the book where you can find the data you are looking for. Non-clustered indexes point to memory addresses instead of storing data themselves. This makes them slower to query than clustered indexes but typically much faster than a non-indexed column.

You can create many non-clustered indexes. That is because indexes do not store all of the information from the original table. The pointer logic would look like this:. That means the query plan , the plan that SQL creates when determining the best way to perform a query, will begin to use the index when queries are being made.

That is the clustered index that was referenced earlier in the article that is automatically created based off of the primary key. That index was created similarly to the names index:.