Performance Tuning Steps a Query by looking an execution plan and effectiveness of the index creation Part-1

The index is very important and powerful object in the database. It is an optional and secondary object for the table. It will speed up the data return time by navigating the B-tree structure, like a book’s index.

In General, index tuning is important one, putting a right index will speed up the code.

 Please read this post for finding symptoms, where you have issue at server or database or query level.  Most of the time, the query will be the issue. Once, you have long running query start tuning it.

In this post, I am going to share the usage and effectiveness of index from low level to high level.

There are many application code tuning, which I saw no index created and bad index chosen etc. Why that is the case, since the application developers are not native t- SQL coders and they have limited knowledge that RDBMS has engine and an optimizer etc.

In general the index deigns is handled by application team. But, there are times that no supports from the application and DBA needs to look at the index creation. Use Whoisactive or server side trace and capture the long running query and most of the time the missing index or larger heap table will be the issue. For those cases we can take a control and can create an index.

I am going to show some example to creating an index for a query.

The common terminology we use in the tuning:

============

Predicates – Means where clause & join columns. The predicates is the one needs to be indexed.

select ID from Tbl_Index_Tuning where ID =100

Leading column – First columns of the index from left to right. – ID

Create index ix_Tbl_Index_Tuning_ID_Name_Create_Date on Tbl_Index_Tuning (ID,Name,Create_Date).

Select list – It just a select columns of the query. It should always go to the include of the index.

select * from Tbl_Index_Tuning

Composite Index – index with more than one column.

Create index ix_Tbl_Index_Tuning_ID_Name_Create_Date on Tbl_Index_Tuning (ID,Name,Create_Date).

Covering Index – It is a index will have all the columns of the query in the index or include clasue.

Create index ix_Tbl_Index_Tuning_ID_include on Tbl_Index_Tuning (ID) Include (Name,Create_Date)

Equality – (=) where column1 = ‘a’

select ID from Tbl_Index_Tuning where ID =100

Inequality – (<,>, <>) where column1 <> ‘a’

select ID from Tbl_Index_Tuning where ID >100

Selectivity – High unique value, if we have a bad selectivity the index will not be used effectively.

SELECT COUNT(DISTINCT(id))/COUNT(*)FROM[dbo].Tbl_Index_Tuning

SARG method – Search Argument (Minimize using function, implicit conversion, like ‘%a’ etc)

select ID from Tbl_Index_Tuning where Name like ‘sys%’

============

Before creating an index the parameters we look.

Missing index – I use Whoisactive or server side trace to get the poorly performing query and will create and index. We can also use DMV and Tuning advisor to find missing index , even the execution plan will suggest to create missing indexes.

Example to test:

Following query will create DB and table and load some data to test yourself. There is a drop all index, you can use that to test different test cases.

create database index_Test
--drop database index_Test
select @@version
--Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)

-- Enable this to see the difference of logical and physical reads and Execution Times
SET STATISTICS IO ON
SET STATISTICS TIME ON

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

use index_Test

CREATE TABLE Tbl_Index_Tuning (
ID INT identity,
Name VARCHAR (100),
Create_Date DATETIME DEFAULT GETDATE()
); 


insert into Tbl_Index_Tuning (Name,Create_Date)
Select o.name,o.create_date from  sys.objects o , sys.columns c

-- Drop all index when require

SELECT  'DROP INDEX ' + si.Name + ' ON ' + OBJECT_NAME(object_id)  AS Drop_all_index
FROM  sys.indexes si --join  sys.tables st join si.object_id
WHERE   OBJECT_NAME(object_id) = 'Tbl_Index_Tuning' and si.Name IS NOT null

General guidelines to follow:

1. Check number tables and do count (*).

 – It is always good to do count (*) of all the tables involved in the code to understand the larger table.

2. Being a DBA try to read the code and think about rewrite to run better run time.

 – Read the code and suggest developers to rewrite better set based SARG based codes.

3. How many query involved in the table column (OR) how many times it’s been called, a single index should fulfill more query.

 – In general, the application developer may know how the code and modules are called and which columns are used as a leading column and the combinations of columns etc. It always, good to create a minimal index that should fulfill more queries.

