Kendra Little Explains How to Design Smarter Indexes

Last Updated 7 years ago

How do you know which indexes to create to improve your database performance? Microsoft Certified Master Kendra Little shows you how to find a missing index, decide on the best index definition, and measure the usefulness of your new index. If you know the basics of what a database index does and want to learn the smart way to design indexes, this talk is for you.

Want to play along with the video with the sample scripts? Scripts are below the video.

Here’s what you need to miss (and fix) these indexes at home:

Get the ContosoRetailDW database from Microsoft
Then check out the missing index samples below

Workload Script

This set of queries generates sample missing index information for the Contoso Retail DW database. This uses queries that cause conversion errors on purpose: missing index recommendations will be generated, but this will still run very fast.

This script is designed to be saved as a file and run periodically during the demo.

/**********************
This is a demo script from https://www.brentozar.com
Scripts provided for testing/demo purposes only.
***********************/

--***IMPORTANT****
--Set Query Options for this connection to 'discard results'.


use ContosoRetailDW
GO

--Queries to build stats

select top 50 OnlineSalesKey, DateKey, StoreKey, PromotionKey
from dbo.FactOnlineSales
where DateKey &gt; '2009-11-11'
and ProductKey = 'abc'
go 200

select top 50 OnlineSalesKey, DateKey, StoreKey, PromotionKey
from dbo.FactOnlineSales
where DateKey &gt; '2010-01-10'
and ProductKey = 'abc'
go 201


select top 50 OnlineSalesKey, DateKey, StoreKey
from dbo.FactOnlineSales
where DateKey &gt; '2010-01-10'
and ProductKey = 'abc'
go 202


select OnlineSalesKey, StoreKey
from dbo.FactOnlineSales
where DateKey &gt; 'a'
go 413



--Other indexes (just for color)
select CurrencyKey, OnlineSalesKey, StoreKey, ProductKey
from dbo.FactOnlineSales
where currencyKey ='a'
and storekey='a'
go 2

select ProductKey, StoreKey, PromotionKey
from factsales
where StoreKey='a'
go 4

select *
from FactSalesQuota
where StoreKey = 'a'
GO 2

select DateKey, Amount
from FactStrategyPlan
where Amount &gt; 'a'
GO

/**********************

This is a demo script from https://www.brentozar.com

Scripts provided for testing/demo purposes only.

***********************/

--***IMPORTANT****

--Set Query Options for this connection to 'discard results'.

use ContosoRetailDW

--Queries to build stats

select top 50 OnlineSalesKey, DateKey, StoreKey, PromotionKey

from dbo.FactOnlineSales

where DateKey > '2009-11-11'

and ProductKey = 'abc'

go 200

select top 50 OnlineSalesKey, DateKey, StoreKey, PromotionKey

from dbo.FactOnlineSales

where DateKey > '2010-01-10'

and ProductKey = 'abc'

go 201

select top 50 OnlineSalesKey, DateKey, StoreKey

from dbo.FactOnlineSales

where DateKey > '2010-01-10'

and ProductKey = 'abc'

go 202

select OnlineSalesKey, StoreKey

from dbo.FactOnlineSales

where DateKey > 'a'

go 413

--Other indexes (just for color)

select CurrencyKey, OnlineSalesKey, StoreKey, ProductKey

from dbo.FactOnlineSales

where currencyKey ='a'

and storekey='a'

go 2

select ProductKey, StoreKey, PromotionKey

from factsales

where StoreKey='a'

go 4

select *

from FactSalesQuota

where StoreKey = 'a'

GO 2

select DateKey, Amount

from FactStrategyPlan

where Amount > 'a'

Demo SCRIPT

This script will prompt you several times to run a “fake workload” using the script above.

/**********************
This is a demo script from https://www.brentozar.com
Scripts provided for testing/demo purposes only.
***********************/


/**********************
0. Preliminary Check
How long has our instance been up?
We want to know to contextualize the number of times the index was needed.
***********************/

