Missing index suggesion by Execution Plan - SQL Server by serverku

The index is a most important factor in the optimization and performance. Whenever we work for the query optimization, we are mostly looking for indexes, then check any other factors. Also scheduling the job for the maintenance of the indexes on periodical basis. From Index usage and statistics report, we can have an idea for the index optimization. But SQL Server query execution plan also recommended and give the suggestion with index hint. We will look here the demonstration for the same with execution plan.

--- Creating tables
IF (OBJECT_ID('ItemTypes','U') > 0 )
DROP TABLE ItemTypes
GO

IF (OBJECT_ID('ItemDetails','U') > 0 )
DROP TABLE ItemDetails
GO

CREATE TABLE ItemTypes
(
 ItemType varchar(100),
 ItemTypeDesc varchar(100)
)
GO

CREATE TABLE ItemDetails
(
 ItemId int not null,
 ItemName varchar(100),
 ItemType varchar(10),
 ItemDescription varchar(200)
)
GO


-- Inserting sample records
INSERT INTO ItemDetails
SELECT 
 a.id,
 a.name,
 a.xtype,
 'ItemDesc' 
FROM sys.sysobjects a
CROSS JOIN sys.sysobjects b
GO

INSERT INTO ItemTypes
SELECT distinct 
 type,
 type_desc 
FROM  sys.objects 
GO

-- Review the execution plan.
SELECT 
 id.ItemId,
 id.ItemName,
 id.ItemType,
 it.ItemTypeDesc as ItemTypeDesc,
 id.ItemDescription
FROM ItemDetails id
INNER JOIN ItemTypes it
ON (it.ItemType = id.ItemType)
GO

You can see the index hint with the execution plan, and following is a script for same.

/*
Missing Index Details from SQLQuery.sql - ReportServer (dba (56))
The Query Processor estimates that implementing the following index could improve the query cost by 89.0255%.
*/

/*
USE [ReportServer]
GO
CREATE NONCLUSTERED INDEX []
ON [dbo].[ItemDetails] ([ItemType])
INCLUDE ([ItemId],[ItemName],[ItemDescription])
GO
*/

Apply it and enjoy!

Using force index hint - SQL Server by serverku

I wrote about my previous just learned tips about covering index and will publish my future post for the detail understanding it. We should generate index statistics and index usage report periodically, so we can have more idea of the index utilization. Sometime we require different indexes other than the query optimizer used for the execution for the best performance.

How can we use different indexes other that query optimizer used for execution? You can force the indexes to be used with Index Hint. Here is the small demonstration by example. Please look on below script and created objects for demo.

-- Creating objects
USE DEMO
GO

-- Creating table which will be used for demo

IF (OBJECT_ID('TblForceIndexHint','U') > 0)
DROP TABLE TblForceIndexHint

CREATE TABLE TblForceIndexHint
(
ObjectId bigint,
ObjectName varchar(100),
CreateDate datetime,
ObjectType varchar(100)
)

GO

-- Inserting some sample records in tables 

INSERT INTO TblForceIndexHint
SELECT 
object_id,
name,
create_date,
type_Desc  
FROM SYS.OBJECTS

GO

Now creating one clustered index, Normal non-clustered index and an additional non-clustered index with include means covering index.

-- Creating clustered index on ObjectId column.
CREATE CLUSTERED INDEX IX_ObjectId ON TblForceIndexHint (ObjectId)
GO

-- Creating normal nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate on TblForceIndexHint (CreateDate)
GO

-- Creating covering nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate_Covering ON TblForceIndexHint (CreateDate) INCLUDE (ObjectId,ObjectName,ObjectType)
GO

To review how force Indexes hint works, We need to get the data with CreateDate filtered. So let us start with there.

-- Keep query optimizer to decides and use the index
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to not using any indexes.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (0))
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to using IX_ObjectId indexes created on ObjectId
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint  with (index (IX_ObjectId))
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to using IX_CreateDate indexes created on CreateDate
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate))
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to using IX_CreateDate_Covering indexes created on CreateDate which is additional index.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate_Covering))
WHERE CreateDate >= GETDATE() - 50

Here you see the first query (by Query optimizer) and last query (forcing index hint) have used same index. Reviewing same index hints, but the query will get the data on ObjectId filter from where condition.

-- Keep query optimizer to decide and use the index
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint
WHERE ObjectId >= 1000000

-- Forcing query to not using any indexes.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (0))
WHERE ObjectId >= 1000000

-- Forcing query to using IX_ObjectId indexes created on ObjectId
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint  with (index (IX_ObjectId))
WHERE ObjectId >= 1000000

-- Forcing query to using IX_CreateDate indexes created on CreateDate
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate))
WHERE ObjectId >= 1000000

-- Forcing query to using IX_CreateDate_Covering indexes created on CreateDate which is additional index.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate_Covering))
WHERE ObjectId >= 1000000

Here you see the first query (by Query optimizer) and third query (forcing index hint) have used same index. Please share your experience if you ever used force indexes hint.