Query Execution

Reading Query Plans

SQL Server supports displaying the query plans as :

  • Graphical
  • Text
  • XML

Display Graphical Query Plan

Using SQL Server Management Studio we can display two types of the execution plan :

  • Estimated Execution Plan
  • Actual Execution Plan

Graphical Execution Plans

SQL Server Join Operators

  • Hash joins
  • Merge joins
  • Nested loop joins

Hash Join

Let’s use the following query to look at the Hash Join :

USE AdventureWorks2012
GO
SELECT  p.*
FROM  Production.Product p
JOIN Production.ProductCategory pc
ON p.ProductSubcategoryID = pc.ProductCategoryID;

The query above will produce the following query plan :

Hash Join Execution Plan

The query optimizer uses hash joins to process large, unsorted, nonindexed inputs efficiently.

Merge Join

n the previous case, input from the Product table is larger, and the table is not indexed on the joining column (ProductCategorylD). Using the following simple query, you can see different behavior:

SELECT  pm.*
FROM    Production.ProductModel pm
JOIN    Production.ProductModelProductDescriptionCulture pmpd
ON pm.ProductModelID = pmpd.ProductModelID ;

Characteristics :

  • The optimizer chooses for medium workloads
  • Requires sorted inputs
  • Can use an index to provide sorted inputs
  • Reads the inputs only once

Nested Loop Join

The optimizer uses the Nested Join for workloads with a small resultset. Lets us the following query and see the query plan :

SELECT  pm.*
FROM    Production.ProductModel pm
JOIN    Production.ProductModelProductDescriptionCulture pmpd
ON pm.ProductModelID = pmpd.ProductModelID
WHERE pm.Name = 'HL Mountain Front Wheel';

Characteristics

  • Smaller results
  • The inner table is executed as many times as the row of the outer table

Query Resource Cost

Even though the execution plan for a query provides a detailed processing strategy and the estimated relative costs of the individual steps involved, it doesn’t provide the actual cost of the query in terms of CPU usage, reads/writes to disk, or query duration. You will need to do optimization to reduce the cost of the queires.

Query Plans

SQL Server breaks an SQL query into blocks called iterators. Each iterator represents a single function or operation. It then uses these iterators to build a query tree known as an execution plan. A single statement can produce different execution plans. The SQL Server optimizer is responsible for costing the least expensive query plan for execution.

sys.dm_exec_requests and sys.dm_exec_sessions

  • sys.dm_exec_requests - displays the requests currently executing on SQL Server
  • sys.dm_exec_sessions - shows the authenticated sessions on the instance.

Open a new query window and record your session id and replace it in the query :

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
SELECT cpu_time, reads, total_elapsed_time, logical_reads, row_count
FROM sys.dm_exec_requests
WHERE session_id = <session id>
GO
SELECT cpu_time, reads, total_elapsed_time, logical_reads, row_count
FROM sys.dm_exec_sessions
WHERE session_id = <session id>

and in another window run some queries and as you run, run the queries with the dmvs and observe. You will notice that the query stats are only available for the sys.dm_exec_requests only whilst the query is running. The values in sys.dm_exec_sessions keeps accumulating since they record for the session.