I had an interesting question yesterday in class about query execution plan control flow (from the root of the plan to the leaf level) and data flow (from leaf to root) and thought I would share a small demo I put together over lunch break to walk through the discussion. Let’s start by dropping clean […]
Joe Sack
SQL Server Performance Tuning, High Availability and Disaster Recovery Blog
Control Flow vs. Data Flow Demo
Posted on: June 13, 2013 1:51 am
Redundant Query Plan Branches
Posted on: February 27, 2013 3:29 pm
Consider the following “basic_member” view definition from the Credit database: A simple SELECT from this view returns 8,498 rows and has the following plan shape (and I’m boxing in an “areas of interest” via SQL Sentry Plan Explorer’s rendering of the plan): We see that the view has a predicate on member_no NOT IN the […]
What Does the Future Hold for Cardinality Estimates and Cost Models in Windows Azure SQL Database?
Posted on: September 11, 2012 11:05 am
I’ve been interested in exploring what changes may be coming through in SQL Azure (Windows Azure – “SQL Database” now) around query execution cost models and cardinality estimates being fed to them. This was motivated by a couple of things… First of all, I saw a blog post from Grant Fritchey where he noticed that […]
Capturing Transient Query Plan Changes
Posted on: August 13, 2012 11:45 am
I had a recent consulting engagement where a query had unpredictable performance on an isolated test environment. I had two initial questions: – What were the wait stats associated with the unpredictable query? – What did the query execution plan look like in the “good” versus “bad” condition? To address the query wait stats question, […]
SQL Server 2012’s Information on Parallel Thread Usage
Posted on: August 8, 2012 8:10 pm
SQLServerCentral published an article today that I wrote about SQL Server 2012’s execution plan parallel thread usage statistics: SQL Server 2012’s Information on Parallel Thread Usage The new thread statistics show information on the number of concurrent execution paths within an execution plan, the count of used threads and also the count of reserved threads […]
Memory Grant Execution Plan Statistics
Posted on: July 31, 2012 9:41 am
SQL Server 2008 execution plans include an optional MemoryGrant attribute in the QueryPlan element – but SQL Server 2012 significantly expands on memory grant related statistics with the new MemoryGrantInfo element and associated attributes. Here is an example of MemoryGrantInfo from an actual SQL Server 2012 execution plan: <MemoryGrantInfo SerialRequiredMemory="5632" SerialDesiredMemory="11016" RequiredMemory="47368" DesiredMemory="52808" RequestedMemory="52808" GrantWaitTime="0" […]
SQL Server 2012’s RetrievedFromCache Attribute
Posted on: July 31, 2012 6:29 am
SQL Server 2012 includes a new RetrievedFromCache attribute in the query execution plan. Let’s say I execute the following query immediately after executing DBCC FREEPROCCACHE: What value for RetrievedFromCache would you expect to see? In this example, I saw the following attribute value (with the attribute highlighted and StmtSimple abridged for clarity): <StmtSimple StatementCompId=”2″ StatementEstRows=”5″ […]
SpillToTempDb warning and SpillLevel’s mapping to single versus multiple pass
Posted on: July 27, 2012 7:31 am
This post shows a few examples of Sort related SpillToTempDb execution plan warnings and the associated SpillLevel attribute. This blog post is based on SQL Server 2012, version 11.0.2316 and I’m using the AdventureWorksDW2012 database and creating a separate version of the FactInternetSales table called FactInternetSales_Spill: SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, […]
Partitions Accessed and Partition Range in the Query Execution Plan
Posted on: April 23, 2012 6:10 am
Let’s say you are querying a partitioned table and you would like to see which partitions were accessed by looking at the graphical execution plan: “Actual Partition Count” shows a value of 1 and “Actual Partitions Accessed” shows a value of 50. The “Actual Partitions Accessed” property name could cause confusion though, since what you’re actually looking […]
