[Video] How to Think Like Clippy (Subtitle: Watch Brent Wear Another Costume)

Last Updated 4 years ago

Remember Clippy, the Microsoft Office assistant from the late 1990s? He would pop up at the slightest provocation and offer to help you do something – usually completely unrelated to the task you were trying to accomplish.

Sadly, the Office team told Clippy that he didn’t make the stack rankings cut, so he relocated over to the SQL Server team. He knows better than to share his face in SSMS, but he toils away tirelessly suggesting indexes you should add. “Hey buddy! It looks like you’re querying the Users table by Reputation. Would you like help?”

In my Fundamentals of Index Tuning class, I explain how humans think about designing nonclustered indexes. After a few hours of the fundamentals, we switch gears and show how Clippy comes up with his column orders. Students are pretty disappointed at Clippy’s simplicity, but on the flip side, he gets these recommendations done in a millisecond – something you and I would be hard-pressed to pull off.

I decided Clippy deserved his own webcast, so this weekend, I let him take over my stream and defend himself. Enjoy!

Wanna see more of this? Every Saturday & Sunday morning from 9AM-11AM Eastern (iCal), you can catch me on my Twitch, YouTube, or Facebook channels.

Here’s the demo script Clippy used in the session:

/*
How Are Missing Index Hints Built?

Here's what you're going to learn in this demo:

* How SQL Server picks missing index column order
* What SQL Server doesn't consider: selectivity or statistics

This demo requires:

* Any supported version of SQL Server or Azure SQL DB
  (although the 10M row table creation can be pretty slow in Azure)
*/


/* Let's create a table with a few columns, and insert 100,000 identical rows: */
--DROP TABLE dbo.DiningRoom;

CREATE TABLE dbo.DiningRoom
  (FirstColumn INT,
   SecondColumn INT,
   ThirdColumn INT,
   FourthColumn INT,
   FifthColumn INT,
   SixthColumn INT
   );
INSERT INTO dbo.DiningRoom (FirstColumn, SecondColumn, ThirdColumn, FourthColumn, FifthColumn, SixthColumn)
  SELECT TOP 10000000 1, 1, 1, 1, 1, 1
  FROM sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
  CROSS JOIN sys.all_columns ac3;
GO
SELECT TOP 100 * FROM dbo.DiningRoom;
GO

/* Turn on actual execution plans, and check the missing index requests: */
SET STATISTICS TIME, IO ON;
GO
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn = 0;

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE SecondColumn = 0;
GO



/* Simple so far. Now let's try two columns: */
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn = 0
    AND SecondColumn = 0;

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE SecondColumn = 0
    AND FirstColumn = 0;
GO

/* 
/poll "What missing index recommendations will we get?" "SQL will ask for two different indexes" "Both will ask for FirstColumn, SecondColumn" "Both will ask for SecondColumn, FirstColumn"  anonymous

*/


/* 
What about selectivity?
If our where clause looks for one thing that doesn't exist, and one thing that
does, will SQL Server put the thing that doesn't exist first so it's faster?
*/
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn = 1
    AND SecondColumn = 0;

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn = 0
    AND SecondColumn = 1;
GO
/* 
/poll "Now what missing index recommendations will we get?" "SQL will ask for two different indexes" "Both will ask for FirstColumn, SecondColumn" "Both will ask for SecondColumn, FirstColumn"  anonymous

*/




/* 
TAKEAWAY #1:
missing index column order is determined by column order in the table.

It's kinda disappointing, but at least there's a little more logic than that.
And I know what you're thinking: selectivity matters, right? Wrong.
*/



/* Next, instead of equality searches ( = ), try inequality ( <> ).*/
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn <> 1
    AND SecondColumn <> 1;

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE SecondColumn <> 1
    AND FirstColumn <> 1;
GO


/* Still the same: FirstColumn, SecondColumn.

But now try equality on one field, inequality on the other:
*/
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn = 1    /* EQUALITY */
    AND SecondColumn <> 1; /* INEQUALITY */ 

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn <> 1   /* INEQUALITY */
    AND SecondColumn = 1;  /* EQUALITY */
GO


/* 
TAKEAWAY #2:
Equality searches go first
Inequality searches go second.

To see the difference, look in the execution plan XML, and you'll see the
equality searches are listed first, then the inequality searches.
*/



/* Another example of inequality vs equality: */
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn IS NULL       /* EQUALITY */
    AND SecondColumn IS NOT NULL; /* INEQUALITY */ 

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn IS NOT NULL   /* INEQUALITY */
    AND SecondColumn IS NULL;     /* EQUALITY */
GO



/* 
So it works like this:
Equality searched fields, in the order that the columns appear in the table
Inequality searched fields, ditto



So now it's your turn.
Without running the query, GUESS the index recommendation orders:
*/
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE ThirdColumn = 0
    AND FourthColumn <> 1;
