Improve the performance by Execution Plan

Introduction

Developers ask a common quest "How to improve the performance of a SQL Query". It is not so easy to answer as lot of factors is related to it. There are some general guidelines that we can follow to improve the overall performance of a query.

But I recommended the execution plan to understand the performance of the query. I preferred execution plan when I am building the query block step by step.

In this article I am trying to show a basic strategy, how to improve a query by observing the query plan.

Prerequisite

To understand this article, we have a very good knowledge of Index, Index Scan, Index Seek, Table scan etc. Please follow the related tropics of this article, to complete this.

Improving Query

To understand it properly, I am taken an example.

Step-1 [ Creating The Base Table ]

-- Creating the Base Table

IF OBJECT_ID('Emp_Dtls') IS NOT NULL

   BEGIN

      DROP TABLE Emp_Dtls;

   END

GO  

CREATE TABLE Emp_Dtls

       (EMPID    INT          NOT NULL IDENTITY,

        EMPNAME  VARCHAR(50)  NOT NULL,

        EMPGRADE VARCHAR(1)   NOT NULL,

        EMPDEPT  VARCHAR(30)  NOT NULL);

GO

Step-2 [ Inserting the Records ]

DECLARE @i INT=1;

BEGIN TRY

BEGIN TRAN

WHILE (@i <= 50000)

BEGIN

      INSERT INTO Emp_Dtls

             (EMPNAME, EMPGRADE, EMPDEPT) 

      VALUES('Developer-'+CONVERT(VARCHAR, @i),'C','DEV');

      SET @i=@i+1;

END

SET @i=1;  

WHILE (@i <= 50000)

BEGIN

      INSERT INTO Emp_Dtls

             (EMPNAME, EMPGRADE, EMPDEPT) 

      VALUES('Devlivery Mgr-'+CONVERT(VARCHAR, @i),'B','DM');

      SET @i=@i+1;

END  

SET @i=1;  

WHILE (@i <= 50000)

BEGIN

      INSERT INTO Emp_Dtls

             (EMPNAME, EMPGRADE, EMPDEPT) 

      VALUES('Manager-'+CONVERT(VARCHAR, @i),'A','MGR');

      SET @i=@i+1;

END  

COMMIT TRAN

END TRY

BEGIN CATCH

ROLLBACK TRAN

END CATCH

Step-3 [ See the Actual Execution Plan ]

-- Execution Plan-1 [ Table Scan ]

SELECT * FROM Emp_Dtls; 

As there is NO INDEX defined it going to TABLE SCAN. So the performance of the SQL Query is worst. We have to improve the performance of the Query.

Step-4 [ Create Clustered Index ]

As there is no index over here and the attribute "EMPID" of Table objects "EMP_DTLS" has INTEGER data type, so it is a good candidate key for CLUSTERED INDEX. Now we are going to create the CLUSTERED INDEX on it.

-- Create Custered Index 

IF EXISTS(SELECT *

          FROM   sys.sysindexes

          WHERE  id = OBJECT_ID('Emp_Dtls')

                 AND name ='IX_CLUS_Emp_Dtls')

   BEGIN

      DROP INDEX Emp_Dtls.IX_CLUS_Emp_Dtls;

   END      

GO

CREATE CLUSTERED INDEX IX_CLUS_Emp_Dtls

ON  Emp_Dtls(EMPID);

Aster creating the CLUSTERED INDEX we are going to see the EXECUTION plan again that it Improves or NOT.

-- Execution Plan-2 [ Clustered Index Scan ]

SELECT * FROM Emp_Dtls;

Now we can see that there is Clustered Index Scan. So the performance is little bit improve. At least it uses the CLSUTERED INDEX.

 Step-5[ Putting WHERE conditions in Query ]

-- Execution Plan-3 [ Using WHERE Conditions ]

SELECT EMPID, EMPNAME FROM Emp_Dtls WHERE EMPGRADE='A'; 

 As the "EMPGRADE" is used in the WHERE conditions we are going to make a NON CLUSTERED Index on it.

-- Now Create Non Clustered Index on EMPGRADE

IF EXISTS(SELECT *

          FROM   sys.sysindexes

          WHERE  id = OBJECT_ID('Emp_Dtls')

                 AND name ='IX_NONCLUS_EMPGRADE')

   BEGIN

      DROP INDEX Emp_Dtls.IX_NONCLUS_EMPGRADE;

   END      

GO

CREATE NONCLUSTERED INDEX IX_NONCLUS_EMPGRADE

ON  Emp_Dtls(EMPGRADE); 

GO

Now again see the execution plan.

-- Execution Plan-4

SELECT EMPID, EMPNAME FROM Emp_Dtls WHERE EMPGRADE='A'; 

Here again the clustered Index is used. The non clustered index that we created is not used here. Why?

You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. By including nonkey columns, you can create nonclustered indexes that cover more queries.

In this Example the "EMPNAME" is a NONKEY Columns.

Step-6[ Solve the Problem ]

-- Non clustered Index with Incluse

IF EXISTS(SELECT *

          FROM   sys.sysindexes

          WHERE  id = OBJECT_ID('Emp_Dtls')

                 AND name ='IX_NONCLUS_EMPGRADE_EMPNAME')

   BEGIN

      DROP INDEX Emp_Dtls.IX_NONCLUS_EMPGRADE_EMPNAME;

   END      

GO

CREATE NONCLUSTERED INDEX IX_NONCLUS_EMPGRADE_EMPNAME

ON Emp_Dtls(EMPGRADE) INCLUDE(EMPNAME);

Now again see the Execution Plan.

-- Execution Plan-5

SELECT EMPID, EMPNAME FROM Emp_Dtls WHERE EMPGRADE='A';

Now the desired output came and it is INDEX SEEK.

Related Tropics

http://sqlknowledgebank.blogspot.in/2012/03/index-scanseek.html

Hope you like it.

Posted by: MR. JOYDEEP DAS