This is the first session to show up how you can identify the missing indexes on your project.
First of all, I've developed a new temporary table for tests:
--// Create a temporary table
--============================================================
Create table tbLabIndexMissing(id int, descriptionValue varchar(200), CreatedDate datetime)
--// Load 1 milion of data into the temporary table
--============================================================
Declare @count int = 0
While @count <= 1000000 Begin
Insert tbLabIndexMissing
Select @count, concat('Data test performance :-',@count), getdate()
set @count += 1
End
--// Test 01 - Cleaning the Execution Plan for an accurate test
--===================================================================================
DBCC FREEPROCCACHE
--// Test 01 - Index Validation (Missing Data) using the Execution Plan
--============================================================
Select
DescriptionValue,
CreatedDate
From tbLabIndexMissing
Where Id = 500
-- >> Select the SQL statement query, press the keys [Alt] + [L] on your keyboard
This is the expected result:
Explanation:
Table Scan - Means missing indexes
--// Test 01 - Fixing the Missing Indexes - Using the Statistics Analyzer
--===================================================================================
Set Statistics Io On
Set Statistics Time On
Select
DescriptionValue,
CreatedDate
From tbLabIndexMissing
Where Id = 500
-- >> This is my preference to analyze the indexes, I have more power to see all the statistics for a better improvement
This is the expected result:
Explanation:
Logical Reads - Greater than 1000 means inefficient or missing indexes
--// Test 01 - Missing Indexes Solution (THE BEST SOLUTION)
--===================================================================================
Create Index IDX_tbLabIndexMissing_01 on tbLabIndexMissing(ID) Include (DescriptionValue,CreatedDate)
-- >> This is THE BEST INDEX because it's avoiding the Look Up Head, making the SQL live easier. You should include the fields from the table you are indexing as long it does not exceed 10 fields (recommendation).
--// Test 01 - Missing Indexes Solution (CAREFUL - THE WORSE SOLUTION)
--===================================================================================
Create Index IDX_tbLabIndexMissing_01 on tbLabIndexMissing(ID)
-- >> It's causing Look Up Head, making inefficient the indexes.
