Большое количество планов выполнения для одного запроса

Автор: Jose_Manuel_Jurado, Lesson Learned #494: High number of Executions Plans for a Single Query

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

У нас есть следующее определение таблицы:

CREATE Table TestTable(ID INT IDENTITY(1,1), string_column NVARCHAR(500))

-- Мы добавили тестовые данные в таблицу, запустив следующий скрипт.
DECLARE @Total INT = 40000;
DECLARE @I int =0
DECLARE @Fill INT;
DECLARE @Letter INT;
WHILE @i <= @Total
BEGIN
    SET @I=@I+1
	SET @Letter = CAST((RAND(CHECKSUM(NEWID())) * (90 - 65 + 1)) + 65 AS INT)
    set @Fill = CAST((RAND(CHECKSUM(NEWID())) * 500) + 1 AS INT)
	INSERT INTO TestTable (string_column) values(REPLICATE(CHAR(@Letter),@Fill))
end

-- Наконец, мы создали новый индекс для этого столбца.
create index TestTable_Ix1 on TestTable (String_column)

Наш клиент обнаружил, что приложение генерирует такой запрос:

SELECT TOP 1 * FROM TestTable WHERE string_column = N'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'

Чтобы воспроизвести проблему и понять влияние, о котором сообщил клиент (количество планов выполнения), мы начали запускать демонстрационную функцию StartAdhocNoParam: Эта функция выполняет непараметризованный запрос. Запуск следующего динамического административного представления (DMV) для определения количества планов показал около 13 тыс. кэшированных планов.

-- dbcc freeproccache -- Только для очистки кэша.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    qs.sql_handle,
    qs.execution_count,
    qs.total_elapsed_time,
    qs.total_logical_reads,
    qs.total_logical_writes,
    qs.total_worker_time,
    qs.creation_time,
    qs.last_execution_time,
    st.text AS sql_text,
    qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY
    sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE
    st.text LIKE '%SELECT TOP 1 * FROM TestTable%'

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

Дополнительно:

OPTIMIZE_FOR_AD_HOC_WORKLOADS может снизить использование памяти, хотя это может и не способствовать повторному использованию планов — Database scoped optimizing for ad hoc workloads.

Также рассмотрите возможность использования руководств для планов, которые могут помочь в этом — Создание нового руководства по плану.

Когда наш клиент завершил модификацию своего кода, мы заметили, что их приложение не указывает размер параметра или длину текста, который ищет приложение, как мы могли видеть в демонстрационной функции StartAdhocWithParam.

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

В этой ситуации мы предложили использовать функцию StartParametrize, указывая максимальную длину столбца (500), что позволило бы иметь только один план выполнения. Это сократило использование кэшированных планов.

Это упражнение подчёркивает важность указания длины параметра.

Наконец, я хотел бы поделиться двумя новыми функциями:

• ImprovedVersionStartParametrize, которая помогает нам сократить количество передаваемых в базу данных текстовых данных, отправляя только значения.
• GetColumnLength, которая подключается к базе данных, чтобы определить общий размер столбца на основе внутренней таблицы INFORMATION_SCHEMA.columns, чтобы выполнять это более динамично.

using System;
using System.Data;
using Microsoft.Data.SqlClient;

class Program
{
    static void Main()
    {
        // Параметры подключения
        string connectionString = "Server=tcp:servername.database.windows.net,1433;User Id=username;Password=pwd!;Initial Catalog=dbname;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Pooling=true;Max Pool size=100;Min Pool Size=1;ConnectRetryCount=3;ConnectRetryInterval=10;Application Name=ConnTest";

        //ImprovedVersionStartParametrize(connectionString);
        for (int j = 65; j <= 90; j = j + 1)
        {
            Console.WriteLine("Letter:" + (char)j);
            for (int i = 1; i <= 500; i = i + 1)
            {
                if (i % 10 == 0)
                {
                    Console.Write(" {0} ,", i);
                }
                //StartAdhocWithParam(connectionString, (char)j, i);
                //StartAdhocWithGuide(connectionString, (char)j, i);
                StartAdhocNoParam(connectionString, (char)j,i);
                //StartParametrize(connectionString, (char)j, i);
            }
        }
    }

