Lesson Learned #437:Why Does Your SQL Query Run Faster Here Than There?

[Jose_Manuel_Jurado]

Ever faced a scenario where the exact SQL query yields different execution times on two databases? We'll dissect this phenomenon using a specific query as our protagonist: SELECT * FROM dbo.PerformanceVarcharNVarchar WHERE TexttoSearch = 'Detail 1'. This article will serve as your guide to understanding and optimizing SQL query performance discrepancies.

Section 1: Setting the Stage

 

1.1 A Tale of Two Databases:

We commence with two databases, DB1 and DB2, and a query that performs differently. Let's explore the structures, indexes, and statistics of dbo.PerformanceVarcharNVarchar, the table in question in both databases.

declare @Table as sysname = 'PerformanceVarcharNVarchar'
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @Table

SELECT
    obj.name AS table_name,
    stat.name AS stat_name,
    stat.filter_definition,
    sc.last_updated,
    sc.rows,
    sc.rows_sampled,
    sc.modification_counter,
    col.name AS column_name,
    stat.auto_created,
    stat.no_recompute,
    stat.user_created,
    stat.is_incremental,
    stat.is_temporary
FROM
    sys.stats stat
JOIN 
    sys.stats_columns stat_col ON stat.object_id = stat_col.object_id AND stat.stats_id = stat_col.stats_id
JOIN 
    sys.columns col ON stat_col.object_id = col.object_id AND stat_col.column_id = col.column_id
JOIN
    sys.objects obj ON stat.object_id = obj.object_id
OUTER APPLY 
    sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sc
WHERE
    obj.name = @table

	SELECT
    i.name as index_name,
    i.type_desc,
    ic.key_ordinal,
    c.name as column_name
FROM sys.indexes as i
JOIN sys.index_columns as ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns as c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID(@table);

SELECT * FROM sys.configurations;

SELECT * FROM sys.databases


-- How many rows?
SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
    AND t.name = @Table
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name
	
-- Do we have partitioning
SELECT
    i.name AS IndexName,
    ds.type_desc AS PartitionScheme,
    ps.name AS PartitionName,
    pf.name AS PartitionFunction
FROM
    sys.indexes i
INNER JOIN 
    sys.data_spaces ds ON i.data_space_id = ds.data_space_id
LEFT JOIN 
    sys.partition_schemes ps ON ds.data_space_id = ps.data_space_id
LEFT JOIN 
    sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE
    i.object_id = OBJECT_ID(@Table) AND i.index_id < 2;

-- Index details
SELECT
    i.name AS IndexName,
    i.type_desc AS IndexType,
    k.column_id,
    c.name AS ColumnName,
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint,
    f.fill_factor
FROM
    sys.indexes i
INNER JOIN
    sys.index_columns k ON i.object_id = k.object_id AND i.index_id = k.index_id
INNER JOIN
    sys.columns c ON k.object_id = c.object_id AND k.column_id = c.column_id
LEFT JOIN
    sys.indexes f ON i.object_id = f.object_id AND i.index_id = f.index_id
WHERE
    i.object_id = OBJECT_ID(@Table)
ORDER BY
    i.index_id, k.key_ordinal;

-- Table Structure
SELECT
    c.name AS ColumnName,
    t.Name AS DataType,
    c.max_length AS MaxLength,
    c.precision,
    c.scale,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) AS IsPrimaryKey
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID(@Table)
ORDER BY
    c.column_id;

Section 2: Into the Query Battlefield

 

2.1 Executing 'Query':

With our environment primed, watch SELECT * FROM dbo.PerformanceVarcharNVarchar WHERE TexttoSearch = 'Detail 1' in action and observe the metrics it produces, shedding light on wait times, task count differences, and other pivotal factors influencing its performance.

Section 3: Unveiling the Differences

Dive deep into a comparative analysis where we decode the variations and provide you actionable insights for optimizing SELECT * FROM dbo.PerformanceVarcharNVarchar WHERE TexttoSearch = 'Detail 1', enhancing its efficiency and performance in any given database.

-- This section of the script selects columns such as wait_type, waiting_tasks_count, and wait_time_ms
-- from the sys.dm_exec_session_wait_stats DMV where the session_id matches the current session (@@SPID). 
-- The result of this SELECT statement is then stored into a temporary table named #InitialWaitStats.
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms
INTO #InitialWaitStats
FROM 
    sys.dm_exec_session_wait_stats
WHERE 
    session_id = @@SPID;

