{"id":640,"date":"2014-01-23T09:32:45","date_gmt":"2014-01-23T17:32:45","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=640"},"modified":"2018-12-14T09:07:14","modified_gmt":"2018-12-14T17:07:14","slug":"statistics-starters-presentation-scripts-the-database-and-some-answers","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/statistics-starters-presentation-scripts-the-database-and-some-answers\/","title":{"rendered":"Statistics Starters Presentation: Scripts, the Database, and Some Answers"},"content":{"rendered":"

This past Tuesday I presented \u201cStatistics Starters\u201d for the PASS DBA Fundamentals Virtual Chapter<\/a>.\u00a0 You can read the abstract here<\/a>, and as you may have guessed from the title, it was a 200 level session on statistics appropriate for anyone who knew of<\/i> statistics in SQL Server, but wasn\u2019t exactly sure how they were created, how they were updated, how to view them, etc.\u00a0 Over 300 people attended (thank you!) and I had some great questions.\u00a0 I plan to answer the questions in a series of posts, starting with this one.<\/p>\n

Question:<\/strong> Can we get a copy of the scripts in your demo?\u00a0 And where can we get a copy of the database you used?<\/p>\n

Answer:<\/strong> The scripts, slide deck, and database can be downloaded from the SQLskills demos and databases resource page<\/a>.\u00a0 The database I used for these demos, which I plan to continue to use for presentations, is the Lahman baseball database<\/a>.\u00a0 While the AdventureWorks database is well known and widely-used, I admit that I have a hard time thinking of good Sales and Product examples in my demos.\u00a0 I know baseball a lot better than I know sales \ud83d\ude42<\/p>\n

Question:<\/strong> Are we able to rollback newly created statistics if the plans created after an update are bad?<\/p>\n

Answer:<\/strong> (edited 2014-01-23 2:45 pm)<\/em> Great question.\u00a0 The answer is no<\/del> kind of.\u00a0 This is one feature that exists in Oracle that I would be interested in seeing in SQL Server.\u00a0 Oracle provides the ability to save and restore statistics.\u00a0 You can even export statistics from one database and import them into another.\u00a0 Pretty cool\u2026potentially dangerous, but still cool; however, it is not<\/del> possible to restore statistics in SQL Server if you save out the stat stream first, and then update the statistic with the stream.\u00a0 Thanks to my colleague Bob Beauchemin (b<\/a>) for pointing out how it can be done (I learn something new every day<\/strong>).\u00a0 Johan Bijnens<\/a> also messaged me to point out that you can script out statistics – which I always forget.\u00a0 The next step is to update statistics with stats_stream that you script out.\u00a0 Take note: it is a hack.\u00a0 Thomas Kejser blogged the steps here<\/a>, and he has a fantastic disclaimer at the beginning because the method described is unsupported<\/strong>.\u00a0 Before I write any more about the “feature”, I’m going to do a little testing and hacking of my own.\u00a0 More to come!<\/p>\n

Question:<\/strong> Why should I use the UPDATE STATISTICS command\u2026isn\u2019t sp_updatestats always the best option?<\/p>\n

Answer:<\/strong> See my post Understanding What sp_updatestats Really Updates<\/a> to see why I don\u2019t recommend using sp_updatestats.<\/p>\n

Question:<\/strong> Is it good to update statistics after rebuilding an index?<\/p>\n

Answer:<\/strong> This is not recommended.\u00a0 Remember that rebuilding an index updates statistics with a full scan \u2013 if you run a command to update statistics after a rebuild, you are wasting resources and the statistics may update with a smaller sample.\u00a0\u00a0 This is sometimes not ideal, because depending on the sample, it can provide less accurate information to the optimizer (not always, but it’s possible).<\/p>\n

Question:<\/strong> Is it good practice to update statistics if I reorganize the index?<\/p>\n

Answer:<\/strong> In general, yes, because reorganizing an index does<\/em> not<\/i> update statistics.\u00a0 I recommend that you pro-actively manage your statistics, and not rely solely on automatic updates (assuming you have the AUTO UPDATE STATISTICS option enabled for your database).\u00a0 If you are only reorganizing your indexes, make sure that you have another step or job that does update statistics.\u00a0 If you either rebuild or reorg (or do nothing) based on the level of fragmentation, then you need to make sure you manage statistics accordingly (e.g., don\u2019t update if a rebuild has occurred, do update if you\u2019ve reorganized).<\/p>\n

I\u2019ll answer a few more questions in my next post, thanks for reading!<\/p>\n","protected":false},"excerpt":{"rendered":"

This past Tuesday I presented \u201cStatistics Starters\u201d for the PASS DBA Fundamentals Virtual Chapter.\u00a0 You can read the abstract here, and as you may have guessed from the title, it was a 200 level session on statistics appropriate for anyone who knew of statistics in SQL Server, but wasn\u2019t exactly sure how they were created, […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[38],"yoast_head":"\nStatistics Starters Presentation: Scripts, the Database, and Some Answers - Erin Stellato<\/title>\n<meta name=\"description\" content=\"In this post I provide resources for my SQL Server Statistics Starters presentation, as well as answers to a few questions from the session.\" \/>\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\/erin\/statistics-starters-presentation-scripts-the-database-and-some-answers\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Statistics Starters Presentation: Scripts, the Database, and Some Answers - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"In this post I provide resources for my SQL Server Statistics Starters presentation, as well as answers to a few questions from the session.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/statistics-starters-presentation-scripts-the-database-and-some-answers\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-23T17:32:45+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-12-14T17:07:14+00:00\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\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\/erin\/statistics-starters-presentation-scripts-the-database-and-some-answers\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/statistics-starters-presentation-scripts-the-database-and-some-answers\/\",\"name\":\"Statistics Starters Presentation: Scripts, the Database, and Some Answers - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2014-01-23T17:32:45+00:00\",\"dateModified\":\"2018-12-14T17:07:14+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"In this post I provide resources for my SQL Server Statistics Starters presentation, as well as answers to a few questions from the session.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/statistics-starters-presentation-scripts-the-database-and-some-answers\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/statistics-starters-presentation-scripts-the-database-and-some-answers\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/statistics-starters-presentation-scripts-the-database-and-some-answers\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Statistics Starters Presentation: Scripts, the Database, and Some Answers\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Statistics Starters Presentation: Scripts, the Database, and Some Answers - Erin Stellato","description":"In this post I provide resources for my SQL Server Statistics Starters presentation, as well as answers to a few questions from the session.","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\/erin\/statistics-starters-presentation-scripts-the-database-and-some-answers\/","og_locale":"en_US","og_type":"article","og_title":"Statistics Starters Presentation: Scripts, the Database, and Some Answers - Erin Stellato","og_description":"In this post I provide resources for my SQL Server Statistics Starters presentation, as well as answers to a few questions from the session.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/statistics-starters-presentation-scripts-the-database-and-some-answers\/","og_site_name":"Erin Stellato","article_published_time":"2014-01-23T17:32:45+00:00","article_modified_time":"2018-12-14T17:07:14+00:00","author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/statistics-starters-presentation-scripts-the-database-and-some-answers\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/statistics-starters-presentation-scripts-the-database-and-some-answers\/","name":"Statistics Starters Presentation: Scripts, the Database, and Some Answers - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2014-01-23T17:32:45+00:00","dateModified":"2018-12-14T17:07:14+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"In this post I provide resources for my SQL Server Statistics Starters presentation, as well as answers to a few questions from the session.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/statistics-starters-presentation-scripts-the-database-and-some-answers\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/statistics-starters-presentation-scripts-the-database-and-some-answers\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/statistics-starters-presentation-scripts-the-database-and-some-answers\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Statistics Starters Presentation: Scripts, the Database, and Some Answers"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/640"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=640"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/640\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=640"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=640"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=640"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}