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":875,"date":"2013-01-15T15:16:21","date_gmt":"2013-01-15T23:16:21","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=875"},"modified":"2013-02-07T05:10:03","modified_gmt":"2013-02-07T13:10:03","slug":"auto-update-stats-default-sampling-test","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/auto-update-stats-default-sampling-test\/","title":{"rendered":"Auto-Update Stats Default Sampling Test"},"content":{"rendered":"

In this post I\u2019ll just share the results of a simple set of tests regarding automatic-update sampling.\u00a0 Nothing fancy \u2013 I just populated the category table from the Credit sample database with varying row counts and then took note of the associated duration and sampling percent.\u00a0 The query I used to kick of auto-updates after the row inserts was the following:<\/p>\n

    \r\nSELECT\u00a0 [category_no],\r\n[category_desc],\r\n[category_code]\r\nFROM [dbo].[category]\r\nWHERE [category_desc] = 'Meals'\r\nOPTION (RECOMPILE);\r\n<\/pre>\n
I also show the StatMan MAXDOP just for kicks (once true sampling kicks in, it is set to MAXDOP \u201c1\u201d, so not terribly interesting). I executed a few test iterations beyond what I\u2019m showing here, but I just include two test runs per scenario just for comparison:<\/p>\n\n\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Test ID<\/strong><\/span><\/span><\/td>\nRow Count<\/strong><\/span><\/span><\/td>\nData Pages<\/strong><\/span><\/span><\/td>\nStatMan Duration (ms)<\/strong><\/span><\/span><\/td>\nTABLESAMPLE PERCENT<\/strong><\/span><\/span><\/td>\nMAXDOP<\/strong><\/span><\/span><\/td>\n<\/tr>\n
1<\/span><\/span><\/td>\n10<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n0<\/span><\/span><\/td>\n100.0000<\/span><\/span><\/td>\n16<\/span><\/span><\/td>\n<\/tr>\n
2<\/span><\/span><\/td>\n10<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n0<\/span><\/span><\/td>\n100.0000<\/span><\/span><\/td>\n16<\/span><\/span><\/td>\n<\/tr>\n
1<\/span><\/span><\/td>\n1,000,000<\/span><\/span><\/td>\n3,228<\/span><\/span><\/td>\n1228<\/span><\/span><\/td>\n34.6964<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n<\/tr>\n
2<\/span><\/span><\/td>\n1,000,000<\/span><\/span><\/td>\n3,228<\/span><\/span><\/td>\n1184<\/span><\/span><\/td>\n34.6964<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n<\/tr>\n
1<\/span><\/span><\/td>\n2,000,000<\/span><\/span><\/td>\n6,455<\/span><\/span><\/td>\n1297<\/span><\/span><\/td>\n18.0480<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n<\/tr>\n
2<\/span><\/span><\/td>\n2,000,000<\/span><\/span><\/td>\n6,455<\/span><\/span><\/td>\n1248<\/span><\/span><\/td>\n18.0480<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n<\/tr>\n
1<\/span><\/span><\/td>\n10,000,000<\/span><\/span><\/td>\n32,275<\/span><\/span><\/td>\n1287<\/span><\/span><\/td>\n4.2324<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n<\/tr>\n
2<\/span><\/span><\/td>\n10,000,000<\/span><\/span><\/td>\n32,275<\/span><\/span><\/td>\n1171<\/span><\/span><\/td>\n4.2324<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n<\/tr>\n
1<\/span><\/span><\/td>\n20,000,000<\/span><\/span><\/td>\n64,550<\/span><\/span><\/td>\n1481<\/span><\/span><\/td>\n2.3625<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n<\/tr>\n
2<\/span><\/span><\/td>\n20,000,000<\/span><\/span><\/td>\n64,550<\/span><\/span><\/td>\n1489<\/span><\/span><\/td>\n2.3625<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n<\/tr>\n
1<\/span><\/span><\/td>\n40,000,000<\/span><\/span><\/td>\n129,103<\/span><\/span><\/td>\n2071<\/span><\/span><\/td>\n1.3617<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n<\/tr>\n
2<\/span><\/span><\/td>\n40,000,000<\/span><\/span><\/td>\n129,103<\/span><\/span><\/td>\n1890<\/span><\/span><\/td>\n1.3617<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n<\/tr>\n
1<\/span><\/span><\/td>\n80,000,000<\/span><\/span><\/td>\n258,210<\/span><\/span><\/td>\n2487<\/span><\/span><\/td>\n0.8129<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n<\/tr>\n
2<\/span><\/span><\/td>\n80,000,000<\/span><\/span><\/td>\n258,210<\/span><\/span><\/td>\n2301<\/span><\/span><\/td>\n0.8129<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
A few comments:<\/p>\n
    \n
  • Notice the sample percent is identical between identical row\/page tests (each test started with a restore to the same base state)<\/li>\n
  • Notice that as the data pages grow, the duration of the stats update doesn\u2019t grow significantly, and then consider the sample percentage multiplied by the total data page value (for example – ~1757 data pages for 40M vs. ~2098 data pages for 80M)<\/li>\n
  • Duration is just based on my laptop and I was more interested in the relative duration across test runs<\/li>\n<\/ul>\n
    Now what happens if I rebuild the index with a low fill factor? (Just doing this on the 80M row version):<\/p>\n
        \r\n\r\nUSE [Credit]\r\nGO\r\nALTER INDEX [category_ident] ON [dbo].[category]\r\nREBUILD PARTITION = ALL\r\nWITH (FILLFACTOR = 10);\r\nGO\r\n<\/pre>\n
    I\u2019ll do a non-updating update so that I can get the threshold for the column updates incremented and thus kick off an auto-update of statistics:<\/p>\n
      \r\nUPDATE TOP (16000500) [category]\r\nSET [category_desc] = [category_desc];\r\nGO\r\n<\/pre>\n
    Here are the results (Test ID 3 in bold):<\/p>\n\n\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n
    Test ID<\/strong><\/span><\/span><\/td>\nRow Count<\/strong><\/span><\/span><\/td>\nData Pages<\/strong><\/span><\/span><\/td>\nStatMan Duration (ms)<\/strong><\/span><\/span><\/td>\nTABLESAMPLE PERCENT<\/strong><\/span><\/span><\/td>\nMAXDOP<\/strong><\/span><\/span><\/td>\n<\/tr>\n
    1<\/span><\/span><\/td>\n80,000,000<\/span><\/span><\/td>\n258,210<\/span><\/span><\/td>\n2487<\/span><\/span><\/td>\n0.8129<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n<\/tr>\n
    2<\/span><\/span><\/td>\n80,000,000<\/span><\/span><\/td>\n258,210<\/span><\/span><\/td>\n2301<\/span><\/span><\/td>\n0.8129<\/span><\/span><\/td>\n1<\/span><\/span><\/td>\n<\/tr>\n
    3<\/strong><\/span><\/span><\/td>\n80,000,000<\/strong><\/span><\/span><\/td>\n2,543,516<\/strong><\/span><\/span><\/td>\n1399<\/strong><\/span><\/span><\/td>\n0.4568<\/strong><\/span><\/span><\/td>\n1<\/strong><\/span><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Same row count, significantly more pages, lower duration (interesting) and a reduced table sample percent \u2013 but if you do the math, more pages than the previous samplings. Lots of other fun things we could try, but that\u2019s all for now\u2026<\/p>\n","protected":false},"excerpt":{"rendered":"
    In this post I\u2019ll just share the results of a simple set of tests regarding automatic-update sampling.\u00a0 Nothing fancy \u2013 I just populated the category table from the Credit sample database with varying row counts and then took note of the associated duration and sampling percent.\u00a0 The query I used to kick of auto-updates after […]<\/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-875","post","type-post","status-publish","format-standard","hentry","category-performance"],"yoast_head":"\nAuto-Update Stats Default Sampling Test - Joe Sack<\/title>\n<meta name=\"description\" content=\"In this post I\u2019ll just share the results of a simple set of tests regarding automatic-update sampling.\" \/>\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\/auto-update-stats-default-sampling-test\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Auto-Update Stats Default Sampling Test - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"In this post I\u2019ll just share the results of a simple set of tests regarding automatic-update sampling.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/auto-update-stats-default-sampling-test\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2013-01-15T23:16:21+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-02-07T13:10:03+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=\"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\/auto-update-stats-default-sampling-test\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/auto-update-stats-default-sampling-test\/\",\"name\":\"Auto-Update Stats Default Sampling Test - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2013-01-15T23:16:21+00:00\",\"dateModified\":\"2013-02-07T13:10:03+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"description\":\"In this post I\u2019ll just share the results of a simple set of tests regarding automatic-update sampling.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/auto-update-stats-default-sampling-test\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/auto-update-stats-default-sampling-test\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/auto-update-stats-default-sampling-test\/#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\":\"Auto-Update Stats Default Sampling Test\"}]},{\"@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":"Auto-Update Stats Default Sampling Test - Joe Sack","description":"In this post I\u2019ll just share the results of a simple set of tests regarding automatic-update sampling.","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\/auto-update-stats-default-sampling-test\/","og_locale":"en_US","og_type":"article","og_title":"Auto-Update Stats Default Sampling Test - Joe Sack","og_description":"In this post I\u2019ll just share the results of a simple set of tests regarding automatic-update sampling.","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/auto-update-stats-default-sampling-test\/","og_site_name":"Joe Sack","article_published_time":"2013-01-15T23:16:21+00:00","article_modified_time":"2013-02-07T13:10:03+00:00","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\/auto-update-stats-default-sampling-test\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/auto-update-stats-default-sampling-test\/","name":"Auto-Update Stats Default Sampling Test - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2013-01-15T23:16:21+00:00","dateModified":"2013-02-07T13:10:03+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"description":"In this post I\u2019ll just share the results of a simple set of tests regarding automatic-update sampling.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/auto-update-stats-default-sampling-test\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/auto-update-stats-default-sampling-test\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/auto-update-stats-default-sampling-test\/#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":"Auto-Update Stats Default Sampling Test"}]},{"@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\/875","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=875"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/875\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=875"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}