Today, I worked on a service request where our customer detected a high number of execution plans consuming resources in the plan cache for a single query. I would like to share my lessons learned and experience to prevent this type of issue.
We have the following table definition:
CREATE Table TestTable(ID INT IDENTITY(1,1), string_column NVARCHAR(500))
--We added dummy data in the table running the following script.
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
-- Finally, we created a new index for this column.
create index TestTable_Ix1 on TestTable (String_column)
Our customer identified that the application is generating this query:
SELECT TOP 1 * FROM TestTable WHERE string_column = N'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
To reproduce the issue and understand the impact about how many execution plan our customer reported, we started running the demo function called StartAdhocNoParam: This function executes a non-parameterized query. Running the following DMV to identify the number of plans we could see around 13K cached plans.
-- dbcc freeproccache --Only to clear the cache.
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%'
In this situation, we changed the property of the database called Parameterization to Force, to This resulted in only one execution plan with a parameter. That's is great but our customer wants to modify the source code and avoiding using Parameterization to Force.
Additionally:
- OPTIMIZE_FOR_AD_HOC_WORKLOADS might reduce the memory usage, altohough it may not promote the plan reuse - Database scoped optimizing for ad hoc workloads - Microsoft Community Hub
- Also, review the option called plan guides that might help on that - Create a New Plan Guide - SQL Server | Microsoft Learn
When our customer finished the modification of their code, we noticed that their application is not specifing the size of parameter or specifing the length of the text that the application is searching, like we could see in the function demo StartAdhocWithParam.
This function is going to run a parametrized query using different length for the parameter, because, for example, if the application is not specifying the length of the parameter or the text that is looking for. In this situation, running the DMV to identify the number of plans we could see around 500 cached plans.
In this situation, we suggested using the function StartParametrize, specifying the max length of the column (500), we could have only an action plan. This reduced the cached plan usage.
This exercise highlights the importance of specifying the length of the parameter,
Finally, I would like to share two new functions:
- ImprovedVersionStartParametrize that helps us to reduce the roundtrips of the text sent to the database, only sending values.
- GetColumnLength that connects to the database to determine the total size of the column base on the internal table INFORMATION_SCHEMA.columns to perform this more dynamic.
sing System;
using System.Data;
using Microsoft.Data.SqlClient;
class Program
{
static void Main()
{
// Parámetros de conexión
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;
}
}
}
}
}
Disclaimer
The use of this application and the provided scripts is intended for educational and informational purposes only. The scripts and methods demonstrated in this guide are provided "as is" without any warranties or guarantees. It is the user's responsibility to ensure the accuracy, reliability, and suitability of these tools for their specific needs.