Share via

SQL Server Performance Tuning with Indexing

  • Article

Introduction

Poor data model, poorly written queries and poor indexing strategies can result in performance degradation of an OLTP system.

When it comes to performance tuning, the queries should be re-written in the best way to fetch the data and then we can create indexes to boost the performance.

Keep in mind, creating index is not the first step in performance tuning and also once indexes are created, we should plan effectively for index storage and maintenance to make the created index boost the performance rather than degrading it.

There are also other factors to be considered to improve performance:

Performance tuning with Indexing

What are the types of indexes available in SQL Server?

Clustered, NonClustered, Columnstore, Index with included columns, Index on computed columns, Filtered, Spatial, Full-text and XML indexes

What is a Clustered and Nonclustered index?

Clustered and Nonclustered Indexes Described.

When creating indexes, what is the maximum index key size allowed?

SQL Server retains the 900-byte limit for the maximum total size of all index key columns.

How to estimate the amount of space that is required to store data in a clustered index?

To estimate the Size of a Clustered Index.

When do you see SORT operator in the query plan and how it can be removed?

Specifying the order in which key values are to be stored in an index is useful when queries referencing the table have ORDER BY clauses.

How to avoid Key/RID lookup?

Refer Example 1.

Is it a best practice to create clustered index on a table before creating non-clustered indexes?

Yes, Refer Example 1.1.

Does order of columns matters when creating indexes?

Yes, Refer Example 2.

How data types degrades Index performance?

Refer Example 3.1 and Example 3.2.

What is the Fill Factor setting in Index and how can we avoid page splits?

Fill factor and Page splits.

What is the purpose of Pad_Index setting in an index?

PAD_INDEX .

Index Maintenance

If the Indexed table is going to have frequent DML operations then Index maintenance is a must task to make full use of created index.

Whether to REBUILD or REORGANISE Index?

Based on the column value avg_fragmentation_in_percent of the system function sys.dm_db_index_physical_stats, we can decide whether to REBUILD or REORGANISE Index.

How to allow concurrent users to access the underlying table or clustered index data and any associated nonclustered indexes during index operations?

To Perform Index Operations Online.

Statistics holds information about the distribution of data in tables & indexes. After DML operations on a table, statistics will get affected. Good statistics allow the optimizer to accurately assess the cost of different query plans and then choose a high-quality plan.

Basics of Statistics.
Statistics Used by the Query Optimizer.

After rebuilding the index can we update the statistics immediately?
Rebuilding indexes and updating statistics.

What is Index Fragmentation?
http://technet.microsoft.com/en-us/sqlserver/gg429790.aspx
http://blog.sqlauthority.com/2010/01/12/sql-server-fragmentation-detect-fragmentation-and-eliminate-fragmentation/

Disk Space Requirements for Index DDL Operations

What happens and when to disable Indexes?
Disabling Indexes.

Will Inadequate disk space can cause the index operation to fail?
Yes.

SORT_IN_TEMPDB Option For Indexes.
Transaction Log Disk Space for Index Operations.
Index Disk Space Example.

See Also