{"id":815,"date":"2013-01-08T13:12:03","date_gmt":"2013-01-08T21:12:03","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=815"},"modified":"2013-02-07T05:16:04","modified_gmt":"2013-02-07T13:16:04","slug":"exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost\/","title":{"rendered":"Row and Page Count Influence on Estimated CPU and I\/O Cost"},"content":{"rendered":"<p>In this post I&#8217;ll step through a few scenarios regarding row and page counts &#8211; and their associated influence on estimated CPU and I\/O cost. \u00a0I&#8217;ll be using the <a href=\"https:\/\/www.sqlskills.com\/resources\/conferences\/CreditBackup100.zip\" target=\"_blank\">Credit<\/a> database to demonstrate different scenarios&#8230;<\/p>\n<p><strong>Starting State<\/strong><\/p>\n<p>I&#8217;ll start off by checking the total number of reported data pages and row counts for the member table:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\"> \r\nSELECT &#x5B;in_row_data_page_count]\r\nFROM &#x5B;sys].&#x5B;dm_db_partition_stats]\r\nWHERE &#x5B;object_id] = OBJECT_ID('dbo.member') AND\r\n&#x5B;index_id] = 1;\r\n\r\nSELECT COUNT(*) AS &#x5B;row_count]\r\nFROM &#x5B;dbo].&#x5B;member];\r\n\r\n<\/pre>\n<p>The member table has 142 data pages and 10,000 rows.<\/p>\n<p>Next I&#8217;ll execute a simple query and check the estimated I\/O and estimated CPU of the Clustered Index Scan operator:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\"> \r\nSELECT &#x5B;member].&#x5B;member_no],\r\n&#x5B;member].&#x5B;lastname]\r\nFROM &#x5B;dbo].&#x5B;member]\r\nOPTION (RECOMPILE);\r\n<\/pre>\n<p>The Clustered Index Scan operator shows the following estimated costs:<\/p>\n<ul>\n<li>Estimated CPU Cost = 0.011157<\/li>\n<li>Estimated I\/O Cost = 0.107569<\/li>\n<\/ul>\n<p><strong>Inflating the page count while maintaining the row count<\/strong><\/p>\n<p>What happens if I inflate (artificially) the member table&#8217;s page count while maintaining the existing row count metadata?<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\"> \r\n-- Please don't use this in production - for demonstration purposes only!\r\nUPDATE STATISTICS dbo.&#x5B;member]\r\nWITH ROWCOUNT = 10000, PAGECOUNT = 149000;\r\n<\/pre>\n<p>If you&#8217;re curious about the method I&#8217;m using with ROWCOUNT and PAGECOUNT, see the SQL Server Query Optimization Team&#8217;s discussion on the subject:<\/p>\n<p><a href=\"http:\/\/blogs.msdn.com\/b\/queryoptteam\/archive\/2006\/07\/21\/674350.aspx\" target=\"_blank\" class=\"broken_link\"><\/p>\n<p style=\"padding-left: 30px;\">UPDATE STATISTICS undocumented options<\/p>\n<p><\/a><\/p>\n<p>Back to the scenario, querying\u00a0sys.dm_db_partition_stats shows a 149,000 in row data page count (incorrect, of course).<\/p>\n<p>Re-executing my previous SELECT statement &#8211; the Clustered Index Scan operator shows the following estimated costs:<\/p>\n<ul>\n<li>Estimated CPU Cost = 0.011157 (same as previous measurement of 0.011157)<\/li>\n<li>Estimated I\/O Cost = 110.373 (increased from 0.107569)<\/li>\n<\/ul>\n<p>Next I&#8217;ll reduce the (artificial) page count by half:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\"> \r\nUPDATE STATISTICS dbo.&#x5B;member]\r\nWITH ROWCOUNT = 10000, PAGECOUNT = 74500;\r\n<\/pre>\n<p>Re-executing the SELECT statement &#8211; the Clustered Index Scan operator shows the following estimated costs:<\/p>\n<ul>\n<li>Estimated CPU Cost = 0.011157 (same as previous measurement of 0.011157)<\/li>\n<li>Estimated I\/O Cost = 55.1987 (decreased from 110.373)<\/li>\n<\/ul>\n<p><strong>Inflating the row count while maintaining the page count<\/strong><\/p>\n<p>What happens if I inflate (unnaturally) the member table&#8217;s row count while maintaining the page count?<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\"> \r\n-- Please don't use this in production - for demonstration purposes only!\r\nUPDATE STATISTICS dbo.&#x5B;member]\r\nWITH ROWCOUNT = 100000000, PAGECOUNT = 142;\r\n<\/pre>\n<p>Re-executing the SELECT statement &#8211; the Clustered Index Scan operator shows the following estimated costs:<\/p>\n<ul>\n<li>Estimated CPU Cost = 110<\/li>\n<li>Estimated I\/O Cost = 0.107569 (no change from the original estimate with 142 data pages and 10,000 rows)<\/li>\n<\/ul>\n<p>Reducing the row count by half, I see the following estimated costs:<\/p>\n<ul>\n<li>Estimated CPU Cost = 55.0002<\/li>\n<li>Estimated I\/O Cost = 0.107569<\/li>\n<\/ul>\n<p><strong>Summary<\/strong><\/p>\n<p>So for this post I demonstrated two straight-forward scenarios:<\/p>\n<ul>\n<li>I\/O Cost of a Clustered Index Scan sensitivity to page counts, but not row counts.<\/li>\n<li>CPU Cost of a Clustered Index Scan sensitivity to row counts, but not data page counts.<\/li>\n<\/ul>\n<p>Why care? \u00a0Regarding page counts &#8211; think about the estimated I\/O cost impact that high fragmentation may have (same number of rows across many partially filled data pages). \u00a0Regarding row counts &#8211; even for narrow rows &#8211; consider the \u00a0estimated\u00a0CPU cost impact that high row counts may have.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this post I&#8217;ll step through a few scenarios regarding row and page counts &#8211; and their associated influence on estimated CPU and I\/O cost. \u00a0I&#8217;ll be using the Credit database to demonstrate different scenarios&#8230; Starting State I&#8217;ll start off by checking the total number of reported data pages and row counts for the member [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[],"class_list":["post-815","post","type-post","status-publish","format-standard","hentry","category-performance"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Row and Page Count Influence on Estimated CPU and I\/O Cost - 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\/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Row and Page Count Influence on Estimated CPU and I\/O Cost - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"In this post I&#8217;ll step through a few scenarios regarding row and page counts &#8211; and their associated influence on estimated CPU and I\/O cost. \u00a0I&#8217;ll be using the Credit database to demonstrate different scenarios&#8230; Starting State I&#8217;ll start off by checking the total number of reported data pages and row counts for the member [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2013-01-08T21:12:03+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-02-07T13:16:04+00:00\" \/>\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=\"3 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\/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost\/\",\"name\":\"Row and Page Count Influence on Estimated CPU and I\/O Cost - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2013-01-08T21:12:03+00:00\",\"dateModified\":\"2013-02-07T13:16:04+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/performance\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Row and Page Count Influence on Estimated CPU and I\/O Cost\"}]},{\"@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":"Row and Page Count Influence on Estimated CPU and I\/O Cost - 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\/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost\/","og_locale":"en_US","og_type":"article","og_title":"Row and Page Count Influence on Estimated CPU and I\/O Cost - Joe Sack","og_description":"In this post I&#8217;ll step through a few scenarios regarding row and page counts &#8211; and their associated influence on estimated CPU and I\/O cost. \u00a0I&#8217;ll be using the Credit database to demonstrate different scenarios&#8230; Starting State I&#8217;ll start off by checking the total number of reported data pages and row counts for the member [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost\/","og_site_name":"Joe Sack","article_published_time":"2013-01-08T21:12:03+00:00","article_modified_time":"2013-02-07T13:16:04+00:00","author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost\/","name":"Row and Page Count Influence on Estimated CPU and I\/O Cost - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2013-01-08T21:12:03+00:00","dateModified":"2013-02-07T13:16:04+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Performance","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/performance\/"},{"@type":"ListItem","position":3,"name":"Row and Page Count Influence on Estimated CPU and I\/O Cost"}]},{"@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\/815","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=815"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/815\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=815"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=815"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=815"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}