{"id":967,"date":"2018-10-19T09:51:10","date_gmt":"2018-10-19T16:51:10","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=967"},"modified":"2018-10-19T09:51:10","modified_gmt":"2018-10-19T16:51:10","slug":"baselines-sql-server-azure-sqldb","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/baselines-sql-server-azure-sqldb\/","title":{"rendered":"Baselines for SQL Server and Azure SQL Database"},"content":{"rendered":"<p>Last week I got an email from a community member who had read this <a href=\"http:\/\/www.sqlservercentral.com\/articles\/baselines\/94657\/\">older article of mine on baselining<\/a>, and asked if there were any updates related to SQL Server 2016, SQL Server 2017, or vNext (<a href=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/sql-server-2019\">SQL Server 2019<\/a>). It was a really good question. I haven\u2019t visited that article in a while and so I took the time to re-read it. I\u2019m rather proud to say that what I said then still holds up today.<\/p>\n<p>The fundamentals of baselining are the same as they were back in 2012 when that article was first published. What is different about today? First, there are a lot more metrics in the current release of SQL Server that you can baseline (e.g. more events in Extended Events, new DMVs, new PerfMon counters,\u00a0 sp_server_diagnostics_component_results). Second, options for capturing baselines have changed. In the article I mostly talked about rolling your own scripts for baselining. If you\u2019re looking to establish baselines for your servers you still have the option to develop your own scripts, but you also can use a third-party tool, and if you\u2019re running SQL Server 2016+ or Azure SQL Database, you can use Query Store.<\/p>\n<p>As much as I love Query Store, I admit that it is not all-encompassing in terms of baselining a server. It does not replace a third-party tool, nor does it fully replace rolling your own scripts. Query Store captures metrics specific to query execution, and you\u2019re not familiar with this feature, feel free to <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/category\/query-store\/\">check out my posts<\/a> about it.<\/p>\n<p>Consider this core question: What should we baseline in our SQL Server environment? If you have a third-party tool, the data captured is determined by the application, and some of them allow you to customize and capture additional metrics. But if you roll your own scripts, there are some fundamental things that I think you should capture such as instance configuration, file space and usage information, and wait statistics.<\/p>\n<p>Beyond that, it really goes back to the question of what problem are you trying to solve? If you are looking at implementing In-Memory OLTP, then you want to capture information related to query execution times and frequency, locking, latching, and memory use. After you implement In-Memory OLTP, you look at those exact same metrics and compare the data. If you\u2019re looking at using Columnstore indexes, you need to look at query performance as it stands right now (duration, I\/O, CPU) and capture how it changes after you\u2019ve added one or more Columnstore indexes. But to be really thorough you should also look at index usage for the involved tables, as well as query performance for other queries against those tables to see if and\/or how performance changes after you\u2019ve added the index. Very few things in SQL Server work truly in isolation, they\u2019re all interacting with each other in some way\u2026which is why baselining can be a little bit overwhelming and why I recommend that you start small.<\/p>\n<p>Back to the original question: is there anything new to consider with SQL Server 2016 and higher? While third-party tools continue to improve and more metrics are available as new features are added and SQL Server continues to evolve, the only thing \u201creally new\u201d is the addition of Query Store and its ability to capture query performance metrics natively within SQL Server. Hopefully this helps as you either look at different third-party tools that you may want to purchase, or you look at rolling your own set of scripts.\u00a0 If you&#8217;re interested in writing your own scripts, I have a set of references that might be of use <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/collection-of-baseline-scripts\/\">here<\/a>.<\/p>\n<p>Lastly, you&#8217;ll note that I haven&#8217;t said much about Azure SQL Database, and that&#8217;s because it&#8217;s an entirely different beast.\u00a0 If you have one or more Azure SQL Databases, then you may know that within the Portal there are multiple options for looking at system performance, including <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/sql-database\/sql-database-intelligent-insights\">Intelligent Insights<\/a> and <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/sql-database\/sql-database-query-performance\">Query Performance Insight<\/a>.\u00a0 Theoretically, you could still roll your own scripts in Azure SQL DB, but I would first explore what Microsoft provides to see if it meets your needs.\u00a0 Have fun!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Last week I got an email from a community member who had read this older article of mine on baselining, and asked if there were any updates related to SQL Server 2016, SQL Server 2017, or vNext (SQL Server 2019). It was a really good question. I haven\u2019t visited that article in a while and [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Baselines for SQL Server and Azure SQL Database - Erin Stellato<\/title>\n<meta name=\"description\" content=\"SQL Server baselines continue to be relevant, whether you&#039;re running on-prem or you have an Azure SQL DB, and in this post we&#039;ll cover current options.\" \/>\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\/baselines-sql-server-azure-sqldb\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Baselines for SQL Server and Azure SQL Database - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"SQL Server baselines continue to be relevant, whether you&#039;re running on-prem or you have an Azure SQL DB, and in this post we&#039;ll cover current options.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/baselines-sql-server-azure-sqldb\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2018-10-19T16:51:10+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\/baselines-sql-server-azure-sqldb\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/baselines-sql-server-azure-sqldb\/\",\"name\":\"Baselines for SQL Server and Azure SQL Database - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2018-10-19T16:51:10+00:00\",\"dateModified\":\"2018-10-19T16:51:10+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"SQL Server baselines continue to be relevant, whether you're running on-prem or you have an Azure SQL DB, and in this post we'll cover current options.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/baselines-sql-server-azure-sqldb\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/baselines-sql-server-azure-sqldb\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/baselines-sql-server-azure-sqldb\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Baselines for SQL Server and Azure SQL Database\"}]},{\"@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":"Baselines for SQL Server and Azure SQL Database - Erin Stellato","description":"SQL Server baselines continue to be relevant, whether you're running on-prem or you have an Azure SQL DB, and in this post we'll cover current options.","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\/baselines-sql-server-azure-sqldb\/","og_locale":"en_US","og_type":"article","og_title":"Baselines for SQL Server and Azure SQL Database - Erin Stellato","og_description":"SQL Server baselines continue to be relevant, whether you're running on-prem or you have an Azure SQL DB, and in this post we'll cover current options.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/baselines-sql-server-azure-sqldb\/","og_site_name":"Erin Stellato","article_published_time":"2018-10-19T16:51:10+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\/baselines-sql-server-azure-sqldb\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/baselines-sql-server-azure-sqldb\/","name":"Baselines for SQL Server and Azure SQL Database - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2018-10-19T16:51:10+00:00","dateModified":"2018-10-19T16:51:10+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"SQL Server baselines continue to be relevant, whether you're running on-prem or you have an Azure SQL DB, and in this post we'll cover current options.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/baselines-sql-server-azure-sqldb\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/baselines-sql-server-azure-sqldb\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/baselines-sql-server-azure-sqldb\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Baselines for SQL Server and Azure SQL Database"}]},{"@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\/967"}],"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=967"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/967\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=967"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=967"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=967"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}