GO
/* 
/poll "What will be the missing index recommendation?" "ThirdColumn, FourthColumn" "FourthColumn, ThirdColumn" "Something else"  anonymous

*/

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FourthColumn <> 1
    AND SecondColumn = 0;
GO
/* 
/poll "What will be the missing index recommendation?" "SecondColumn, FourthColumn" "FourthColumn, SecondColumn" "Something else"  anonymous

*/


/* Advanced test: guess the order of these: */
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn <> SecondColumn;
GO
/* 
/poll "What will be the missing index recommendation?" "FirstColumn, SecondColumn" "SecondColumn, FirstColumn" "Something else"  anonymous

*/
  
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FourthColumn = 0
    AND FirstColumn <> 0
    AND ThirdColumn = 0;
GO
/* 
/poll "What will be the missing index recommendation?" "FirstColumn, ThirdColumn, FourthColumn" "ThirdColumn, FourthColumn, FirstColumn" "FourthColumn, FirstColumn, ThirdColumn" "Something else"  anonymous

*/
	
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn <> 0
    AND SecondColumn IS NULL
    AND FourthColumn IS NOT NULL
    AND ThirdColumn = 1;
GO





/* 
This isn't just about execution plan hints, either: the same logic drives
the missing index recommendations in the DMVs. Run a few queries to generate
missing indexes:

Turn off execution plans first, then:
*/
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn = 0
    AND SecondColumn = 0; /* BOTH EQUALITY */

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn <> 1
    AND SecondColumn <> 1; /* BOTH INEQUALITY */

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn = 0
    AND SecondColumn <> 0; /* MIXED */

GO 10


/* And check the missing index DMVs: */
sp_BlitzIndex @TableName = 'DiningRoom'


/* 
SQL Server will recommend 3 similar indexes on FirstColumn, SecondColumn.
We're not doing any kind of advanced de-duping in sp_BlitzIndex: index tuning
still involves manual intervention to figure out which field should really
go first.

But now that you know the trick, you can do a better job of interpreting
real-life recommendations.

To learn more about how this works:

How does SQL Server determine key column order in missing index requests?
https://dba.stackexchange.com/questions/208947/
Shout out to Bryan Rebok for figuring this out!
*/
DROP TABLE dbo.DiningRoom;









/*
License: Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
More info: https://creativecommons.org/licenses/by-sa/3.0/

You are free to:
* Share - copy and redistribute the material in any medium or format
* Adapt - remix, transform, and build upon the material for any purpose, even 
  commercially

Under the following terms:
* Attribution - You must give appropriate credit, provide a link to the license,
  and indicate if changes were made.
* ShareAlike - If you remix, transform, or build upon the material, you must
  distribute your contributions under the same license as the original.
*/

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

276

277

278

279

280

281

282

283

284

285

286

287

How Are Missing Index Hints Built?

Here's what you're going to learn in this demo:

* How SQL Server picks missing index column order

* What SQL Server doesn't consider: selectivity or statistics

This demo requires:

* Any supported version of SQL Server or Azure SQL DB

(although the 10M row table creation can be pretty slow in Azure)

/* Let's create a table with a few columns, and insert 100,000 identical rows: */

--DROP TABLE dbo.DiningRoom;

CREATE TABLE dbo.DiningRoom

(FirstColumn INT,

SecondColumn INT,

ThirdColumn INT,

FourthColumn INT,

FifthColumn INT,

SixthColumn INT

);

INSERT INTO dbo.DiningRoom (FirstColumn, SecondColumn, ThirdColumn, FourthColumn, FifthColumn, SixthColumn)

SELECT TOP 10000000 1, 1, 1, 1, 1, 1

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

CROSS JOIN sys.all_columns ac3;

SELECT TOP 100 * FROM dbo.DiningRoom;

/* Turn on actual execution plans, and check the missing index requests: */

SET STATISTICS TIME, IO ON;

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn = 0;

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE SecondColumn = 0;

/* Simple so far. Now let's try two columns: */

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn = 0

AND SecondColumn = 0;

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE SecondColumn = 0

AND FirstColumn = 0;

/poll "What missing index recommendations will we get?" "SQL will ask for two different indexes" "Both will ask for FirstColumn, SecondColumn" "Both will ask for SecondColumn, FirstColumn" anonymous

What about selectivity?

If our where clause looks for one thing that doesn't exist, and one thing that

does, will SQL Server put the thing that doesn't exist first so it's faster?

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn = 1

AND SecondColumn = 0;

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn = 0

AND SecondColumn = 1;

/poll "Now what missing index recommendations will we get?" "SQL will ask for two different indexes" "Both will ask for FirstColumn, SecondColumn" "Both will ask for SecondColumn, FirstColumn" anonymous

TAKEAWAY #1:

missing index column order is determined by column order in the table.

It's kinda disappointing, but at least there's a little more logic than that.

And I know what you're thinking: selectivity matters, right? Wrong.

/* Next, instead of equality searches ( = ), try inequality ( <> ).*/

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn <> 1

AND SecondColumn <> 1;

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE SecondColumn <> 1

AND FirstColumn <> 1;

/* Still the same: FirstColumn, SecondColumn.

But now try equality on one field, inequality on the other:

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn = 1 /* EQUALITY */

AND SecondColumn <> 1; /* INEQUALITY */

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn <> 1 /* INEQUALITY */

AND SecondColumn = 1; /* EQUALITY */

TAKEAWAY #2:

Equality searches go first

Inequality searches go second.

To see the difference, look in the execution plan XML, and you'll see the

equality searches are listed first, then the inequality searches.

/* Another example of inequality vs equality: */

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn IS NULL /* EQUALITY */

AND SecondColumn IS NOT NULL; /* INEQUALITY */

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn IS NOT NULL /* INEQUALITY */

AND SecondColumn IS NULL; /* EQUALITY */

So it works like this:

Equality searched fields, in the order that the columns appear in the table

Inequality searched fields, ditto

So now it's your turn.

Without running the query, GUESS the index recommendation orders:

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE ThirdColumn = 0

AND FourthColumn <> 1;

/poll "What will be the missing index recommendation?" "ThirdColumn, FourthColumn" "FourthColumn, ThirdColumn" "Something else" anonymous

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FourthColumn <> 1

AND SecondColumn = 0;

/poll "What will be the missing index recommendation?" "SecondColumn, FourthColumn" "FourthColumn, SecondColumn" "Something else" anonymous

/* Advanced test: guess the order of these: */

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn <> SecondColumn;

/poll "What will be the missing index recommendation?" "FirstColumn, SecondColumn" "SecondColumn, FirstColumn" "Something else" anonymous

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FourthColumn = 0

AND FirstColumn <> 0

AND ThirdColumn = 0;

/poll "What will be the missing index recommendation?" "FirstColumn, ThirdColumn, FourthColumn" "ThirdColumn, FourthColumn, FirstColumn" "FourthColumn, FirstColumn, ThirdColumn" "Something else" anonymous

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn <> 0

AND SecondColumn IS NULL

AND FourthColumn IS NOT NULL

AND ThirdColumn = 1;

This isn't just about execution plan hints, either: the same logic drives

the missing index recommendations in the DMVs. Run a few queries to generate

missing indexes:

Turn off execution plans first, then:

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn = 0

AND SecondColumn = 0; /* BOTH EQUALITY */

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn <> 1

AND SecondColumn <> 1; /* BOTH INEQUALITY */

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn = 0

AND SecondColumn <> 0; /* MIXED */

GO 10

/* And check the missing index DMVs: */

sp_BlitzIndex @TableName = 'DiningRoom'

SQL Server will recommend 3 similar indexes on FirstColumn, SecondColumn.

We're not doing any kind of advanced de-duping in sp_BlitzIndex: index tuning

still involves manual intervention to figure out which field should really

go first.

But now that you know the trick, you can do a better job of interpreting

real-life recommendations.

To learn more about how this works:

How does SQL Server determine key column order in missing index requests?

https://dba.stackexchange.com/questions/208947/

Shout out to Bryan Rebok for figuring this out!

DROP TABLE dbo.DiningRoom;

License: Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)

More info: https://creativecommons.org/licenses/by-sa/3.0/

You are free to:

* Share - copy and redistribute the material in any medium or format

* Adapt - remix, transform, and build upon the material for any purpose, even

commercially

Under the following terms:

* Attribution - You must give appropriate credit, provide a link to the license,

and indicate if changes were made.

* ShareAlike - If you remix, transform, or build upon the material, you must

distribute your contributions under the same license as the original.

How to Install Azure Data Studio to Work with SQL Server

What SQL ConstantCare® Has Been Finding In Your Servers Lately

3 Comments. Leave new

Jonathan Pufal
July 27, 2020 10:08 am

I got my phone on Saturday and the first thing that popped up was the live stream. I knew it had to be good when I saw Clippy there. 🙂

Reply
Simon Richardson (@tsql_simon)
July 28, 2020 12:27 am

Oh My! It’s like the giant floating head from the Wizard of Oz

Reply
Roberto
July 15, 2021 9:41 am

Microsoft threatens to resurrect Clippy as an Office emoji

https://www.theverge.com/2021/7/14/22577468/microsoft-office-clippy-emoji-tweet-likes?fbclid=IwAR3JC7EeAPKu6pqwt-f8iY9t8YEpG156xeOFAQfdRuYlL4-swdreckpw9I8

Reply

[Video] How to Think Like Clippy (Subtitle: Watch Brent Wear Another Costume)

3 Comments. Leave new

Leave a Reply Cancel reply

Hi! I’m Brent Ozar.