    static void StartAdhocWithParam(string connectionString, char Letter, int Length)
    {
        string query = "SELECT TOP 1 * FROM TestTable WHERE string_column = @stringParam --Adhoc with Param";
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                string stringParam = new string(Letter, Length);
                cmd.Parameters.Add(new SqlParameter("@stringParam", SqlDbType.NVarChar, Length) { Value = stringParam });
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
            }
        }
    }

    static void StartAdhocNoParam(string connectionString, char Letter, int Length)
    {
        string stringParam = new string(Letter, Length);
        string query = "SELECT TOP 1 * FROM TestTable WHERE string_column = N'" + stringParam + "' --Adhoc without Param";
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
            }
        }
    }

    static void StartParametrize(string connectionString, char Letter, int Length)
    {
        string query = "SELECT TOP 1 * FROM TestTable WHERE string_column = @stringParam --Adhoc with Max Length";
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                string stringParam = new string(Letter, Length);
                cmd.Parameters.Add(new SqlParameter("@stringParam", SqlDbType.NVarChar, 500) { Value = stringParam });
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
            }
        }
    }

    static void ImprovedVersionStartParametrize(string connectionString)
    {
        string query = "SELECT TOP 1 * FROM TestTable WHERE string_column = @stringParam --Adhoc with Max Length";
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                cmd.Parameters.Add(new SqlParameter("@stringParam", SqlDbType.NVarChar, GetColumnLength(connectionString, "dbo", "TestTable", "string_column")));
                conn.Open();
                cmd.Prepare();
                for (int j = 65; j <= 90; j = j + 1)
                {
                    Console.WriteLine("Letter:" + (char)j);
                    for (int i = 1; i <= 500; i = i + 1)
                    {
                        if (i % 10 == 0)
                        {
                            Console.Write(" {0} ,", i);
                        }
                        cmd.Parameters[0].Value = new string((char)j, i);
                        SqlDataReader reader = cmd.ExecuteReader();
                        reader.Close();
                    }
                }
            }
        }
    }

    static void StartAdhocWithGuide(string connectionString, char Letter, int Length)
    {
        string query = @"
                DECLARE @sqlQuery NVARCHAR(MAX) = N'SELECT TOP 1 * FROM TestTable WHERE string_column = @stringColumn';
                EXEC sp_executesql @sqlQuery, N'@stringColumn NVARCHAR(500)', @stringColumn = @stringParam";

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                string stringParam = new string(Letter, Length);
                cmd.Parameters.Add(new SqlParameter("@stringParam", SqlDbType.NVarChar, Length) { Value = stringParam });
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
            }
        }
    }
    static int GetColumnLength(string connectionString, string schemaName, string tableName, string columnName)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {

            using (SqlCommand cmd = new SqlCommand(@"
                SELECT CHARACTER_MAXIMUM_LENGTH
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @NameT AND COLUMN_NAME = @ColumnName", connection))
            {
                cmd.Parameters.Add("@SchemaName", SqlDbType.NVarChar, 128);
                cmd.Parameters.Add("@NameT", SqlDbType.NVarChar, 128);
                cmd.Parameters.Add("@ColumnName", SqlDbType.NVarChar, 128);

                cmd.Parameters["@SchemaName"].Value=schemaName;
                cmd.Parameters["@NameT"].Value = tableName;
                cmd.Parameters["@ColumnName"].Value = columnName;

                connection.Open();
                var result = cmd.ExecuteScalar();
                if (result != null)
                {
                    return Convert.ToInt32(result);
                }
                else
                {
                    return 0;
                }
            }
        }
    }
}

Отказ от ответственности

Использование этого приложения и предоставленных скриптов предназначено только для образовательных и информационных целей. Скрипты и методы, продемонстрированные в этом руководстве, предоставляются «как есть» без каких-либо гарантий. Пользователь несёт ответственность за обеспечение точности, надёжности и пригодности этих инструментов для своих конкретных нужд.