This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .
When working with databases, high resource usage or a query reporting a timeout could indicate that the statistics of the tables involved in the query are not up to date, that we might be missing indexes, or that there are excessive blocking as the most common elements and possible causes of performance loss. For this reason, I would like to add in this article elements that can help us determine what might be happening with our query.
CommandTimeout and Obtaining Statistics
The inception point of our exploration is the creation of a stored procedure, sp_AnalyzeQueryStatistics. This procedure is designed to take a SQL query as input, specified through the @SQLQuery parameter, and dissect it to unveil the underlying schema and tables it interacts with.
Crafting
sp_AnalyzeQueryStatistics: The core functionality of this procedure leverages the sys.dm_exec_describe_first_result_set DMV. This invaluable tool provides a window into the query's anatomy, pinpointing the schema and tables entwined in its execution path.
CREATE PROCEDURE sp_AnalyzeQueryStatistics
@SQLQuery NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TableNames TABLE (
SourceSchema NVARCHAR(128),
TableName NVARCHAR(128)
);
INSERT INTO @TableNames (SourceSchema, TableName)
SELECT DISTINCT
source_schema AS SourceSchema,
source_table AS TableName
FROM
sys.dm_exec_describe_first_result_set(@SQLQuery, NULL, 1) sp
WHERE sp.error_number IS NULL AND NOT sp.source_table is NULL
SELECT
t.TableName,
s.name AS StatisticName,
STATS_DATE(s.object_id, s.stats_id) AS LastUpdated,
sp.rows,
sp.rows_sampled,
sp.modification_counter
FROM
@TableNames AS t
INNER JOIN
sys.stats AS s ON s.object_id = OBJECT_ID(QUOTENAME(t.SourceSchema) + '.' + QUOTENAME(t.TableName))
CROSS APPLY
sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp;
END;
Diving Deeper with Table Statistics:
Identification is just the precursor; the crux lies in scrutinizing the statistics of these identified tables. By employing sys.stats and sys.dm_db_stats_properties, we delve into the statistical realm of each table, gleaning insights into data distribution, sampling rates, and the freshness of the statistics.
Informed Decision-Making:
This statistical audit empowers us with the knowledge to make data-driven decisions. Should the rows_sample significantly deviate from the total rows, or the statistics' last update be a for example 2 months, it's a clarion call for action—be it updating the statistics or reevaluating index strategies.
C# Implementation:
using System;
using System.Diagnostics;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
namespace HighCPU
{
class Program
{
private static string ConnectionString = "Server=tcp:myservername.database.windows.net,1433;User Id=MyUser;Password=MyPassword;Initial Catalog=MyDb;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";
private static string Query = "SELECT * FROM [MSxyzTest].[_x_y_z_MS_HighDATAIOBlocks] ORDER BY NEWID() DESC";
static async Task Main(string[] args)
{
SqlConnection connection = await EstablishConnectionWithRetriesAsync(3, 2000);
if (connection == null)
{
Console.WriteLine("Failed to establish a database connection.");
return;
}
await ExecuteQueryWithRetriesAsync(connection, 5, 1000, 100000,2,true);
connection.Close();
}
private static async Task<SqlConnection> EstablishConnectionWithRetriesAsync(int maxRetries, int initialDelay)
{
SqlConnection connection = null;
int retryDelay = initialDelay;
for (int attempt = 1; attempt <= maxRetries; attempt++)
{
try
{
connection = new SqlConnection(ConnectionString);
await connection.OpenAsync();
Console.WriteLine("Connection established successfully.");
return connection;
}
catch (SqlException ex)
{
Console.WriteLine($"Failed to establish connection: {ex.Message}. Attempt {attempt} of {maxRetries}.");
if (attempt == maxRetries)
{
Console.WriteLine("Maximum number of connection attempts reached. The application will terminate.");
return null;
}
Console.WriteLine($"Waiting {retryDelay / 1000} seconds before the next connection attempt...");
await Task.Delay(retryDelay);
retryDelay *= 2;
}
}
return null;
}
private static async Task ExecuteQueryWithRetriesAsync(SqlConnection connection, int maxRetries, int initialDelay, int CancellationTokenTimeout, int CommandSQLTimeout, Boolean bReviewQuery = false)
{
int retryDelay = initialDelay;
for (int attempt = 1; attempt <= maxRetries; attempt++)
{
using (var cts = new CancellationTokenSource())
{
cts.CancelAfter(CancellationTokenTimeout*attempt);
try
{
using (SqlCommand command = new SqlCommand(Query, connection))
{
command.CommandTimeout = CommandSQLTimeout*attempt;
Stopwatch stopwatch = Stopwatch.StartNew();
await command.ExecuteNonQueryAsync(cts.Token);
stopwatch.Stop();
Console.WriteLine($"Query executed successfully in {stopwatch.ElapsedMilliseconds} milliseconds.");
return;
}
}
catch (TaskCanceledException)
{
Console.WriteLine($"Query execution was canceled by the CancellationToken. Attempt {attempt} of {maxRetries}.");
}
catch (SqlException ex) when (ex.Number == -2)
{
Console.WriteLine($"Query execution was canceled due to CommandTimeout. Attempt {attempt} of {maxRetries}.");
if (bReviewQuery)
{ await ReviewQuery(); }
}
catch (SqlException ex) when (ex.Number == 207 || ex.Number == 208 || ex.Number == 2627)
{
Console.WriteLine($"SQL error preventing retries: {ex.Message}");
return;
}
catch (Exception ex)
{
Console.WriteLine($"An exception occurred: {ex.Message}");
return;
}
Console.WriteLine($"Waiting {retryDelay / 1000} seconds before the next query attempt...");
await Task.Delay(retryDelay);
retryDelay *= 2;
}
}
}
private static async Task ReviewQuery()
{
SqlConnection connection = await EstablishConnectionWithRetriesAsync(3, 2000);
if (connection == null)
{
Console.WriteLine("Review Query - Failed to establish a database connection.");
return;
}
await ReviewQueryWithRetriesAsync(connection, 5, 1000, 10000, 15);
connection.Close();
}
private static async Task ReviewQueryWithRetriesAsync(SqlConnection connection, int maxRetries, int initialDelay, int CancellationTokenTimeout, int CommandSQLTimeout, Boolean bReviewQuery = false)
{
int retryDelay = initialDelay;
for (int attempt = 1; attempt <= maxRetries; attempt++)
{
using (var cts = new CancellationTokenSource())
{
cts.CancelAfter(CancellationTokenTimeout * attempt);
try
{
using (SqlCommand command = new SqlCommand("sp_AnalyzeQueryStatistics", connection))
{
command.CommandTimeout = CommandSQLTimeout * attempt;
command.CommandType = CommandType.StoredProcedure;
Stopwatch stopwatch = Stopwatch.StartNew();
command.Parameters.Add(new SqlParameter("@SQLQuery", SqlDbType.NVarChar, -1));
command.Parameters["@SQLQuery"].Value = Query;
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
Console.WriteLine("TableName: " + reader["TableName"].ToString());
Console.WriteLine("StatisticName: " + reader["StatisticName"].ToString());
Console.WriteLine("LastUpdated: " + reader["LastUpdated"].ToString());
Console.WriteLine("Rows: " + reader["Rows"].ToString());
Console.WriteLine("RowsSampled: " + reader["Rows_Sampled"].ToString());
Console.WriteLine("ModificationCounter: " + reader["Modification_Counter"].ToString());
Console.WriteLine("-----------------------------------");
}
}
stopwatch.Stop();
Console.WriteLine($"Query executed successfully in {stopwatch.ElapsedMilliseconds} milliseconds.");
return;
}
}
catch (TaskCanceledException)
{
Console.WriteLine($"Query execution was canceled by the CancellationToken. Attempt {attempt} of {maxRetries}.");
}
catch (SqlException ex) when (ex.Number == -2)
{
Console.WriteLine($"Query execution was canceled due to CommandTimeout. Attempt {attempt} of {maxRetries}.");
if (bReviewQuery)
{ }
}
catch (SqlException ex) when (ex.Number == 207 || ex.Number == 208 || ex.Number == 2627)
{
Console.WriteLine($"SQL error preventing retries: {ex.Message}");
return;
}
catch (Exception ex)
{
Console.WriteLine($"An exception occurred: {ex.Message}");
return;
}
Console.WriteLine($"Waiting {retryDelay / 1000} seconds before the next query attempt...");
await Task.Delay(retryDelay);
retryDelay *= 2;
}
}
}
}
}
The Bigger Picture
This initial foray into CommandTimeout and statistics is merely the tip of the iceberg. It sets the stage for a broader discourse on query performance, where each element—from indexes to execution plans—plays a crucial role. Our series aims to arm you with the knowledge and tools to not just react to performance issues but to anticipate and mitigate them proactively, ensuring your databases are not just operational but optimized for efficiency and resilience.
Stay tuned as we continue to peel back the layers of SQL performance tuning, offering insights, strategies, and practical advice to elevate your database management game.