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

Default Parameter Values In Powershell

Chrissy LeMaire explains what $PSDefaultParameterValues is and places where she finds it useful: $PSDefaultParameterValues is a hashtable available in PowerShell that can set defaults for any command that you run. In it’s simplest form, setting a default parameter value can look like this: 1 $PSDefaultParameterValues = @{ ‘Get-DbaDatabase:Verbose’ = $true } After running the above code, Get-DbaDatabase will show […]

Read More

Getting Started With Azure Databricks

David Peter Hansen has a quick walkthrough of Azure Databricks: RUN MACHINE LEARNING JOBS ON A SINGLE NODE A Databricks cluster has one driver node and one or more worker nodes. The Databricks runtime includes common used Python libraries, such as scikit-learn. However, they do not distribute their algorithms. Running a ML job only on the driver might not […]

Read More

Categories

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