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

Posted by

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

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:


    • 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.
    • 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.
    • 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.
  1. Open Saved Execution Plans:

    • In SSMS, open the saved .sqlplan files. You can open them side by side in separate tabs.
  2. 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.
  3. 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.
  4. 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.



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.