select cast(
  datediff(hh,sqlserver_start_time,GETDATE())
  /24. 
  as numeric(10,1)) as [Days Uptime] 
from sys.dm_os_sys_info

--What does this mean for missing index data?


/**********************
1. Review missing indexes.
Create a view to look at them.
**************************/    
Use admin;
GO

if object_id('dbo.MissingIndexes') is null
  exec sp_executesql N'create view dbo.MissingIndexes as select ''''as Stub'
GO
ALTER VIEW dbo.MissingIndexes
as
SELECT 
    id.statement,
        cast(gs.avg_total_user_cost * gs.avg_user_impact * ( gs.user_seeks + gs.user_scans )as int) AS Impact,
        cast(gs.avg_total_user_cost as numeric(10,2)) as [Average Total Cost],
        cast(gs.avg_user_impact as int) as [% Reduction of Cost],
        gs.user_seeks + gs.user_scans as [Missed Opportunities],
        id.equality_columns as [Equality Columns],
        id.inequality_columns as [Inequality Columns],
        id.included_columns as [Included Columns]
FROM sys.dm_db_missing_index_group_stats AS gs
JOIN sys.dm_db_missing_index_groups AS ig ON gs.group_handle = ig.index_group_handle
JOIN sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle
go


--Run a fake workload using the "workload script" above in another session.
--Then check out your view

SELECT *
from admin.dbo.MissingIndexes
ORDER BY Impact desc

/**********************
2. Make a list of the columns which look interesting
**************************/ 
--Key:


--Include:



/**********************
3. Review indexes already on the table.
This will give you a sense of the current 'weight' of indexes for the table.
**************************/    
use ContosoRetailDW;
go

SELECT  
        OBJECT_NAME(ps.object_id) AS object_name ,
        ps.index_id ,
        ISNULL(si.name, '(heap)') AS index_name ,
        CAST(ps.reserved_page_count * 8 / 1024. / 1024. AS NUMERIC(10, 2)) AS reserved_GB ,
        ps.row_count ,
        ps.partition_number ,
        ps.in_row_reserved_page_count ,
        ps.lob_reserved_page_count ,
        ps.row_overflow_reserved_page_count
FROM    sys.dm_db_partition_stats ps
        LEFT JOIN sys.indexes AS si
            ON ps.object_id = si.object_id
               AND ps.index_id = si.index_id
WHERE   OBJECT_NAME(ps.object_id) = 'FactOnlineSales' 
 


--Check for indexes already on the table which may combine with your desired index.
--For example, there may be an existing index which you can add key columns or includes to, 
--That's always better than creating a new index!


--What is our table keyed on?
exec sp_helpindex FactOnlineSales


/**********************
4. Create an index
Try to play it safe and create a narrow index
**************************/  
use ContosoRetailDW;
go
   
CREATE NONCLUSTERED INDEX [ixFactOnlineSales_DateKey_Narrow] ON [dbo].[FactOnlineSales]
([DateKey] ASC) WITH (FILLFACTOR = 98) 
GO



--What did this do to our missing index recommendations?
SELECT *
from admin.dbo.MissingIndexes
ORDER BY Impact desc



--Run a fake workload using the script above to simulate more activity



/**********************
5. Review index usage
**************************/  
use ContosoRetailDW;
go

SELECT  o.name as [Object Name],
        s.index_id as [Index ID],
    ps.partition_number as [Partition Num],
        i.name as [Index Name],
        i.type_desc as [Index Type],
        s.user_seeks + s.user_scans + s.user_lookups as [Total Queries Which Read] ,
        s.user_updates [Total Queries Which Wrote] ,
        ps.row_count as [Row Count],	
        CASE WHEN s.user_updates &lt; 1 THEN 100
             ELSE ( s.user_seeks + s.user_scans + s.user_lookups ) / s.user_updates * 1.0
        END AS [Reads Per Write] 
FROM    sys.dm_db_index_usage_stats s
JOIN sys.dm_db_partition_stats ps on s.object_id=ps.object_id and s.index_id=ps.index_id
JOIN sys.indexes i ON i.index_id = s.index_id
  AND s.object_id = i.object_id
JOIN sys.objects o ON s.object_id = o.object_id
JOIN sys.schemas c ON o.schema_id = c.schema_id
WHERE 
    s.database_id=db_id()
    and o.name = 'FactOnlineSales'





/**********************
6. Do we still have missing indexes?
**************************/  
SELECT *
from admin.dbo.MissingIndexes
ORDER BY Impact desc

--Why is that?




/**********************
7. Try creating a covering index
**************************/  
Use ContosoRetailDW
GO

CREATE NONCLUSTERED INDEX [ixFactOnlineSales_DateKey_Covering] ON [dbo].[FactOnlineSales]
([DateKey],[ProductKey]) INCLUDE ([OnlineSalesKey], [StoreKey], [PromotionKey]) WITH (FILLFACTOR = 95) ON [PRIMARY]
GO

--I'm putting DateKey first, even though it was an 'Inequality'.
--This may not *always* be the best idea, but sometimes it works!



/**********************
8. Compare the size of our indexes
**************************/    
use ContosoRetailDW;
go

SELECT  
        OBJECT_NAME(ps.object_id) AS object_name ,
        ps.index_id ,
        ISNULL(si.name, '(heap)') AS index_name ,
        CAST(ps.reserved_page_count * 8 / 1024. / 1024. AS NUMERIC(10, 2)) AS reserved_GB ,
        ps.row_count ,
        ps.partition_number ,
        ps.in_row_reserved_page_count ,
        ps.lob_reserved_page_count ,
        ps.row_overflow_reserved_page_count
FROM    sys.dm_db_partition_stats ps
        LEFT JOIN sys.indexes AS si
            ON ps.object_id = si.object_id
               AND ps.index_id = si.index_id
WHERE   OBJECT_NAME(ps.object_id) = 'FactOnlineSales' 


--Let's drop that first index.
--Our second contains the same definition.
drop index [dbo].[FactOnlineSales].[ixFactOnlineSales_DateKey_Narrow]


--Run a fake workload using the script above


/**********************
9. Review index usage
**************************/  
use ContosoRetailDW;
go

SELECT  o.name as [Object Name],
        s.index_id as [Index ID],
    ps.partition_number as [Partition Num],
        i.name as [Index Name],
        i.type_desc as [Index Type],
        s.user_seeks + s.user_scans + s.user_lookups as [Total Queries Which Read] ,
        s.user_updates [Total Queries Which Wrote] ,
        ps.row_count as [Row Count],	
        CASE WHEN s.user_updates &lt; 1 THEN 100
             ELSE ( s.user_seeks + s.user_scans + s.user_lookups ) / s.user_updates * 1.0
        END AS [Reads Per Write] 
FROM    sys.dm_db_index_usage_stats s
JOIN sys.dm_db_partition_stats ps on s.object_id=ps.object_id and s.index_id=ps.index_id
JOIN sys.indexes i ON i.index_id = s.index_id
  AND s.object_id = i.object_id
JOIN sys.objects o ON s.object_id = o.object_id
JOIN sys.schemas c ON o.schema_id = c.schema_id
WHERE 
    s.database_id=db_id()
    and o.name = 'FactOnlineSales'




/**********************
10. Do we still have missing indexes?
**************************/  
SELECT *
from admin.dbo.MissingIndexes
ORDER BY Impact desc





/**********************
11. ~ PROFIT ~
**************************/  

--We made things better, and we can prove it!




/**********************
THE END. Drop indexes
**************************/ 
use ContosoRetailDW
go

if indexproperty(object_id('FactOnlineSales'),'ixFactOnlineSales_DateKey_Covering','IsClustered') =0
  drop index [dbo].[FactOnlineSales].[ixFactOnlineSales_DateKey_Covering]
go

if indexproperty(object_id('FactOnlineSales'),'ixFactOnlineSales_DateKey_Covering','IsClustered') =0
  drop index [dbo].[FactOnlineSales].[ixFactOnlineSales_DateKey_Covering]
go

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

/**********************

This is a demo script from https://www.brentozar.com

Scripts provided for testing/demo purposes only.

***********************/

/**********************

0. Preliminary Check

How long has our instance been up?

We want to know to contextualize the number of times the index was needed.

***********************/

select cast(

datediff(hh,sqlserver_start_time,GETDATE())

/24.

as numeric(10,1)) as [Days Uptime]

from sys.dm_os_sys_info

--What does this mean for missing index data?

/**********************

1. Review missing indexes.

Create a view to look at them.

**************************/

Use admin;

if object_id('dbo.MissingIndexes') is null

exec sp_executesql N'create view dbo.MissingIndexes as select ''''as Stub'

