Feb 26, 2013

When sql server is able to use indexes and when not


As we know we should write sql queries according to indexes on the table as well we should create the indexes on the table according to sql queries. Here we will discuss about when sql server is able to use indexes and when not.  To illustrate this we are creating a table and a non clustered index on it:

--Creating table
CREATE TABLE tblMessage(
     ntMsgID BIGINT PRIMARY KEY IDENTITY,
     ntSeverity INT,
     ntLanguageID INT,
     vcText VARCHAR(100)
)

--Inserting some records into it
INSERT tblMessage
SELECT severity,language_id,LEFT(text,100)   
FROM Sys.messages

--Creating non-clustred index
CREATE NONCLUSTERED INDEX NCI_Severity
ON tblMessage(ntSeverity,ntLanguageID)

In the non clustered index NCI_Severity, first key column is ntSeverity and second key column is ntLanguageID

Rule 1: A sql query use the index if the query uses following operators with the first key column of index:
a. =
b. <= or <
c. >= or >
d. BETWEEN
e. IN or NOT IN
f. <> or !=

For example following sql queries uses the non clustered index NCI_Severity:

SELECT ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntSeverity = 20

SELECT ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntSeverity >= 20

SELECT ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntSeverity BETWEEN 20 AND 30

SELECT ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntSeverity IN (15,16,20)

SELECT ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntSeverity NOT IN (15,16,20)

SELECT  ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntSeverity <> 20

Its actual execution plan:


So all of the above query is using non cluster index NCI_Severity.

Important points:

During the query compilation phase sql server tries to improve the query performances by changing the query to its equivalent syntax. In sql server following operator are equivalent to each others:

1.

a. ntSeverity BETWEEN 20 AND 30
b. ntSeverity >= 20 AND ntSeverity <= 30

2.

a. ntSeverity IN (15,16,20)
b. ntSeverity = 15 OR ntSeverity = 16 OR  ntSeverity = 17

3.

a. ntSeverity NOT IN(15,16,17)
b. ntSeverity <> 15 AND ntSeverity <> 16 AND ntSeverity <> 17
c. (ntSeverity > 15 OR ntSeverity < 15)
AND (ntSeverity > 16 OR ntSeverity < 16)
AND (ntSeverity > 17 OR ntSeverity < 17)

4. <> is also very much equivalent to

a. ntSeverity <> 20
b. ntSeverity != 20
c. ntSeverity > 20 OR ntSeverity < 20

Rule 2: If sql query use the second key column of index in query predicate then must have to use operators =, <, <=, >, >=, IN, NOT INT, BETWEEN, <> etc with the first key column of index to use the index. For example:

Following sql queries will not able to use index NCI_Severity:

SELECT  ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntLanguageID = 1033

SELECT  ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntLanguageID > 1033

SELECT  ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntLanguageID IN (1033,1035)

Since in  where clause there is not any filter condition with first key column i.e. ntSeverity of index NCI_Severity While following sql queries will be able to use index:

SELECT  ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntSeverity > 20 AND ntLanguageID = 1033

SELECT  ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntSeverity = 20 AND ntLanguageID > 1033

SELECT  ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntLanguageID IN (1033,1035) AND ntSeverity <> 20

Important point:

Almost all cases order of predicates in WHERE clause doesn't matter since while query compilation phase sql server reorders the predicates in WHERE clause according to best performance. For example following sql queries are equivalent:

a.

SELECT  ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntSeverity > 20 AND ntLanguageID = 1033

b.

SELECT  ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntLanguageID = 1033 AND ntSeverity > 20


If will check the detail of index seek operator in the execution plan of following sql query:

SELECT  ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntSeverity > 20 AND ntLanguageID = 1033


Predicate ntSeverity > 20 is use index seek while predicate ntLanguageID = 1033 is not able to use index seek.  Why?  Go through Rule 3.

Rule 3: If there are multiple key columns in index  sql server will choose seek operation with other key columns if and only if there must have a predicate with equal to (=) operator with the first key column of an index. For examples:

SELECT  ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntSeverity = 20 AND ntLanguageID = 1033

SELECT  ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntLanguageID > 1033 AND ntSeverity = 20

SELECT  ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntSeverity = 20 AND ntLanguageID <> 1033

SELECT  ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntSeverity = 20 AND ntLanguageID IN (1033,1035)

If will check the detail of index seek operator in the execution plan of following sql query:

SELECT  ntSeverity,ntLanguageID
FROM  tblMessage
WHERE ntSeverity = 20 AND ntLanguageID = 1033



1 comment:

  1. Anonymous8/21/2013

    Good article...very helpful...
    thanx...

    ReplyDelete