sqlskills-logo-2015-white.png

Control Flow vs. Data Flow Demo

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 buffers (on a test system, please):

DBCC DROPCLEANBUFFERS;

USE [Credit];
GO

SELECT  COUNT(*) AS [page_count]
FROM    [sys].[dm_os_buffer_descriptors] AS bd
WHERE   [bd].[database_id] = DB_ID() AND
[bd].[allocation_unit_id] = 15045483298816;
GO

This returns 0 rows for the allocation_unit_id associated with the table we’re about to query from the Credit database:

SELECT  [charge].[charge_no]
FROM    [dbo].[charge]
ORDER BY [charge].[charge_no];
GO

The actual plan shows the following (via SQL Sentry Plan Explorer):

image

Nothing fancy, just a Clustered Index Scan.  And in terms of page counts from sys.dm_os_buffer_descriptors, we see 9,303 data pages in cache now.

Now let’s drop the clean buffers and execute a query returning just the top 100 rows:

DBCC DROPCLEANBUFFERS;

USE [Credit];
GO

SELECT TOP 100
[charge].[charge_no]
FROM    [dbo].[charge]
ORDER BY [charge].[charge_no];
GO

The actual plan is as follows:

image

Re-executing the query against sys.dm_os_buffer_descriptors, this time we see just 13 data pages.

The original question/discussion was with regards to the storage engine – and whether all data pages still get loaded into memory even with a TOP.  As we see in this scenario, this was not the case.

2 thoughts on “Control Flow vs. Data Flow Demo

  1. read ahead read also impacts the output. Try the same thing on adventureworks2008R2. If traceflag 652 is off then for 30 rows around 484 pages are brought into cache. There are around 480 read ahead reads.For 50 or more rows it brings all the pages into memory. If the trace flag is off then just 11 pages comes into the buffer.

    Also off course if no index on the column specified in orderby then It has to read all pages whether topp has specified just 1 row.

    I ran below query.

    select top 30 * from sales.salesorderheader order by slaesorderid

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.