ALTER VIEW dbo.MissingIndexes

SELECT

id.statement,

cast(gs.avg_total_user_cost * gs.avg_user_impact * ( gs.user_seeks + gs.user_scans )as int) AS Impact,

cast(gs.avg_total_user_cost as numeric(10,2)) as [Average Total Cost],

cast(gs.avg_user_impact as int) as [% Reduction of Cost],

gs.user_seeks + gs.user_scans as [Missed Opportunities],

id.equality_columns as [Equality Columns],

id.inequality_columns as [Inequality Columns],

id.included_columns as [Included Columns]

FROM sys.dm_db_missing_index_group_stats AS gs

JOIN sys.dm_db_missing_index_groups AS ig ON gs.group_handle = ig.index_group_handle

JOIN sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle

--Run a fake workload using the "workload script" above in another session.

--Then check out your view

SELECT *

from admin.dbo.MissingIndexes

ORDER BY Impact desc

/**********************

2. Make a list of the columns which look interesting

**************************/

--Key:

--Include:

/**********************

3. Review indexes already on the table.

This will give you a sense of the current 'weight' of indexes for the table.

**************************/

use ContosoRetailDW;

SELECT

OBJECT_NAME(ps.object_id) AS object_name ,

ps.index_id ,

ISNULL(si.name, '(heap)') AS index_name ,

