SQL Indexes

What are SQL Indexes?

Indexes are used to help the SQL engine to identify faster the required data. There are associated with the data from the tables or the views.

If you don’t have indexes on your columns that you use in the WHERE clause, then the database server has to query the entire table. For instance, you have a table where you keep all the citizens of a city. Using an index on the Social Security Number column will give you the possibility to get the needed row right away by using the identifier.

select * FROM Citizens
WHERE SSN='710-18-5673' -- If a index is used on the SSN column, the server will identify directly the row without to search in the entire table

 

SQL Server Indexes Types

Clustered Index

The clustered index sorts the data based on the value of that column. We use the clustered index when the data has an order. The primary key creates this type of index on that column.

There can be only one clustered index on the table. The reason for this limitation is because you can store the records in only one order.

CREATE TABLE Employee
(
    Id INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Salary decimal(9,1) NOT NULL
 )

 insert into Employee Values (7,'Catwoman',4000)
 insert into Employee Values (6,'Spiderman',199.9)
 insert into Employee Values (1,'Super Man',4000)
 insert into Employee Values (3,'Batman',100)
 insert into Employee Values (2,'John Doe',100)

 select  * FROM Employee

If you execute the above script, you will see that the records will be sorted even if you didn’t insert them in the correct order.Query Result in correct order For most tables, the Id column will be the primary key and will have a clustered index.

Nonclustered Index

A nonclustered index contains the key value and a pointer to the actual row that represents it. For this type of index, the physical order of the rows doesn’t matter.

You should create nonclustered indexes for the columns that are used in the WHERE clause. Also, if you have a column that you used for a join operation and it isn’t a primary key, then you should create a nonclustered index.

CREATE INDEX IX_Employee_Name ON Employee (Name ASC);

The index can also have a unique constraint.

DROP INDEX Employee.IX_Employee_Name; -- drop previous index
CREATE UNIQUE INDEX IX_Employee_Name ON Employee (Name ASC);

 

Execution Plan

Microsoft SQL Server Management Studio offers the option to include the actual execution plan. Press CTRL + M and execute the query.

SQL Execution Plan

A new tab will show up. You can see how the query was performed and what step took the most time. You can hover your mouse over every step to find different statistics and information.

Usually, you will want to see more Index Seek operations instead of Table Scan. This means that the SQL server will look at the index and will not look at every row.

Imagine a table with million records, a table scan will take many seconds to give you the desired result. An index seek operation will get your result much faster.

T-SQL Missing Indexes

The below script will give you a list of possible indexes that you should create. Be careful and don’t create every index that is recommended, because this will cause an overhead.

Every time you run a DML operation (INSERT, UPDATE, DELETE) then the index is automatically updated by the SQL Server.

SELECT
TableName = d.statement,
d.equality_columns,
d.inequality_columns,
d.included_columns,
s.user_scans,
s.user_seeks,
s.avg_total_user_cost,
s.avg_user_impact,
AverageCostSavings = ROUND(s.avg_total_user_cost * (s.avg_user_impact/100.0), 3),
TotalCostSavings = ROUND(s.avg_total_user_cost * (s.avg_user_impact/100.0) * (s.user_seeks + s.user_scans),3)
FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
WHERE d.database_id = db_id()
ORDER BY TableName, TotalCostSavings DESC;

This SQL script will give you statistics about what will be the saving after an index will be created. This is a good starting point to create indexes. For every table, look at the recommended indexes and choose only the columns that will have the greatest impact on total saving time.

List T-SQL Indexes

If you want to see all indexes and how many times were used in seek operations or updated after a DML operation, then you should run the following query.

SELECT
OBJECT_NAME(s.object_id) as TableName,
i.name AS IndexName,
i.type_desc as IndexType,
user_seeks + user_scans + user_lookups as TotalUsage,
user_seeks,
user_scans,
user_lookups,
user_updates
FROM sys.dm_db_index_usage_stats s
RIGHT OUTER JOIN sys.indexes i
ON s.[object_id] = i.[object_id]
 AND s.index_id = i.index_id
WHERE s.database_id=DB_ID()
AND i.name is NOT NULL
and OBJECTPROPERTY(s.[object_id], 'IsMsShipped')=0
ORDER BY s.object_id,s.index_id

If the user_updates column is higher than the user_seeks column this means that the SQL performed more operations to update the index than use it in the queries. You must delete the indexes that require more updates than seek operations if there are no special specifications.

Leave a Comment