Types of Indexes in SQL Server 2012

The following table lists the types of indexes available in SQL Server and provides links to additional information.

Index type	Description	Additional information
Clustered	A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.	Clustered Index Design Guidelines Clustered Index Structures
Nonclustered	A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.	Nonclustered Index Design Guidelines Nonclustered Index Structures
Unique	A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique. Uniqueness can be a property of both clustered and nonclustered indexes.	Create Unique Indexes
Columnstore	An index based on vertical partitioning of the data by columns, stored as large objects (LOB).	Columnstore Index Structures  Columnstore Indexes
Index with included columns	A nonclustered index that is extended to include nonkey columns in addition to the key columns.	Create Indexes with Included Columns
Index on computed columns	An index on a column that is derived from the value of one or more other columns, or certain deterministic inputs.	Indexes on Computed Columns
Filtered	An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.	Create Filtered Indexes
Spatial	A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.	Spatial Indexing Overview
XML	A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.	XML Indexes (SQL Server)
Full-text	A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.	Populate Full-Text Indexes

Database Performance Tuning

Why Performance Tuning?

1. Prevention is better than Cure: The solution to most slow data centric applications is taking care of issues proactively rather than reactively which include:
2. Thorough analysis of the user’s needs
3.  Thoughtful design
4.  Optimal coding
5.  Appropriate implementation

How Queries are Executed

 

Common causes of poor SQL Server performance

1. Poorly written queries and database schema
2. Inadequate disk performance
3. Memory pressure leading to low page life expectancy
4. Long running queries (blocking and locking)

Focus: Query Optimization

1. Execution plans and cost-based optimizations
2. Optimization phases
3. SARG-able clauses – (This basically refers to a condition in a WHERE clause that is able to use an index if one exists)
4. Indices and Distribution statistics
5. Join Selection
6. Execution plan notation

Optimization Process

 

Performance Tuning Best Practices – DON’Ts

1. DO NOT use scalar functions in queries which process a large number of rows. Due to the way SQL Server processes this sort of operation, long query durations can result (although typically without heavy resource use – a common cause of slow running queries).
2. DO NOT use sub-queries unless it is absolutely needed. The entire sub-query result set is often evaluated, even if many of the rows are eliminated immediately by the way the sub-query is joined to the main query.
3. DO NOT use the DISTINCT operator unless it is absolutely necessary. In many cases duplicate rows can be eliminated through restructuring of a query – there are very few circumstances where DISTINCT is actually necessary.
4. DO NOT apply the ORDER BY clause to a set of data unless it is absolutely required (e.g. for front-end presentation). Never sort data into a table where the clustered index sequence differs from the ORDER BY. Sorting data is expensive, and sorting against the clustered index is pointless as SQL Server will automatically re-sequence the data upon insertion.
5. DO NOT use cursors.They use memory; they lock tables in weird ways, and they are slow.
6. DO NOT use the TEXT datatype. Unless you are using it for really large data. The TEXT datatype is not flexible to query, is slow and wastes a lot of space if used incorrectly. Sometimes a VARCHAR will handle your data better.
7. DO NOT use triggers to maintain data in regularly accessed tables. While useful for change-logging, firing triggers at (or from) busy tables can cause serious contention issues.

Performance Tuning Best Practices – DOs

1. DO prefix the identifiers of all objects with the name of their owner to ensure SQL Server immediately selects the correct context. E.g. Prefix all Stored procedures, Tables, Functions and Views owned by the database owner with “dbo”.
2. DO use UNION ALL when possible, instead of UNION – adding the modifier “ALL” prevents SQL Server from performing a DISTINCT operation on the data sets during merging.
3. DO minimize the length of any explicit transactions. The longer a transaction, and the more objects involved, the worse the impact which it will have on concurrency.
4. DO reference indexed columns in join predicates and where clauses. This will allow SQL Server to leverage indexes and minimize the size of the data set being processed earlier rather than later.
5. DO keep code as simple as possible. Several short, simple SQL statements will almost always operate more efficiently than one huge query. This also improves maintainability.

Conclusion:        Query performance tuning is an important part of today’s database applications.
Often you can achieve large savings in both time and money with proper query performance tuning.