CAST(ps.reserved_page_count * 8 / 1024. / 1024. AS NUMERIC(10, 2)) AS reserved_GB ,

ps.row_count ,

ps.partition_number ,

ps.in_row_reserved_page_count ,

ps.lob_reserved_page_count ,

ps.row_overflow_reserved_page_count

FROM sys.dm_db_partition_stats ps

LEFT JOIN sys.indexes AS si

ON ps.object_id = si.object_id

AND ps.index_id = si.index_id

WHERE OBJECT_NAME(ps.object_id) = 'FactOnlineSales'

--Check for indexes already on the table which may combine with your desired index.

--For example, there may be an existing index which you can add key columns or includes to,

--That's always better than creating a new index!

--What is our table keyed on?

exec sp_helpindex FactOnlineSales

/**********************

4. Create an index

Try to play it safe and create a narrow index

**************************/

use ContosoRetailDW;

CREATE NONCLUSTERED INDEX [ixFactOnlineSales_DateKey_Narrow] ON [dbo].[FactOnlineSales]

([DateKey] ASC) WITH (FILLFACTOR = 98)

--What did this do to our missing index recommendations?

SELECT *

from admin.dbo.MissingIndexes

ORDER BY Impact desc

--Run a fake workload using the script above to simulate more activity

/**********************

5. Review index usage

**************************/

use ContosoRetailDW;

SELECT o.name as [Object Name],

s.index_id as [Index ID],

ps.partition_number as [Partition Num],

i.name as [Index Name],

i.type_desc as [Index Type],

s.user_seeks + s.user_scans + s.user_lookups as [Total Queries Which Read] ,

s.user_updates [Total Queries Which Wrote] ,

ps.row_count as [Row Count],

CASE WHEN s.user_updates < 1 THEN 100

ELSE ( s.user_seeks + s.user_scans + s.user_lookups ) / s.user_updates * 1.0

END AS [Reads Per Write]