4. Is the table is heap, does it have a candidate column for clustered index .

– I always try to create a clustered index for larger tables, when we have a right candidate or sometimes, I choose dummy unique columns for VLT. It is always better to have a clustered index. Note that, default the primary key will create clustered index, unless we change to non clustered index.

5. Check the leading column that should have a high selectivity.

High selectivity will be good that index will be useful, there are cases we use a low selectivity in the combinations.  – Ex: Where emp_id =10 and emp_name =’mike’ – good empid or sometimes, we do not have good candidate.

Formula to find high selectivity:

SELECT COUNT(DISTINCT(ID))/COUNT(*)FROM[dbo].Tbl_Index_Tuning

 

6. Remove Lookups by including that column as covering index.

Create the following index and run query with actual execution plan. Try how to remove the lookup.

Create index ix_Tbl_Index_Tuning_ID on Tbl_Index_Tuning (ID)

--Drop index ix_Tbl_Index_Tuning_ID on Tbl_Index_Tuning

select * from Tbl_Index_Tuning WHERE ID = 100

 

By creating covering index with include clause will remove the lookup, since all the data is available in the index.

-- create index only to ID and include Name & Create_Date and check

Create index ix_Tbl_Index_Tuning_ID_include on Tbl_Index_Tuning (ID) Include (Name,Create_Date)

--Drop index ix_Tbl_Index_Tuning_ID_include on Tbl_Index_Tuning

select * from Tbl_Index_Tuning WHERE ID = 100 --AND Name = 'sysxsrvs' AND Create_Date > '2011-02-10 20:16:01.910'

 

Ex:

7. Column order and how do we choose index column.

This is bit big example to test

– Creating index for combination of columns in single index for all columns (OR) each column need an index.

It depends on the condition Operators and Expressions, we used in the code. It always better to create a combination of columns – composite index, when we know the underlying query and application.

In the following example drop all index and try one by one with creating index and run select statement with execution plan to understand better.

-- Drop all index when require

SELECT  'DROP INDEX ' + si.Name + ' ON ' + OBJECT_NAME(object_id)  AS Drop_all_index
FROM  sys.indexes si --join  sys.tables st join si.object_id
WHERE   OBJECT_NAME(object_id) = 'Tbl_Index_Tuning' and si.Name IS NOT null

--
Create index ix_Tbl_Index_Tuning_ID on Tbl_Index_Tuning (ID) 
--Drop index ix_Tbl_Index_Tuning_ID on Tbl_Index_Tuning 
select * from Tbl_Index_Tuning WHERE ID = 100
select ID from Tbl_Index_Tuning WHERE ID = 100 AND Name = 'sysxsrvs' AND Create_Date > '2011-02-10 20:16:01.910'
--

-- create index only to ID and include Name & Create_Date and check 
Create index ix_Tbl_Index_Tuning_ID_include on Tbl_Index_Tuning (ID) Include (Name,Create_Date) 
--Drop index ix_Tbl_Index_Tuning_ID_include on Tbl_Index_Tuning 
select * from Tbl_Index_Tuning WHERE ID = 100
select * from Tbl_Index_Tuning WHERE ID = 100 AND Name = 'sysxsrvs' AND Create_Date > '2011-02-10 20:16:01.910'


------------
SELECT  'DROP INDEX ' + si.Name + ' ON ' + OBJECT_NAME(object_id)  AS Drop_all_index
FROM  sys.indexes si --join  sys.tables st join si.object_id
WHERE   OBJECT_NAME(object_id) = 'Tbl_Index_Tuning' and si.Name IS NOT null

--DROP INDEX ix_Tbl_Index_Tuning_ID ON Tbl_Index_Tuning
--DROP INDEX ix_Tbl_Index_Tuning_ID_include ON Tbl_Index_Tuning

-- create combination columns composite index for ID, Name & Create_Date and check
Create index ix_Tbl_Index_Tuning_ID_Name_Create_Date on Tbl_Index_Tuning (ID,Name,Create_Date)
--drop index ix_Tbl_Index_Tuning_ID_Name_Create_Date on Tbl_Index_Tuning

