Share via

SQL Optimizations – A well concealed transform that can cut that much needed millisecond out of your query.

  • Article

SQL Server Optimizer is full of surprises… Optimizer uses many transformations to find the best possible path of execution. One of those happen in cases where you do a query that looks like the following;

-- displays the duration, cpu, reads, writes and database id

-- for the query with the max duration from a profiler trace table

select duration,cpu,reads,writes, databaseid

from dbo.trace_data a

where duration = (select max(duration) from dbo.trace_data b)

Looking at the query many may think we scan the table twice; first to find the max(Duration) then to locate the CPU, Reads, Writes for this instance of Duration. However the optimizer in this case chooses to use a single table scan and "TOP 1" to locate the max(Duration) and to pull the rest of the columns out of the row. The Parallelism (Logical Operator Gather Stream) allows parallel Table Scan and Parallel Sort operations to be merged into a single stream before Top operator is processed.

Here is the execution plan;


(Click on the image to enlarge)

By the way depending on the size of your data and underlying indexing SQL Server may still choose to do multiple table scans with a join on the primary key.

 

Optimizer also will take care of more complex cases with groupings like the following query that outputs the same information but for every database in the trace;


(Click on the image to enlarge)

In this instance you see even the TOP parallelized. Also Parallelism (Reparatition Streams) allows randomized streams from the Table Scan to be partitioned over databaseid do sorting can happen within each stream. Parallelism Gather Streams reduces the multiple results into s single stream once we locate the top out of every segment (in this case; databaseid).

This is a simple example of a transform we are doing in the sql engine. SQL Server optimizer is constantly evolving to add new transforms to get better. Keep checking those execution plans and I am quite certain you will find other interesting transforms you did not know the optimizer was applying to your queries.

Comments

  • Anonymous
    May 05, 2006


    This is an intersting walk through transforms that the optimizer makes to a
    query. The degree of...
  • Anonymous
    July 22, 2007
    UPDATE STATISTICS is very helpful to get the performance by updating the distribution of key values for