Optimizing SQL Server Tables

In this blog we will discuss optimizing SQL server tables using indexes. A heap is a table without a clustered index. If a table is a heap and does not have any nonclustered indexes, then the entire table must be examined (a table scan) to find any row (*). I will be using a customer’s dimension table from ContosoRetailDW sample database to demonstrate.

Let us first check what index we have on the table

--Check index on the table
 
sp_helpindex 'dbo.DimCustomer';

I have a simple query that we run on the table and we want to optimize the table for it. Let us run the script by including the actual execution plan. To check the IO usage of of the query let us turn that on.

--Check the table performance
 
SET STATISTICS IO ON;
SELECT FirstName,LastName
FROM dbo.DimCustomer
WHERE LastName='Smith';

The result set is not import for our exercise but we got 100% scan on the clustered index. That is not a good execution plan at all. If you click on the Message tab we could also see that the IO usage of the query is 753 logical reads. Which again is not good for such small result set.

SQL server check every record on the table to provide us the result set. The next logical thing will be adding a nonclusted index on the table. As you could see above the execution plan SQL Server suggested what index to create with green color. Right click on the plan and select Missing Index Details…

For our exercise let use create our own index and move on.

--Add nonclusted index on LastName
 
CREATE NONCLUSTERED INDEX ncl_Customer_Name
ON dbo.DimCustomer(LastName);

Let us check the performance of our query again

--Check the table performance
 
SET STATISTICS IO ON;
SELECT FirstName,LastName
FROM dbo.DimCustomer
WHERE LastName='Smith';

We have a Nested Loops with Key Lookup this time and 305 logical reads. This much better than the previous table scan plan but still not the best possible plan. Okay next let us use a cover index to get a better performance on the table. A cover index is nothing but a nonclusted index on more than one column of a given table to satisfy all request. In our case we need to create a nonclusted index on both LastName and FirstName.

--Add cover index on LastName and FirstName
 
CREATE NONCLUSTERED INDEX ncl_Customer_Name
ON dbo.DimCustomer(LastName,FirstName)
WITH (DROP_EXISTING=ON);

Let us check the performance of our query again

--Check the table performance
 
SET STATISTICS IO ON;
 
SELECT FirstName,LastName
FROM dbo.DimCustomer
WHERE LastName='Smith';

This gives us a 100% seek on our nonclusted index which is the best plan. In this case we only have 3 logical reads and efficient IO usage as well.