select * from Tbl_Index_Tuning WHERE ID = 100 AND Name = 'sysxsrvs' AND Create_Date > '2011-02-10 20:16:01.910'
select * from Tbl_Index_Tuning where ID = 100 AND Name = 'sysxsrvs'
select * from Tbl_Index_Tuning WHERE ID = 100 AND Create_Date > '2011-02-10 20:16:01.910'

-- This will not use index, since the leading column does not has index
select * from Tbl_Index_Tuning where Name = 'sysxsrvs' AND Create_Date > '2011-02-10 20:16:01.910'
-- See, This will use index, since the ID column has index and it is added in the condition
-- There is always no need to have leadning column as first of index for true condiions
select * from Tbl_Index_Tuning where Name = 'sysxsrvs' AND Create_Date > '2011-02-10 20:16:01.910' AND ID =100

-- The leading/first column is changed, the index is not used
select * from Tbl_Index_Tuning WHERE Create_Date > '2011-02-10 20:16:01.910'
-- seek is going on, Since AND TRUE if both Boolean expressions are TRUE.
select * from Tbl_Index_Tuning WHERE Create_Date > '2011-02-10 20:16:01.910' and ID = 100 
-- Scan is going on, since OR if either Boolean expression is TRUE, which meand one is flase.
select * from Tbl_Index_Tuning WHERE Create_Date > '2011-02-10 20:16:01.910' OR ID = 100 

-- When optimizer thinks to return large portion of data scans it 
-- See this says, Even we have all column indexed and true condition used , if we get all rows from table , it simply scan it.
select * from Tbl_Index_Tuning WHERE ID > 100 AND Create_Date > '2011-02-10 20:16:01.910'
-- See top 1 row with same condition
select top 1 * from Tbl_Index_Tuning WHERE ID > 100 AND Create_Date > '2011-02-10 20:16:01.910'


-- Drop that composite index and try the same above query with single index for each column
-- This will not use index
SELECT  'DROP INDEX ' + si.Name + ' ON ' + OBJECT_NAME(object_id)  AS Drop_all_index
FROM  sys.indexes si --join  sys.tables st join si.object_id
WHERE   OBJECT_NAME(object_id) = 'Tbl_Index_Tuning' and si.Name IS NOT null

DROP INDEX ix_Tbl_Index_Tuning_ID_Name_Create_Date ON Tbl_Index_Tuning

Create index ix_Tbl_Index_Tuning_ID on Tbl_Index_Tuning (ID)
Create index ix_Tbl_Index_Tuning_Name on Tbl_Index_Tuning (Name)
Create index ix_Tbl_Index_Tuning_Create_Date on Tbl_Index_Tuning (Create_Date)

--drop index ix_Tbl_Index_Tuning_ID on Tbl_Index_Tuning
--drop index ix_Tbl_Index_Tuning_Name on Tbl_Index_Tuning
--drop index ix_Tbl_Index_Tuning_Create_Date on Tbl_Index_Tuning

--The index is not combined and it's using a ID index and going to a lookup to heap table to get a remaining data

select * from Tbl_Index_Tuning WHERE ID = 100 AND Name = 'sysxsrvs' AND Create_Date > '2011-02-10 20:16:01.910'

The above example clears most of the confusions.

if you have index with different leading column and other columns are added as composite index, if the query does not has the leading column, then the index is not used

There is always no need to have leadning column as first of index for true condiions

When optimizer thinks to return large portion of data, Even we have all coulmn indexed and true condition used , if we get all rows from table , then it scan it.

 

8. Column order is more important in the index with true and false condition.

Ex: Where emp_id = 1 (OR) Where emp_id = 1 and emp_name=’a’ etc.

9. Main important thing here is the column order in the condition from Left to Right.

The leading column/first column should have high selectivity/ unique value in the index statement. The reason SQL will only have “statistics” of first column, so that the index will be useful to get the data. The statistics is another topic will be covered in this post.

10. Constraints and indexes are different and thing we need to look “how Unique” – I see still some are thinking that primary key and clustered index are same and we need a PK to have CI.