[SQL Server Performance] Speed up your query with covering index

Do you use the same statement often? Are you facing performance issues on that query? Covering index is what you need :) (Ofc depends on your statement/data size)

So what is covering? Covering index is a technique of designing indexes for a table, not a type of index. I will explain this technique with an example.

Lets take Orders table from Northwind sample db. Orders table has a clustered index on CustomerID. We would like to see shipping fee for daily basis. To get this data execute to following query.

select OrderDate, SUM(Freight) from Northwind.dbo.[Orders] where group by OrderDate





As you can see from execution plan, it scans all clustered index data then sorting and grouping the result from index scan.

Lets put an index for OrderDate and Freight fields and see what happens

CREATE INDEX IX_OrderDate_Freight ON dbo.ORDERS(OrderDate, Freight)

then execute the same query

select OrderDate, SUM(Freight) from Northwind.dbo.[Orders] where group by OrderDate







It is directly accessing to the index and getting all the necessary information from index. Normally if sql scans a non-clustered index and there are some fields in the statement which does not exist in the index, Sql Server engine goes to original record to get the field values for every single record. This may cause a performance issue. In this example as it gets all necessary information from index it will speed up the query. Additionally as non-clustered index is sorted, it saves the engine from making additional sorting process.

Note: When you select your index, try to pick small size ones like smallint, integer, datetime. If you have bigger size fields then the implementation may cost more then you had. Try to prevent to put index on the table which has big number of insert/update/delete operations as well. Because everytime a record is changed, indexes will be reviewed from SQL Server Engine to see if they need to be sorted again.

Difference between Clustered and Non-Clustered Indexes

Most of the performance issues are being faced is about how indexes are implemented. Although indexing is very benefical, it can cause performance problems if it is not well structured. In this post i am going to explain the difference between Clustered and Non-Clustered, and also on which condition we should prevent using indexing.

Following explains the methods we are going to use ;

* Lists all of a table's data and index pages
DBCC IND
(
['database name'|database id], -- the database to use
table name, -- the table name to list results
index id, -- an index_id from sys.indexes; -1 shows all indexes and IAMs, -2 just show IAMs
)

* Setting via trace flag 3604, It allows to instruct the engine to send output to the console
DBCC TRACEON(3604)

* DBCC PAGE statement lists the page header, data rows, and row offset table for any data page in a database.
DBCC PAGE ( {dbid | dbname}, filenum, pagenum [, printopt] [, cache]


Lets have a look how they stored by SQL;


1) Non indexed Table
Tables do not have a default index.

. Create the test table

Create Table TestTable
(
Id Int,
Name Varchar(255)
)

. Insert test data

Insert Into TestTable Values (3, 'Name3')
Insert Into TestTable Values (1, 'Name1')
Insert Into TestTable Values (2, 'Name2')

. Query the table

Select * From TestTable

. See the result

Id Name
-------------- ------------------
3 Name3
1 Name1
1 Name2

As there is no index, data was returned as the same order as they were inserted.

. See the structure of the data
Declare @dbID Int, @TableID Int
Set @dbID = db_id()
Set @TableID = object_id('TestTable')
DBCC ind(@dbID, @TableID, -1)

Here is the result



The columns mean:

* PageFID - the file ID of the page
* PagePID - the page number in the file
* IAMFID - the file ID of the IAM page that maps this page (this will be NULL for IAM pages themselves as they're not self-referential)
* IAMPID - the page number in the file of the IAM page that maps this page
* ObjectID - the ID of the object this page is part of
* IndexID - the ID of the index this page is part of
* PartitionNumber - the partition number (as defined by the partitioning scheme for the index) of the partition this page is part of
* PartitionID - the internal ID of the partition this page is part of
* iam_chain_type - see IAM chains and allocation units in SQL Server 2005
* PageType - the page type. Some common ones are:
1 - data page
2 - index page
3 and 4 - text pages
8 - GAM page
9 - SGAM page
10 - IAM page
11 - PFS page
* IndexLevel - what level the page is at in the index (if at all). Remember that index levels go from 0 at the leaf to N at the root page (except in clustered indexes in SQL Server 2000 and 7.0 - where there's a 0 at the leaf level (data pages) and a 0 at the next level up (first level of index pages))
* NextPageFID and NextPagePID - the page ID of the next page in the doubly-linked list of pages at this level of the index
* PrevPageFID and PrevPagePID - the page ID of the previous page in the doubly-linked list of pages at this level of the index


But we are only interested with PagePID, IndexID and PageType