FROM sys.dm_db_index_usage_stats s

JOIN sys.dm_db_partition_stats ps on s.object_id=ps.object_id and s.index_id=ps.index_id

JOIN sys.indexes i ON i.index_id = s.index_id

AND s.object_id = i.object_id

JOIN sys.objects o ON s.object_id = o.object_id

JOIN sys.schemas c ON o.schema_id = c.schema_id

WHERE

s.database_id=db_id()

and o.name = 'FactOnlineSales'

/**********************

6. Do we still have missing indexes?

**************************/

SELECT *

from admin.dbo.MissingIndexes

ORDER BY Impact desc

--Why is that?

/**********************

7. Try creating a covering index

**************************/

Use ContosoRetailDW

CREATE NONCLUSTERED INDEX [ixFactOnlineSales_DateKey_Covering] ON [dbo].[FactOnlineSales]

([DateKey],[ProductKey]) INCLUDE ([OnlineSalesKey], [StoreKey], [PromotionKey]) WITH (FILLFACTOR = 95) ON [PRIMARY]

--I'm putting DateKey first, even though it was an 'Inequality'.

--This may not *always* be the best idea, but sometimes it works!

/**********************

8. Compare the size of our indexes

**************************/

use ContosoRetailDW;

SELECT

OBJECT_NAME(ps.object_id) AS object_name ,

ps.index_id ,

ISNULL(si.name, '(heap)') AS index_name ,

CAST(ps.reserved_page_count * 8 / 1024. / 1024. AS NUMERIC(10, 2)) AS reserved_GB ,

ps.row_count ,

ps.partition_number ,

ps.in_row_reserved_page_count ,

ps.lob_reserved_page_count ,

ps.row_overflow_reserved_page_count

FROM sys.dm_db_partition_stats ps

LEFT JOIN sys.indexes AS si

ON ps.object_id = si.object_id

AND ps.index_id = si.index_id

WHERE OBJECT_NAME(ps.object_id) = 'FactOnlineSales'

--Let's drop that first index.

--Our second contains the same definition.

drop index [dbo].[FactOnlineSales].[ixFactOnlineSales_DateKey_Narrow]

--Run a fake workload using the script above

/**********************

9. Review index usage

**************************/

use ContosoRetailDW;

SELECT o.name as [Object Name],

s.index_id as [Index ID],

ps.partition_number as [Partition Num],

i.name as [Index Name],

i.type_desc as [Index Type],

s.user_seeks + s.user_scans + s.user_lookups as [Total Queries Which Read] ,

s.user_updates [Total Queries Which Wrote] ,

ps.row_count as [Row Count],

CASE WHEN s.user_updates < 1 THEN 100

ELSE ( s.user_seeks + s.user_scans + s.user_lookups ) / s.user_updates * 1.0

END AS [Reads Per Write]

FROM sys.dm_db_index_usage_stats s

JOIN sys.dm_db_partition_stats ps on s.object_id=ps.object_id and s.index_id=ps.index_id

JOIN sys.indexes i ON i.index_id = s.index_id

AND s.object_id = i.object_id

JOIN sys.objects o ON s.object_id = o.object_id

JOIN sys.schemas c ON o.schema_id = c.schema_id

WHERE

s.database_id=db_id()

and o.name = 'FactOnlineSales'

/**********************

10. Do we still have missing indexes?

**************************/

SELECT *

from admin.dbo.MissingIndexes

ORDER BY Impact desc

/**********************

11. ~ PROFIT ~

**************************/

--We made things better, and we can prove it!

/**********************

THE END. Drop indexes

**************************/

use ContosoRetailDW

if indexproperty(object_id('FactOnlineSales'),'ixFactOnlineSales_DateKey_Covering','IsClustered') =0

drop index [dbo].[FactOnlineSales].[ixFactOnlineSales_DateKey_Covering]

if indexproperty(object_id('FactOnlineSales'),'ixFactOnlineSales_DateKey_Covering','IsClustered') =0

