During the performance issues of database servers and database itself, one of my activities to improve performance of the Indexes. I have created some of the clustered indexes on tables which have only non-clustered indexes and also on some heap tables. Also, I have created some additional non-clustered indexes called as Covering indexes and some of having composite indexes.
After creating or having so many indexes on tables, it is real time to check the usage of them as which indexes are used by execution plans or query plans among them. We should review the index statistics and usage report of all the indexes of tables as how they are seeking, scanned and update. Let us pick one example and will create one table and some of the indexes as well.
(Click on image to enlarge)
Once again we will create a more indexes, but they are additional covering indexes.
(Click on image to enlarge)
You can see here the difference between both of the execution plans and impact of indexes on queries and the index used for the same.
After creating or having so many indexes on tables, it is real time to check the usage of them as which indexes are used by execution plans or query plans among them. We should review the index statistics and usage report of all the indexes of tables as how they are seeking, scanned and update. Let us pick one example and will create one table and some of the indexes as well.
USE DEMO GO -- Creating table which will be used for demo IF (OBJECT_ID('TblIndexUsed','U') > 0) DROP TABLE TblIndexUsed CREATE TABLE TblIndexUsed ( Id bigint identity(1,1), ObjectId bigint, ObjectName varchar(100), CreateDate datetime, ObjectType varchar(100) ) GO -- Inserting some sample records in tables INSERT INTO TblIndexUsed SELECT object_id, name, create_date, type_Desc FROM SYS.OBJECTS GO 10000Now We will create one clustered index, Normal non-clustered indexes, We are not creating additional non-clustered index here.
-- Creating clustered index on ObjectId column. CREATE CLUSTERED INDEX IX_UObjectIdDate ON TblIndexUsed (Id,CreateDate) GO -- Creating normal nonclustered index on CreateDate column. CREATE NONCLUSTERED INDEX IX_UId on TblIndexUsed (Id) -- Creating normal nonclustered index on CreateDate column. CREATE NONCLUSTERED INDEX IX_UCreateDate on TblIndexUsed (CreateDate) GONow we are running the below query to check the usage of the above created index on the table.
SELECT ID, ObjectId, ObjectName, CreateDate, ObjectType FROM TblIndexUsed WHERE ID >=30000 and CreateDate >= GETDATE() - 50 SELECT ID, ObjectId, ObjectName, CreateDate, ObjectType FROM TblIndexUsed WHERE CreateDate >= GETDATE() - 50 and ID >=30000 SELECT ID, ObjectId, ObjectName, CreateDate, ObjectType FROM TblIndexUsed WHERE ID >= 30000 SELECT ID, ObjectId, ObjectName, CreateDate, ObjectType FROM TblIndexUsed WHERE CreateDate >= GETDATE() - 50
(Click on image to enlarge)
Once again we will create a more indexes, but they are additional covering indexes.
-- Creating covering nonclustered index on CreateDate column. CREATE NONCLUSTERED INDEX IX_UId_Covering ON TblIndexUsed (Id)
INCLUDE (ObjectId,ObjectName,CreateDate,ObjectType) GO -- Creating covering nonclustered index on CreateDate column. CREATE NONCLUSTERED INDEX IX_UCreateDate_Covering ON TblIndexUsed (CreateDate) INCLUDE (Id,ObjectId,ObjectName,ObjectType) GOAfter creating covering indexes we are reviewing execution plans again for the same above queries.
SELECT ID, ObjectId, ObjectName, CreateDate, ObjectType FROM TblIndexUsed WHERE ID >=30000 and CreateDate >= GETDATE() - 50 SELECT ID, ObjectId, ObjectName, CreateDate, ObjectType FROM TblIndexUsed WHERE CreateDate >= GETDATE() - 50 and ID >=30000 SELECT ID, ObjectId, ObjectName, CreateDate, ObjectType FROM TblIndexUsed WHERE ID >= 30000 SELECT ID, ObjectId, ObjectName, CreateDate, ObjectType FROM TblIndexUsed WHERE CreateDate >= GETDATE() - 50
(Click on image to enlarge)
You can see here the difference between both of the execution plans and impact of indexes on queries and the index used for the same.
Some good points. IT is also important to maintain statistics , so the optimizer can make the right choice.
ReplyDeletehttp://www.sqlserver-dba.com/2011/05/sql-server-query-optimizer-and-statistics-.html