Created
March 6, 2017 16:23
-
-
Save LitKnd/57c3755d4535ddf069cbe896ad91769b to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* Sample code that explores index recommendations in BabbyNames */ | |
USE BabbyNames; | |
GO | |
SET NOCOUNT ON; | |
GO | |
SELECT | |
ref.FirstName, | |
agg.NameCount | |
FROM agg.FirstNameByYear as agg | |
JOIN ref.FirstName as ref on | |
agg.FirstNameId=ref.FirstNameId | |
WHERE | |
Gender='F' | |
and ref.FirstName = 'Calliope'; | |
GO 10 | |
SELECT TOP 100 | |
ref.FirstName, | |
agg.NameCount | |
FROM agg.FirstNameByYear as agg | |
JOIN ref.FirstName as ref on | |
agg.FirstNameId=ref.FirstNameId | |
WHERE | |
Gender = 'M' | |
ORDER BY NameCount DESC; | |
GO 20 | |
/* Missing index requests for this database */ | |
SELECT | |
d.statement as table_name, | |
d.equality_columns, | |
d.inequality_columns, | |
d.included_columns, | |
s.avg_total_user_cost as avg_est_plan_cost, | |
s.avg_user_impact as avg_est_cost_reduction, | |
s.user_scans + s.user_seeks as times_requested | |
FROM sys.dm_db_missing_index_groups AS g | |
JOIN sys.dm_db_missing_index_group_stats as s on | |
g.index_group_handle=s.group_handle | |
JOIN sys.dm_db_missing_index_details as d on | |
g.index_handle=d.index_handle | |
JOIN sys.databases as db on | |
d.database_id=db.database_id | |
WHERE db.database_id=DB_ID(); | |
GO | |
/* Which indexes would be great for each query? */ | |
--KEY (Gender, FirstNameId) INCLUDE NameCount | |
--KEY (Gender, NameCount) INCLUDE FirstNameId | |
/* Compromise index if the TOP query is slightly more important. | |
Great for the TOP query that runs 20 times, good enough for the other query*/ | |
CREATE INDEX ix_iamapersonnotamachine on agg.FirstNameByYear | |
(Gender, NameCount) INCLUDE (FirstNameId); | |
GO | |
CREATE INDEX ix_FirstName_FirstName_INCLUDES on ref.FirstName | |
(FirstName) INCLUDE (FirstNameId); | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment