Notes On Automating Automatic Indexing

Grant Fritchey shares with us some of his findings with automatic indexing on Azure SQL Database:

What you’ll notice is that several of the queries are filtering on the FirstName column. There’s no good index there. If you look at the execution plans for those queries you’ll also note the Missing Index suggestion. That suggestion is a necessary part of the automatic indexing. Yeah, missing indexes. I know. They’re not always accurate. It’s just a suggestion. Blah, blah, blah. I hear you.

The magic is not supplied by missing indexes. The magic is supplied by lots of data. Microsoft can take advantage of three things. Yes, missing index suggestions is first. Then, they can use the query metrics gathered in Query Store to see the behavior of your queries over time. Finally, they can use machine learning algorithms to determine if indexes will be helpful and measure how helpful they’ve been if one gets added. It’s great stuff. Go and read on it.

Click through for more notes, as well as a Powershell script you can use to replicate his findings.

Related Posts

Checking If Ports Are Open Using Powershell

Anthony Nocentino has a quick Powershell script to see if ports are open on a machine: Ever want to confirm that a port is accessible from one computer to another? There’s a PowerShell cmdlet for that, Test-NetConnection. With the -Port option, this cmdlet will do a quick TCP three-way handshake on the remote system to confirm that the service is […]

Read More

More dbatools Changes

Chrissy LeMaire has a few more breaking changes for us: It’s been a busy couple days! Here’s a list of our breaking changes Invoke-Sqlcmd2 has been removed and replaced with a warning to use Invoke-DbaQuery The NetworkShare parameter has been renamed to SharedPath UseLastBackups has been renamed to UseLastBackup There are also several new commands […]

Read More

Categories

May 2018
MTWTFSS
« Apr Jun »
 123456
78910111213
14151617181920
21222324252627
28293031