SQL query performance killers – understanding poor database indexing
SQL Server performance is affected by many factors. The most common SQL Server performance killers are poor database design, poor indexing, poor query design, not reusable execution plans, frequent query recompilation, excessive fragmentation, and more. With these factors present on your machine, even adding additional hardware resources might not help, as these SQL Server performance killers can use all available resources.
What are indexes?
An index is used to speed up data search and SQL query performance. The database indexes reduce the number of data pages that have to be read in order to find the specific record.
The biggest challenge with indexing is to determine the right ones for each table.
We will start with explaining clustered and nonclustered indexes.
A table without a clustered index is called a heap, due to its unordered structure. Data in a heap table isn’t sorted, usually the records are added one after another, as they are inserted into the table. They can also be rearranged by the database engine, but again, without a specific order. When you insert a lot of rows into a heap table, the new records are written on data pages without a specific order. Finding a record in a heap table can be compared to finding a specific leaf in a heap of leaves. It is inefficient and requires time.
A heap can have one or several nonclustered indexes, or no indexes at all.
A nonclustered index is made only of index pages that contain row locators (pointers) for records in data pages. It doesn’t contain data pages, like clustered indexes.
A clustered index organizes table data, so data is queried quicker and more efficiently. A clustered index consists of both index and data pages, while a heap table has no index pages; it consists only of data pages. In other words, it is not just an index, i.e. a pointer to the data row that contains the key value, but it also contains table data. The data in the clustered table is sorted using the values of the columns the clustered index is made of.
Finding a record from a table with a proper clustered index is quick and easy like finding a name in an alphabetically ordered list. A general recommendation for all SQL tables is to have a proper clustered index
While there can be only one clustered index on a table, a table can have up to 999 nonclustered indexes
A table or view can contain the following types of indexes:+
Clustered
Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
Nonclustered
Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.