-- WARNING: DBCC FREEPROCCACHE will clear all elements in the plan cache for the instance of SQL Server. 
-- Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause 
-- a sudden, temporary decrease in query performance. In production environments, this command should be used carefully.
DBCC FREEPROCCACHE; 

-- WARNING: DBCC DROPCLEANBUFFERS removes all clean buffers from the buffer pool, and columnstores from 
-- the columnstore object pool. It should only be used as a testing tool and should not be used in production environments.
DBCC DROPCLEANBUFFERS;

-- SET STATISTICS IO ON, when activated, causes SQL Server to display information regarding the amount of disk activity 
-- generated by Transact-SQL statements. It’s helpful in performance tuning as you can understand the number of scans or 
-- seeks needed for your queries.
SET STATISTICS IO ON 

-- SET STATISTICS TIME ON displays the amount of time (in milliseconds) that is needed to parse, compile, and execute each statement.
SET STATISTICS TIME ON 

-- Execution of the query in question, capturing metrics for analysis.
SELECT * FROM dbo.PerformanceVarcharNVarchar WHERE TexttoSearch = 'Detail 1';

-- Disabling the statistics options post query execution.
SET STATISTICS XML OFF;
SET STATISTICS IO Off;

-- This part of the script compares the initial and final wait statistics to calculate the differences. 
-- The calculated differences (task_count_diff and wait_time_diff) will provide insights into the wait stats 
-- incurred during the execution of the SELECT statement.
SELECT 
    final.wait_type,
    final.waiting_tasks_count - ISNULL(initial.waiting_tasks_count, 0) AS task_count_diff,
    final.wait_time_ms - ISNULL(initial.wait_time_ms, 0) AS wait_time_diff
FROM 
    (SELECT * FROM sys.dm_exec_session_wait_stats WHERE session_id = @@SPID) final
LEFT JOIN 
    #InitialWaitStats initial ON final.wait_type = initial.wait_type
ORDER BY 
    wait_time_diff DESC;

 

Explanation and Warnings:

• DBCC FREEPROCCACHE:
  • Explanation: This command clears the plan cache, forcing SQL Server to recompile queries, and is often used to ensure that performance testing is done with a cold cache.
  • Warning: It can lead to CPU overhead due to recompilation of queries. It should be used with caution, especially in a production environment.
• DBCC DROPCLEANBUFFERS:
  • Explanation: It is used to remove all buffers from the buffer pool, providing a clean environment for testing the performance of a query without data cached in memory.
  • Warning: This command is resource-intensive and can affect performance. It should never be used on a production server unless absolutely necessary.

Remember to use these commands responsibly, especially on production servers, as they can significantly impact performance and the workload of your SQL Server instance. Always ensure you have appropriate permissions and backups before running such commands.

 

Other important aspect is to compare the executions plans using SSMS:

 

Steps for Visual Comparison within SSMS:

1. Generate and Save Execution Plans:

   • Run the queries on both databases with the actual execution plan enabled. You can do this by clicking on the “Include Actual Execution Plan” icon in the toolbar or by pressing Ctrl + M before running your query.

1. • After execution, you will see the execution plan tab. Right-click on the execution plan and select “Save Execution Plan As...”. Save the plans as .sqlplan files.

2. Open Saved Execution Plans:

   • In SSMS, open the saved .sqlplan files. You can open them side by side in separate tabs.

3. Visual Comparison:

   • Now you can visually inspect and compare the execution plans. Look for differences in the plan's structure, the order of operations, the operators used, and their properties.
   • Pay attention to the tooltips on each operator, as they provide detailed information. Inspect the estimated number of rows, actual number of rows, CPU cost, I/O cost, and other relevant metrics.

4. Deep Dive into Operators:

   • Specific operators may have different costs and cardinality estimates between the two plans. Carefully analyze any significant discrepancies, as these often highlight the main performance differences.

5. Compare Tooltips and Properties:

   • When you hover over an operator or click on it, you will see detailed information. Compare these tooltips and properties between the two plans to gain insights into the differences in estimates, costs, and actual performance metrics.

Important Points:

• Query Cost Relative to Batch:

  • Look at the "Query cost (relative to the batch)" metric to understand how expensive each query is compared to others.

• Color-Coding:

  • SSMS uses color-coding to indicate the relative cost of operators. Darker shades usually represent higher costs.

• Multiple Execution Plans:

  • If there are multiple statements in the batch, SSMS will display multiple execution plans. Ensure you are comparing the correct plans for your target queries.

Enjoy!