drop index [dbo].[FactOnlineSales].[ixFactOnlineSales_DateKey_Covering]

6 Blog Projects to Improve Your Writing

DotNetRocks Podcast

23 Comments. Leave new

Jose
September 23, 2011 11:26 am

My kind of girl, smart and cute :-)…

Jokes apart, thanks! Very nice presentation. If you can post or share the TSQL scripts so we can practice at home too, would be nice.

Awesome job!

Reply
- Kendra Little
  September 23, 2011 12:01 pm
  
  Hi Jose,
  
  A link to the scripts is right below the video. Guessing it wasn’t easy to see, so I updated the post with a note above the video about where the links are hiding. Have fun!
  
  Reply
Alexey
September 27, 2011 8:27 am

Hi Kendra,

What is the range for “Impact” from your view that requires immediate attention?

When the view recommends to build an index on Inequality Columns, how does the approach of creating index differ from Equality Columns?

Thanks

Reply
- Kendra Little
  September 27, 2011 10:15 am
  Hey Alexey,
  
  These are great questions, and might make really nice future webcasts!
  
  My ‘generic’ answer to the impact question is to look at any impacts over 1 million. This is, of course, generic! Some systems have lower thresholds, some have higher. System uptime can really impact this.
  
  For inequality and equality questions, I’d consider a few things:
  - Limitations on the order listed in the missing index feature— what to understand about order (or lack thereof) and how costing is done for inequality only. Read more here.
  - The problem you’re trying to solve. In some cases, you may want to provide a narrow index that can help the most queries. In this case you may experiment and see if inequality columns can serve you just as well as leading columns as equality columns– the results will depend on the selectivity of your workload and the commonality of the queries.
  - The general rules on selectivity and column order. Here’s a good article from SQLServerPedia on the topic. (Not beginner stuff!)
  For people starting out, figuring out when to follow the rules for column order and when to disregard the rules is typically overwhelming. This is why I like to show how even a simple index (which doesn’t lead with the most selective equality column, which would frequently win the “best in show” award) can address a good chunk of a workload in many cases. Although it won’t cover everything– and you need to measure if it covers ‘enough’ for you. (I’m not sure if that’s a pun or not, but there it is.)
  Reply
  - Alexey
    September 27, 2011 12:41 pm
    
    Thank you.
    
    Reply
Vasiliy Goncharenko
October 3, 2011 7:31 am

Hi Kendra,

Thank you for a very interesting and helpful video and scripts.

We are working on a similar task, that we call “Dynamic Indexes” (DI).
The idea is to analyze periodically DMV, identify missing indexes and delete indexes that are not in use anymore.
The DI task should be completely automated.
SQL job supposed to take care of these 2 tasks (create new/delete old), but we are stuck on tweaking – identifying valuable info and thresholds.
Your video gave us fresh ideas. 🙂

Thanks again,
Vasiliy

Reply
- Kendra Little
  October 6, 2011 12:45 pm
  
  Hi Vasiliy,
  
  Automated index tuning is indeed tempting, and I can think of many environments where it would save a lot of time– however, I have several clients who have tried it and have had disappointing results. As I suspect you’ve found, it can be difficult to properly combine indexes without using too much space— and it’s also very difficult to automate determining when a filtered index would work and when it would not.
  
  If you hit road bumps and are blocked from the full project, don’t let it spoil the goal of developing tools to make index tuning either. Even if you end up with a tool that helps you collect indexing information for manual review and decisions (which is my personal preference, I admit), those tools can be really helpful.
  
  kl
  
  Reply
  - Vasiliy Goncharenko
    January 29, 2012 5:54 pm
    
    Hi Kendra,
    
    Take a look at this MS patent: DYNAMIC INTEGRATED DATABASE INDEX MANAGEMENT
    http://www.google.com/patents/US20100250504?dq=database+dynamic+index&ei=zs4iT73FBs-JtwfFt6SjCw
    
    It is very much what I was thinking about.
    Of course, the most difficult part is not in a high-level concept, but in a low-level implementation and algorithms. At the same time ppl started thinking in this direction, and this is good.
    Btw, based on my progress so far, DMV may not be sufficient for this task (to make index management fully automated).
    
    Reply
