Warning: Constant WP_TEMP_DIR already defined in /var/www/html/blogs/joe/wp-config.php on line 93

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
{"id":966,"date":"2013-04-14T17:19:38","date_gmt":"2013-04-15T00:19:38","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=966"},"modified":"2013-04-14T17:24:16","modified_gmt":"2013-04-15T00:24:16","slug":"data-page-count-influence-on-the-query-execution-plan","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/data-page-count-influence-on-the-query-execution-plan\/","title":{"rendered":"Data Page Count Influence on the Query Execution Plan"},"content":{"rendered":"

In my post Row and Page Count Influence on Estimated CPU and I\/O Cost<\/a> I demonstrated how I\/O cost of a clustered index scan had sensitivity to page counts but not row counts.\u00a0 For this post I\u2019ll lay out a direct connection between data page counts and the query execution plan that gets generated.<\/p>\n

To illustrate the scenario, I created a table in the Credit database based on the charge table and I added two indexes, one clustered and one nonclustered:<\/p>\n

 \r\nUSE [Credit];\r\nGO\r\n\r\nSELECT TOP 575000\r\n[charge_no],\r\n[member_no],\r\n[provider_no],\r\n[category_no],\r\n[charge_dt],\r\n[charge_amt],\r\n[statement_no],\r\n[charge_code]\r\nINTO [dbo].[charge_demo]\r\nFROM [dbo].[charge];\r\nGO\r\n\r\nCREATE CLUSTERED INDEX [charge_demo_charge_no]\r\nON [dbo].[charge_demo] ([charge_no]);\r\nGO\r\n\r\nCREATE NONCLUSTERED INDEX [charge_demo_charge_amt]\r\nON [dbo].[charge_demo] ([charge_amt])\r\nINCLUDE ([member_no])\r\nWITH (FILLFACTOR = 100);\r\nGO\r\n <\/pre>\n
Next, I checked the data page counts by index for this new 575,000 row table:<\/p>\n
\r\nSELECT\u00a0 [index_id],\r\n[in_row_data_page_count]\r\nFROM\u00a0\u00a0\u00a0 [sys].[dm_db_partition_stats]\r\nWHERE\u00a0\u00a0 [object_id] = OBJECT_ID('dbo.charge_demo');\r\nGO\r\n<\/pre>\n
The clustered index has 3,426 data pages and the nonclustered index has 1,567 data pages.<\/p>\n
Next I looked at the execution plan for the following query:<\/p>\n
\r\nSELECT [member_no],\r\nSUM([charge_amt]) AS [charge_amt]\r\nFROM [dbo].[charge_demo]\r\nWHERE [charge_amt] &gt; 0\r\nGROUP BY [member_no]\r\nOPTION (RECOMPILE);\r\nGO\r\n <\/pre>\n
The query execution plan (via SQL Sentry Plan Explorer) was as follows:<\/p>\n
\"image\"<\/a><\/p>\n
The overall estimated subtree cost for the plan ended up being 4.6168.<\/p>\n
Next, I rebuilt the nonclustered index using a very low fill factor (far lower than I would ever recommend, but I was doing this to demonstrate the placement of the same number of rows over many more pages than the original default fill factor):<\/p>\n
\r\nCREATE NONCLUSTERED INDEX [charge_demo_charge_amt]\r\nON [dbo].[charge_demo] ([charge_amt])\r\nINCLUDE ([member_no])\r\nWITH (FILLFACTOR = 1, DROP_EXISTING = ON);\r\nGO\r\n <\/pre>\n
The clustered index still has 3,426 data pages (since we didn\u2019t change it), but now the nonclustered index has 143,753 data pages instead of the original 1,567 data pages.\u00a0 And again, this represents the same 575,000 row count. Re-executing the original test query, I saw the following changed plan:<\/p>\n
\"image\"<\/a><\/p>\n
The overall estimated subtree cost for the plan increased to 54.3065 with a few other significant changes as well.\u00a0 The second plan switched to using a clustered index scan instead of a nonclustered index seek.\u00a0 Also, the second plan uses a stream aggregate with an \u201cinjected\u201d sort operation, instead of the original plan\u2019s hash match aggregate operation.<\/p>\n","protected":false},"excerpt":{"rendered":"
In my post Row and Page Count Influence on Estimated CPU and I\/O Cost I demonstrated how I\/O cost of a clustered index scan had sensitivity to page counts but not row counts.\u00a0 For this post I\u2019ll lay out a direct connection between data page counts and the query execution plan that gets generated. To […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24,28],"tags":[],"class_list":["post-966","post","type-post","status-publish","format-standard","hentry","category-indexing","category-performance"],"yoast_head":"\nData Page Count Influence on the Query Execution Plan - Joe Sack<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/data-page-count-influence-on-the-query-execution-plan\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Data Page Count Influence on the Query Execution Plan - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"In my post Row and Page Count Influence on Estimated CPU and I\/O Cost I demonstrated how I\/O cost of a clustered index scan had sensitivity to page counts but not row counts.\u00a0 For this post I\u2019ll lay out a direct connection between data page counts and the query execution plan that gets generated. To […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/data-page-count-influence-on-the-query-execution-plan\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2013-04-15T00:19:38+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-04-15T00:24:16+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/04\/image_thumb.png\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/data-page-count-influence-on-the-query-execution-plan\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/data-page-count-influence-on-the-query-execution-plan\/\",\"name\":\"Data Page Count Influence on the Query Execution Plan - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2013-04-15T00:19:38+00:00\",\"dateModified\":\"2013-04-15T00:24:16+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/data-page-count-influence-on-the-query-execution-plan\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/data-page-count-influence-on-the-query-execution-plan\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/data-page-count-influence-on-the-query-execution-plan\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Indexing\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/indexing\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Data Page Count Influence on the Query Execution Plan\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Data Page Count Influence on the Query Execution Plan - Joe Sack","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/joe\/data-page-count-influence-on-the-query-execution-plan\/","og_locale":"en_US","og_type":"article","og_title":"Data Page Count Influence on the Query Execution Plan - Joe Sack","og_description":"In my post Row and Page Count Influence on Estimated CPU and I\/O Cost I demonstrated how I\/O cost of a clustered index scan had sensitivity to page counts but not row counts.\u00a0 For this post I\u2019ll lay out a direct connection between data page counts and the query execution plan that gets generated. To […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/data-page-count-influence-on-the-query-execution-plan\/","og_site_name":"Joe Sack","article_published_time":"2013-04-15T00:19:38+00:00","article_modified_time":"2013-04-15T00:24:16+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/04\/image_thumb.png"}],"author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/data-page-count-influence-on-the-query-execution-plan\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/data-page-count-influence-on-the-query-execution-plan\/","name":"Data Page Count Influence on the Query Execution Plan - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2013-04-15T00:19:38+00:00","dateModified":"2013-04-15T00:24:16+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/data-page-count-influence-on-the-query-execution-plan\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/data-page-count-influence-on-the-query-execution-plan\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/data-page-count-influence-on-the-query-execution-plan\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Indexing","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/indexing\/"},{"@type":"ListItem","position":3,"name":"Data Page Count Influence on the Query Execution Plan"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/966","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=966"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/966\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=966"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=966"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=966"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}