uncovering duplicate, redundant, & missing indexes · leaf level...
TRANSCRIPT
Uncovering Duplicate, Redundant, & Missing Indexes
Andy YunSenior Solutions Engineer
Andy YunSenior Solutions Engineer
• SQL Server DBA & DB Developer• Working with SQL Server since 2001• Chicago Suburban User Group Chapter Leader• Speaking since Early 2014
Place your photo here
@SQLBek
[email protected] | [email protected]
https://blogs.sentryone.com/andyyun/
Have You Ever Asked?
1. Are there any indexes that I do not need anymore?
2. Is SQL Server even using of my indexes?
3. What additional indexes could help my current workload?
Why Reassess My Indexes?
1. Optimize & aid as many of my queries as possible
2. Reduce DML query overhead
3. Reduce data storage footprint
Presentation Agenda
1. Index Internals
2. T-SQL Tools
3. Index Analysis Demo
Index Internals:B-Trees & Index Operations
Customer TableCREATE TABLE dbo.Customer (
CustomerID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,FirstName VARCHAR(50),LastName VARCHAR(50),Address VARCHAR(50),City VARCHAR(50),State CHAR(2),ZipCode CHAR(5),Email VARCHAR(50),PhoneNumber CHAR(10),FirstVisit DATE,RepeatCustomer BIT
);CREATE NONCLUSTERED INDEX IX_Customer_LastName
ON dbo.Customer (LastName, FirstName) INCLUDE (FirstVisit, Email);
Index B-TreeStructure
Adams,James,29285Martinez,Luis,29214Simon,Paul,25946
Adams,James,29285Chavez,Esme,23555Howard,Kate,29334
Martinez,Luis,29214Raji,Bo,26523Samant,Mike,12873
Simon,Paul,25946Truempy,Nick,14558
Adams,James,2002-02-10,29285Agbonile,Lisa,2004-01-01,18083Alan,Pete,2003-10-06,15384
Chavez,Esme,2003-09-26,23555Chen,Li,2004-05-13,29031Chow,Matt,2001-11-12,28644
Howard,Kate,2002-02-14,29334Hu,Li,2002-01-28,29203Huang,Ilo,2002-07-30,26276
Martinez,Luis,2003-08-19,29214McDon,Jynell,2004-03-09,28084Mehta,Rit,2004-05-19,27436
Raji,Bo,2004-04-20,26523Raman,Arthur,2001-12-20,28990Ramirez,Lori,2002-01-17,29198
Samant,Mike,2003-05-10,12873Sanchez,Ray,2002-01-17,29254Sandberg,Ryne,2003-11-06,23075
Simon,Paul,2002-06-28,25946Simpson,2003-01-08,26717Sims,2003-12-28,26869
Truempy,Nick,2001-07-28,4558Turner,Tina,2002-02-17,29279Umeda,Umesh,2003-10-13,16717
CREATE NONCLUSTERED INDEX IX_Customer_LastName ON
dbo.Customer (LastName, FirstName) INCLUDE (FirstVisit, Email);
Root Level
➢ Adams,James,29285
➢ Martinez,Luis,29214
➢ Simon,Paul,25946
CREATE NONCLUSTERED INDEX IX_Customer_LastName
ON dbo.Customer(LastName, FirstName)
INCLUDE (FirstVisit, Email);
Intermediate Level
➢ Adams,James,29285
➢ Chavez,Esme,23555
➢ Howard,Kate,29334
➢ Martinez,Luis,29214
➢ Raji,Bo,26523
➢ Samant,Mike,12873
➢ Adams,James,29285
➢ Martinez,Luis,29214
➢ Simon,Paul,25946
CREATE NONCLUSTERED INDEX IX_Customer_LastName
ON dbo.Customer(LastName, FirstName)
INCLUDE (FirstVisit, Email);
Leaf Level
➢ Adams,James,2002-02-10,[email protected],29285
➢ Agbonile,Lisa,2004-01-01,[email protected],18083
➢ Alan,Pete,2003-10-06,[email protected]
CREATE NONCLUSTERED INDEX IX_Customer_LastName
ON dbo.Customer(LastName, FirstName)
INCLUDE (FirstVisit, Email);
➢ Chavez,Esme,2003-09-26,[email protected],23555
➢ Chen,Li,2004-05-13,[email protected]
➢ Chow,Matt,2001-11-12,[email protected]
➢ Adams,James,29285
➢ Chavez,Esme,23555
➢ Howard,Kate,29334
Index Scan Adams,James,29285Martinez,Luis,29214Simon,Paul,25946
Adams,James,29285Chavez,Esme,23555Howard,Kate,29334
Martinez,Luis,29214Raji,Bo,26523Samant,Mike,12873
Simon,Paul,25946Truempy,Nick,14558
Adams,James,2002-02-10,29285Agbonile,Lisa,2004-01-01,18083Alan,Pete,2003-10-06,15384
Chavez,Esme,2003-09-26,23555Chen,Li,2004-05-13,29031Chow,Matt,2001-11-12,28644
Howard,Kate,2002-02-14,29334Hu,Li,2002-01-28,29203Huang,Ilo,2002-07-30,26276
Martinez,Luis,2003-08-19,29214McDon,Jynell,2004-03-09,28084Mehta,Rit,2004-05-19,27436
Raji,Bo,2004-04-20,26523Raman,Arthur,2001-12-20,28990Ramirez,Lori,2002-01-17,29198
Samant,Mike,2003-05-10,12873Sanchez,Ray,2002-01-17,29254Sandberg,Ryne,2003-11-06,23075
Simon,Paul,2002-06-28,25946Simpson,2003-01-08,26717Sims,2003-12-28,26869
Truempy,Nick,2001-07-28,4558Turner,Tina,2002-02-17,29279Umeda,Umesh,2003-10-13,16717
SELECT FirstName, LastName, FirstVisit
FROM Customer
Logical Reads?10 Data Pages
Index Seek Adams,James,29285Martinez,Luis,29214Simon,Paul,25946
Adams,James,29285Chavez,Esme,23555Howard,Kate,29334
Martinez,Luis,29214Raji,Bo,26523Samant,Mike,12873
Simon,Paul,25946Truempy,Nick,14558
Adams,James,2002-02-10,29285Agbonile,Lisa,2004-01-01,18083Alan,Pete,2003-10-06,15384
Chavez,Esme,2003-09-26,23555Chen,Li,2004-05-13,29031Chow,Matt,2001-11-12,28644
Howard,Kate,2002-02-14,29334Hu,Li,2002-01-28,29203Huang,Ilo,2002-07-30,26276
Martinez,Luis,2003-08-19,29214McDon,Jynell,2004-03-09,28084Mehta,Rit,2004-05-19,27436
Raji,Bo,2004-04-20,26523Raman,Arthur,2001-12-20,28990Ramirez,Lori,2002-01-17,29198
Samant,Mike,2003-05-10,12873Sanchez,Ray,2002-01-17,29254Sandberg,Ryne,2003-11-06,23075
Simon,Paul,2002-06-28,25946Simpson,2003-01-08,26717Sims,2003-12-28,26869
Truempy,Nick,2001-07-28,4558Turner,Tina,2002-02-17,29279Umeda,Umesh,2003-10-13,16717
SELECT FirstName, LastName, FirstVisit
FROM CustomerWHERE LastName = ‘Dean’
Logical Reads?3 Data Pages
Index Scan…or Seek?
Adams,James,29285Martinez,Luis,29214Simon,Paul,25946
Adams,James,29285Chavez,Esme,23555Howard,Kate,29334
Martinez,Luis,29214Raji,Bo,26523Samant,Mike,12873
Simon,Paul,25946Truempy,Nick,14558
Adams,James,2002-02-10,29285Agbonile,Lisa,2004-01-01,18083Alan,Pete,2003-10-06,15384
Chavez,Esme,2003-09-26,23555Chen,Li,2004-05-13,29031Chow,Matt,2001-11-12,28644
Howard,Kate,2002-02-14,29334Hu,Li,2002-01-28,29203Huang,Ilo,2002-07-30,26276
Martinez,Luis,2003-08-19,29214McDon,Jynell,2004-03-09,28084Mehta,Rit,2004-05-19,27436
Raji,Bo,2004-04-20,26523Raman,Arthur,2001-12-20,28990Ramirez,Lori,2002-01-17,29198
Samant,Mike,2003-05-10,12873Sanchez,Ray,2002-01-17,29254Sandberg,Ryne,2003-11-06,23075
Simon,Paul,2002-06-28,25946Simpson,2003-01-08,26717Sims,2003-12-28,26869
Truempy,Nick,2001-07-28,4558Turner,Tina,2002-02-17,29279Umeda,Umesh,2003-10-13,16717
SELECT FirstName, LastName, FirstVisit
FROM CustomerWHERE LastName > ‘Inez’
AND LastName < ‘Sark’Logical Reads?6 Data Pages
Index Scan…or Seek?
Adams,James,29285Martinez,Luis,29214Simon,Paul,25946
Adams,James,29285Chavez,Esme,23555Howard,Kate,29334
Martinez,Luis,29214Raji,Bo,26523Samant,Mike,12873
Simon,Paul,25946Truempy,Nick,14558
Adams,James,2002-02-10,29285Agbonile,Lisa,2004-01-01,18083Alan,Pete,2003-10-06,15384
Chavez,Esme,2003-09-26,23555Chen,Li,2004-05-13,29031Chow,Matt,2001-11-12,28644
Howard,Kate,2002-02-14,29334Hu,Li,2002-01-28,29203Huang,Ilo,2002-07-30,26276
Martinez,Luis,2003-08-19,29214McDon,Jynell,2004-03-09,28084Mehta,Rit,2004-05-19,27436
Raji,Bo,2004-04-20,26523Raman,Arthur,2001-12-20,28990Ramirez,Lori,2002-01-17,29198
Samant,Mike,2003-05-10,12873Sanchez,Ray,2002-01-17,29254Sandberg,Ryne,2003-11-06,23075
Simon,Paul,2002-06-28,25946Simpson,2003-01-08,26717Sims,2003-12-28,26869
Truempy,Nick,2001-07-28,4558Turner,Tina,2002-02-17,29279Umeda,Umesh,2003-10-13,16717
SELECT FirstName, LastName, FirstVisitFROM CustomerWHERE LastName > ‘Inez’
AND LastName < ‘Sark’ORDER BY LastName DESC
Index Scan…or Seek?
Adams,James,29285Martinez,Luis,29214Simon,Paul,25946
Adams,James,29285Chavez,Esme,23555Howard,Kate,29334
Martinez,Luis,29214Raji,Bo,26523Samant,Mike,12873
Simon,Paul,25946Truempy,Nick,14558
Adams,James,2002-02-10,29285Agbonile,Lisa,2004-01-01,18083Alan,Pete,2003-10-06,15384
Chavez,Esme,2003-09-26,23555Chen,Li,2004-05-13,29031Chow,Matt,2001-11-12,28644
Howard,Kate,2002-02-14,29334Hu,Li,2002-01-28,29203Huang,Ilo,2002-07-30,26276
Martinez,Luis,2003-08-19,29214McDon,Jynell,2004-03-09,28084Mehta,Rit,2004-05-19,27436
Raji,Bo,2004-04-20,26523Raman,Arthur,2001-12-20,28990Ramirez,Lori,2002-01-17,29198
Samant,Mike,2003-05-10,12873Sanchez,Ray,2002-01-17,29254Sandberg,Ryne,2003-11-06,23075
Simon,Paul,2002-06-28,25946Simpson,2003-01-08,26717Sims,2003-12-28,26869
Truempy,Nick,2001-07-28,4558Turner,Tina,2002-02-17,29279Umeda,Umesh,2003-10-13,16717
SELECT FirstName, LastName, FirstVisit
FROM CustomerWHERE FirstName = ‘Arthur’
Logical Reads?10 Data Pages
Is SeekingAlways Better?
Adams,James,29285Martinez,Luis,29214Simon,Paul,25946
Adams,James,29285Chavez,Esme,23555Howard,Kate,29334
Martinez,Luis,29214Raji,Bo,26523Samant,Mike,12873
Simon,Paul,25946Truempy,Nick,14558
Adams,James,2002-02-10,29285Agbonile,Lisa,2004-01-01,18083Alan,Pete,2003-10-06,15384
Chavez,Esme,2003-09-26,23555Chen,Li,2004-05-13,29031Chow,Matt,2001-11-12,28644
Howard,Kate,2002-02-14,29334Hu,Li,2002-01-28,29203Huang,Ilo,2002-07-30,26276
Martinez,Luis,2003-08-19,29214McDon,Jynell,2004-03-09,28084Mehta,Rit,2004-05-19,27436
Raji,Bo,2004-04-20,26523Raman,Arthur,2001-12-20,28990Ramirez,Lori,2002-01-17,29198
Samant,Mike,2003-05-10,12873Sanchez,Ray,2002-01-17,29254Sandberg,Ryne,2003-11-06,23075
Simon,Paul,2002-06-28,25946Simpson,2003-01-08,26717Sims,2003-12-28,26869
Truempy,Nick,2001-07-28,4558Turner,Tina,2002-02-17,29279Umeda,Umesh,2003-10-13,16717
SELECT FirstName, LastName, FirstVisitFROM CustomerWHERE LastName IN (‘Alan’, ‘Chen’, ‘Mehta’, ‘Sandberg’, ‘Simon’, ‘Turner’)
Logical Reads?18 Data Pages
Full Scan?10 Data Pages
Key Lookup Adams,James,29285Martinez,Luis,29214Simon,Paul,25946
Adams,James,29285Chavez,Esme,23555Howard,Kate,29334
Martinez,Luis,29214Raji,Bo,26523Samant,Mike,12873
Simon,Paul,25946Truempy,Nick,14558
Adams,James,2002-02-10,29285Agbonile,Lisa,2004-01-01,18083Alan,Pete,2003-10-06,15384
Chavez,Esme,2003-09-26,23555Chen,Li,2004-05-13,29031Chow,Matt,2001-11-12,28644
Howard,Kate,2002-02-14,29334Hu,Li,2002-01-28,29203Huang,Ilo,2002-07-30,26276
Martinez,Luis,2003-08-19,29214McDon,Jynell,2004-03-09,28084Mehta,Rit,2004-05-19,27436
Raji,Bo,2004-04-20,26523Raman,Arthur,2001-12-20,28990Ramirez,Lori,2002-01-17,29198
Samant,Mike,2003-05-10,12873Sanchez,Ray,2002-01-17,29254Sandberg,Ryne,2003-11-06,23075
Simon,Paul,2002-06-28,25946Simpson,2003-01-08,26717Sims,2003-12-28,26869
Truempy,Nick,2001-07-28,4558Turner,Tina,2002-02-17,29279Umeda,Umesh,2003-10-13,16717
SELECT FirstName, LastName, FirstVisit, City, State
FROM CustomerWHERE LastName = ‘Huang’
Index Operation Recap
• Index Key Order Matters. Can only seek on leading key column
• Add Appropriate Columns as Included Columns to “Cover a Query”
• Seeks are not necessarily better than Scans –Check # of Executions in Properties
How Many Indexes?HEAP
OR
CLUSTERED INDEX
Update OperationHEAP
OR
CLUSTERED INDEX
Insert or Delete OperationHEAP
OR
CLUSTERED INDEX
Too Many Indexes?
• OLTP Database or OLAP Database?
• How much DML vs SELECT in your workload?
• Periodic ETLs? Disable, batch load, then rebuild
• Index Survey Results by Paul Randalhttp://www.sqlskills.com/blogs/paul/over-and-under-indexing-how-bad-is-it-out-there/
T-SQL Tools&Index Analysis Demo
Parting Thoughts
1. Duplicate & Redundant Indexes are worth cleaning up
2. Missing Index Recommendations can be useful
3. Know your workload to make smart decisions
Thank Youhttps://github.com/SQLBek
Andy Yun@SQLBek
[email protected] / [email protected]://sqlbek.wordpress.com