Vasiliy Goncharenko
October 12, 2011 10:33 am

> … I can think of many environments where it would save a lot of time
> – however, I have several clients who have tried it
> and have had disappointing results.

Yes, I agree, the idea is simple and beneficial, tweaking and tuning is challenging.

While tuning auto-indexing, we end-up with an additional table, kind of DMV nightly snapshot, that allows us to compare efficiency of auto-created views and, more importantly, identify indexes that need to be auto-removed by checking recent activities.
It would be nice to have a team effort.

Reply
JJ
November 15, 2011 5:51 pm

Wow, such a great video! I’ve never seen such helpful information on indexes before. Thanks so much for doing this and taking the time to really explain what is going on and *practical* approach to indexing. So, so helpful!

Reply
Rylie Weissman
January 29, 2012 3:27 pm

Im obliged for the post.Much thanks again. Want more.

Reply
- Kendra Little
  January 29, 2012 3:59 pm
  
  Thanks for the feedbac, Rylie! I was just thinking of putting together a full length index presentation, and perhaps a few more 30 minute webcasts on the topic. Now that you commented, I *know* we need to do it!
  
  Reply
Usman
March 13, 2012 7:34 am

Hi Kendra Little,

Link Broken:

The link “Then download our MissingIndexSamples” is broken.

Reply
- Usman
  March 13, 2012 10:45 am
  
  Hi Kendra Little,
  
  The link to “MissingIndexSamples” is broken
  So,
  
  Can you please email me the “MissingIndexSamples” on my Email ID?
  
  Or
  
  please provide the link to the attachment.?
  
  Many Thanks.
  
  Reply
  - Kendra Little
    March 13, 2012 2:56 pm
    
    Hello!
    
    Sorry about that– not sure how that got broken. I updated the link, tested it, and it works for me now. Please give it another shot.
    
    Thanks much for letting me know.
    Kendra
    
    Reply
Usman
March 14, 2012 7:08 am

Thanks! Kendra Little,

Yes it is Now WORKING 🙂

Cheers…!!!

Reply
Bill
January 24, 2013 1:19 pm

I would like to add one thing. I’ve utilized this script on a server to see exactly where it stands. This is a server that is coming to us shortly. The view would not run. The resolution is rather depressing…. We had to convert impact to BIGINT to get it to work. /sigh.

Thank you again for your wonderful work!!! And of course saving our little DB’s.

Reply
- Kendra Little
  January 24, 2013 1:26 pm
  
  Hi Bill!
  
  I’m sorry your missing index recommendation was so large 🙂
  
  Thanks for letting me know this is still the case in the sample script here– adding a to-do item to update the data type in the scripts. (probably be a few days before I can get to it, but it’s on the list.)
  
  Reply
  - Bill
    January 24, 2013 1:55 pm
    
    No worries at all. I’m sad it was over 2.2B. ^.^;
    
    Reply
Patrick Wren
March 29, 2013 11:10 am

Thank you so much for this video. It really helped me to understand indexes better than I ever have. The scripts have been a very helpful too for me as well.

SQL keeps recommending that I create an Index and the Index already exist and is being used The recommendation only has Equality and Inequality columns. What would cause it to tell me to create the same index again?

Reply
- Kendra Little
  March 29, 2013 12:05 pm
  
  Unfortunately this is a known bug in some versions of SQL Server. http://connect.microsoft.com/SQLServer/feedback/details/400578/query-plan-missing-index-recommendation-doesnt-check-if-an-index-actually-exists
  
  Reply
  - Patrick Wren
    March 29, 2013 12:07 pm
    
    Thank you for the quick reply and again Thank You for the video.
    
    Reply
George Copeland
August 23, 2018 1:55 pm

Holy smoke is this a perfect post. Thank you very much Kendra. I like your squid. 🙂

Reply