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.<\/p>\n

Let\u2019s start by dropping clean buffers (on a test system, please):<\/p>\n

\r\nDBCC DROPCLEANBUFFERS;\r\n\r\nUSE [Credit];\r\nGO\r\n\r\nSELECT\u00a0 COUNT(*) AS [page_count]\r\nFROM\u00a0\u00a0\u00a0 [sys].[dm_os_buffer_descriptors] AS bd\r\nWHERE\u00a0\u00a0 [bd].[database_id] = DB_ID() AND\r\n[bd].[allocation_unit_id] = 15045483298816;\r\nGO\r\n<\/pre>\n
This returns 0 rows for the allocation_unit_id associated with the table we\u2019re about to query from the Credit database:<\/p>\n
\r\nSELECT\u00a0 [charge].[charge_no]\r\nFROM\u00a0\u00a0\u00a0 [dbo].[charge]\r\nORDER BY [charge].[charge_no];\r\nGO\r\n<\/pre>\n
The actual plan shows the following (via SQL Sentry Plan Explorer):<\/p>\n
<\/a><\/p>\n
Nothing fancy, just a Clustered Index Scan.\u00a0 And in terms of page counts from sys.dm_os_buffer_descriptors, we see 9,303 data pages in cache now.<\/p>\n
Now let\u2019s drop the clean buffers and execute a query returning just the top 100 rows:<\/p>\n
\r\nDBCC DROPCLEANBUFFERS;\r\n\r\nUSE [Credit];\r\nGO\r\n\r\nSELECT TOP 100\r\n[charge].[charge_no]\r\nFROM\u00a0\u00a0\u00a0 [dbo].[charge]\r\nORDER BY [charge].[charge_no];\r\nGO\r\n<\/pre>\n
The actual plan is as follows:<\/p>\n
<\/a><\/p>\n
Re-executing the query against sys.dm_os_buffer_descriptors, this time we see just 13 data pages.<\/p>\n
The original question\/discussion was with regards to the storage engine \u2013 and whether all data pages still get loaded into memory even with a TOP.\u00a0 As we see in this scenario, this was not the case.<\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019s start by dropping clean […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,28],"tags":[],"class_list":["post-1006","post","type-post","status-publish","format-standard","hentry","category-execution-plan","category-performance"],"yoast_head":"\n