Few things about Indexes - 1

A Pseudo clustered Index

The covering index physically organizes the data of all the indexed columns in a sequential order. Thus, from a disk I/O perspective, a covering index that doesn’t use included columns becomes a clustered index for all queries satisfied completely by the columns in the covering index. If the result set of a query requires a sorted output, then the covering index can be used to physically maintain the column data in the same order as required by the result set— it can then be used in the same way as a clustered index for sorted output. Covering indexes can give better performance than clustered indexes for queries requesting a range of rows and/or sorted output. The included columns are not part of the key and therefore wouldn’t offer the same benefits for ordering as the key columns of the index.

Index Intersections

If a table has multiple indexes, then SQL Server can use multiple indexes to execute a query. SQL Server can take advantage of multiple indexes, selecting small subsets of data based on each index and then performing an intersection of the two subsets (that is, returning only those rows that meet all the criteria). SQL Server can exploit multiple indexes on a table and then employ a join algorithm to obtain the index intersection between the two subsets.

Index Compression

Data and index compression was introduced in SQL Server 2008 (available in the Enterprise and Developer Editions). Compressing an index means getting more key information onto a single page. This can lead to significant performance improvements because fewer pages and fewer index levels are needed to store the index. There will be overhead in the CPU as the key values in the index are compressed and decompressed, so this may not be a solution for all indexes. Memory benefits also because the compressed pages are stored in memory in a compressed state.

By default, an index will be not be compressed. You have to explicitly call for the index to be compressed when you create the index. There are two types of compression: row- and page-level compression. Non-leaf pages in an index receive no compression under the page type.

CREATE NONCLUSTERED INDEX IX_Comp_Test

ON Person.Address (City,PostalCode)

WITH (DATA_COMPRESSION = ROW ) ;

Lookups can become performance bottlenecks really quickly and can sometimes cause blocking or even worse dead locks.  Key Lookups can cause blocking and deadlocks because the optimizer has to take a shared lock on the Clustered Index to get the data that is missing from the nonclustered index and this causes a problem when an insert/update/delete occurs because it requires an exclusive lock, on the Clustered Index.

When you are trying to optimize queries the first place to look is the execution plan.  If you see a lot of scans, you have a lot of optimization potential.  Remember that you want to make sure all columns in the select, the join and the where clause are present in your index. 

If we create clustered index on employeeid (int), and nonclustered on SSN (varchar), then leaf level and non-leaf level will appear as shown below.

Clustered index

Leaf level           – actual data

Non-leaf level    - employeeid+pointer

Nonclustered index

Leaf level           – employeeid (or RID)+SSN

Non-leaf level    - SSN+pointer

Always keep frequently updated columns in the include of an index. Otherwise, whenever that column has got updated, order of the index has to be changed.

Always WHERE clause column should be the first column in an index. If there are multiple search options, it would be better create multiple non-clustered indexes (covering) with include option. And add select, group columns into that include part.

If clustered key column is part of where clause, then that’s enough for the optimizer to pull the required data from the index irrespective of other columns which are part of other filters or part of select statement. Otherwise, we need to create covering index. 

Each page contains least values of next level pages.