Я влюбился в в концепцию DBaaS с тех пор, как я впервые увидел базу данных Azure SQL. Это имеет смысл для меня. Заберите механизмы управления сервером и управления базами данных. Сосредоточьтесь на содержимом вашей базы данных. Резервные копии, проверки согласованности - об этих легко автоматизированных аспектах можно легко позаботиться. То же самое происходит с некоторым, но не всем, управлением индексами. База данных Azure SQL может управлять вашими индексами для вас. Я называю это вооруженным магазином запросов.

Во всяком случае, мне нужен был способ автоматизировать это для книги, которую я пишу. Я не мог найти хороших примеров в Интернете, поэтому я создал свой собственный.

Запросы, нуждающиеся в автоматической индексации

Поскольку я хочу, чтобы это было максимально простым и повторяемым, я использую образец базы данных, которую вы можете создать при создании базы данных Azure SQL. Это, по сути, очень крошечная подсистема AdventureWorks, упрощенная и уменьшенная по размеру. При этом в качестве отправной точки вам понадобятся некоторые запросы:

CREATE OR ALTER PROCEDURE dbo.CustomerInfo
(@Firstname NVARCHAR(50))
AS
SELECT c.FirstName,
       c.LastName,
       c.Title,
       a.City
FROM SalesLT.Customer AS c
    JOIN SalesLT.CustomerAddress AS ca
        ON ca.CustomerID = c.CustomerID
    JOIN SalesLT.Address AS a
        ON a.AddressID = ca.AddressID
WHERE c.FirstName = @Firstname;
GO


CREATE INDEX TestDuplicate ON SalesLT.Customer (EmailAddress)
GO

CREATE OR ALTER PROCEDURE dbo.EmailInfo (@EmailAddress nvarchar(50))
AS
SELECT c.EmailAddress,
       c.Title,
       soh.OrderDate
FROM SalesLT.Customer AS c
    JOIN SalesLT.SalesOrderHeader AS soh
        ON soh.CustomerID = c.CustomerID
WHERE c.EmailAddress = @EmailAddress;
GO

CREATE OR ALTER PROCEDURE dbo.SalesInfo (@firstName NVARCHAR(50))
AS
SELECT c.FirstName,
       c.LastName,
       c.Title,
       soh.OrderDate
FROM SalesLT.Customer AS c
    JOIN SalesLT.SalesOrderHeader AS soh
        ON soh.CustomerID = c.CustomerID
WHERE c.FirstName = @firstName
GO


CREATE OR ALTER PROCEDURE dbo.OddName (@FirstName NVARCHAR(50))
AS
SELECT c.FirstName
FROM SalesLT.Customer AS c
WHERE c.FirstName BETWEEN 'Brian'
                  AND     @FirstName
GO

Вы заметите, что несколько запросов фильтруются в столбце FirstName. Там нет хорошего индекса. Если вы посмотрите планы выполнения этих запросов, вы также обратите внимание на предложение Missing Index. Это предложение является необходимой частью автоматической индексации. Да, отсутствуют индексы. Да, они не всегда точны. Это всего лишь предложение.

Суть не в отсутствующих индексах, а во множестве данных. Microsoft может воспользоваться тремя преимуществами. Да, недостающие предложения указателей в первую очередь. Затем они могут использовать показатели запросов, собранные в Query Store, чтобы просмотреть поведение ваших запросов с течением времени. Наконец, они могут использовать алгоритмы машинного обучения, чтобы определить, будут ли индексы полезными и определить, насколько они полезны, если их добавить. Это здорово.

Автоматизация запросов для автоматической индексации

Ключ к добавлению ваших индексов автоматически добавляется, хотя это займет какое-то время. Вы не можете запускать один запрос один раз, получить отсутствующий индекс, и Microsoft добавит его для вас. Они не сумасшедшие или глупые. Вам нужно много выполнений (точное число, мне неизвестное). С течением времени должно быть много выполнений (приблизительное время, основанное на тестировании, 12-18 часов). Наконец, вам также нужно больше одного запроса (опять же, сколько, я не знаю, но только один или два не пересекали порог, поэтому я пошел на четвертый, который сработал).

Итак, как я проработал эти процессы в течение нескольких часов? Легко. Powershell:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = 'Server=qpf.database.windows.net;Database=QueryPerformanceTuning;trusted_connection=false;user=Grant;password=icanttellyou'

## load customer names
$DatCmd = New-Object System.Data.SqlClient.SqlCommand
$DatCmd.CommandText = "SELECT c.FirstName, c.EmailAddress
FROM SalesLT.Customer AS c;"
$DatCmd.Connection = $SqlConnection
$DatDataSet = New-Object System.Data.DataSet
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $DatCmd
$SqlAdapter.Fill($DatDataSet)


$Proccmd = New-Object System.Data.SqlClient.SqlCommand
$Proccmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$Proccmd.CommandText = "dbo.CustomerInfo"
$Proccmd.Parameters.Add("@FirstName",[System.Data.SqlDbType]"varchar")
$Proccmd.Connection = $SqlConnection

$EmailCmd = New-Object System.Data.SqlClient.SqlCommand
$EmailCmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$EmailCmd.CommandText = "dbo.EmailInfo"
$EmailCmd.Parameters.Add("@EmailAddress",[System.Data.SqlDbType]"varchar")
$EmailCmd.Connection = $SqlConnection

$SalesCmd = New-Object System.Data.SqlClient.SqlCommand
$SalesCmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$SalesCmd.CommandText = "dbo.SalesInfo"
$SalesCmd.Parameters.Add("@FirstName",[System.Data.SqlDbType]"varchar")
$SalesCmd.Connection = $SqlConnection

$OddCmd = New-Object System.Data.SqlClient.SqlCommand
$OddCmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$OddCmd.CommandText = "dbo.OddName"
$OddCmd.Parameters.Add("@FirstName",[System.Data.SqlDbType]"varchar")
$OddCmd.Connection = $SqlConnection



while(1 -ne 0)
{
    foreach($row in $DatDataSet.Tables[0])
        {
        
        $name = $row[0]
        $email = $row[1]
        $SqlConnection.Open()
        $Proccmd.Parameters["@FirstName"].Value = $name
        $Proccmd.ExecuteNonQuery() | Out-Null
        $EmailCmd.Parameters["@EmailAddress"].Value = $email
        $EmailCmd.ExecuteNonQuery() | Out-Null
        $SalesCmd.Parameters["@FirstName"].Value = $name
        $SalesCmd.ExecuteNonQuery() | Out-Null
        $OddCmd.Parameters["@FirstName"].Value = $name
        $OddCmd.ExecuteNonQuery() | Out-Null
        $SqlConnection.Close()

 }
 }

Создайте процедуры. Запустите этот скрипт в течение 12-18 часов, и вы должны получить что-то похожее на это в своем блэйде рекомендаций:

Заключение

Цель здесь очень проста. Я хочу, чтобы автоматическая индексация срабатывала. Я сделал достаточно, чтобы это произошло. Автоматическая индексация также имеет опцию drop для дубликатов. Я еще не понял, как это сделать, но вы тоже сможете это выяснить в этих сценариях.

 Грант Фритчей, Microsoft SQL Server MVP

No Comments