How To Optimize SQL Server For Better Performance

Published: Monday October 12, 2020

Indexes are paramount to achieving good database and application performance. Poorly designed indexes and a lack of the same are primary sources of poor SQL Server performance. This article describes an approach for SQL server index optimization to increase query performance.

In general, SQL Server supports many types of indexes but in this article, we assume the reader has a general understanding of the index types available in SQL Server and will only list the most used ones that have the greatest impact on SQL Server index optimization.

Clustered – One important feature of SQL Server is its automatic creation of a clustered index when the primary key is defined for a table.

CREATE TABLE Persons (
    ID int NOT NULL,
    FirstName varchar(255),
    CONSTRAINT PK_Person PRIMARY KEY (ID)
);

Non-clustered – A non-clustered index contains the non-clustered index key values, and each of those keys has a pointer to a data row that contains the key value. This pointer is referred to as a row locator.

A non-clustered index usage example is to create an index on columns for queries that are used often; like in a where clause or on foreign keys.

CREATE NONCLUSTERED INDEX IX_Table_Column   
    ON [schema].[table] (column);   
GO