{"id":1113,"date":"2020-05-01T06:00:30","date_gmt":"2020-05-01T13:00:30","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=1113"},"modified":"2020-05-04T09:04:19","modified_gmt":"2020-05-04T16:04:19","slug":"troubleshooting-performance-in-azure-sql-database","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/troubleshooting-performance-in-azure-sql-database\/","title":{"rendered":"Troubleshooting Performance in Azure SQL"},"content":{"rendered":"

At some point, whether you\u2019re a DBA, developer, or application administrator, you\u2019re going to find yourself troubleshooting performance in Azure.\u00a0 Within the Azure Portal<\/a> you have the capability to look at performance for an Azure SQL via the Performance overview and Query Performance insight pages.<\/p>\n

\"Checking<\/a>
Checking Performance in the Azure Portal<\/figcaption><\/figure>\n

If you haven\u2019t worked with SQL Server previously, this is a great place to start as it provides a high level view of system performance, broken out by DTUs, CPU, and data and log I\/O.\u00a0 You can dig into specific queries that might be causing issues through Query Performance Insight, including query text and resource use over time.<\/p>\n

But if you\u2019re familiar with troubleshooting in SQL Server, this method feels different, and slower.\u00a0 Not to worry, many of the tools you\u2019re familiar with still work in Azure SQL, you just need a few tweaks.<\/p>\n

Wait Statistics<\/h2>\n

Whenever I\u2019m dropping into a server \u2013 whether it\u2019s one I\u2019m familiar with or not \u2013 to look at a performance issue, I want to understand wait statistics.\u00a0 Specifically, I want to know what wait statistics while the problem is occurring.\u00a0 For this reason, I don\u2019t just query sys.dm_os_wait_stats, I snapshot it, using Paul\u2019s script from his post Capturing wait statistics for a period of time<\/a>.<\/p>\n

You can use this as-is in Azure SQL, but<\/em>, since it queries sys.dm_os_wait_ stats, you\u2019re not looking at wait statistics specific to your<\/em> database, which is really what you care about.\u00a0 So you have to change sys.dm_os_wait_ stats to sys.dm_db_wait_stats in the script.\u00a0 Once you make that switch, you\u2019re good to go \u2013 although depending on the issue you\u2019re dealing with you may change the delay between snapshots to something lower than 30 minutes, which is what Paul has in the script.\u00a0 I sample for either 5 and 10 minutes, depending on what else I want to check.\u00a0 This also assumes the problem is occurring WHILE I am capturing wait statistics.<\/p>\n

WhoIsActive<\/h2>\n

I think I\u2019ve bought Adam Machanic drinks a few times, but at this point I feel like I owe him several dinners at high end restaurants.\u00a0 While I\u2019m waiting for the wait statistics snapshot to complete, I like to run WhoIsActive.\u00a0 You want to grab that from his blog<\/a>, rather than GitHub.\u00a0 If you read the post, there was an issue with the original version of the script, fixed here in v2.\u00a0 Note that when you create the stored procedure you want to do that in your user database.\u00a0 It will fail if you try to create it in master.\u00a0 Once it\u2019s created, you can use all your favorite parameters for execution:<\/p>\n

EXEC sp_WhoIsActive; \/* default, what’s running right now *\/
\nGO<\/p>\n

EXEC sp_WhoIsActive @find_block_leaders = 1; \/* list output with lead blocking sessions at the top *\/
\nGO<\/p>\n

EXEC sp_WhoIsActive @get_plans = 1; \/* pull execution plans *\/<\/p>\n

If you haven\u2019t worked with WhoIsActive much, I\u2019ll refer to you the documentation<\/a>.\u00a0 Between wait statistics and WhoIsActive, I usually get an idea of where to look next.\u00a0 And this is where the next script comes in handy\u2026<\/p>\n

SQL Server Diagnostic Queries<\/h2>\n

You can get the current version of Glenn\u2019s Diagnostic Queries for each release on his Resources<\/a> page.\u00a0 Note that Azure SQL and Managed Instances have separate scripts.\u00a0 With the Diagnostics Queries in hand, I can dig deeper into what I think might be the issue, or I can just start rolling through the queries in order to check configuration and performance data.\u00a0 I may end up on some tangents, depending on what I find, but these three scripts together provide a great starting point when troubleshooting performance in Azure, whether I have a complaint that the system is slow overall, or I\u2019m told that a particular set of queries is slow. Happy troubleshooting!<\/p>\n","protected":false},"excerpt":{"rendered":"

At some point, whether you\u2019re a DBA, developer, or application administrator, you\u2019re going to find yourself troubleshooting performance in Azure.\u00a0 Within the Azure Portal you have the capability to look at performance for an Azure SQL via the Performance overview and Query Performance insight pages. If you haven\u2019t worked with SQL Server previously, this is […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[54,15,18],"tags":[],"yoast_head":"\nTroubleshooting Performance in Azure SQL - Erin Stellato<\/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\/erin\/troubleshooting-performance-in-azure-sql-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Troubleshooting Performance in Azure SQL - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"At some point, whether you\u2019re a DBA, developer, or application administrator, you\u2019re going to find yourself troubleshooting performance in Azure.\u00a0 Within the Azure Portal you have the capability to look at performance for an Azure SQL via the Performance overview and Query Performance insight pages. If you haven\u2019t worked with SQL Server previously, this is […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/troubleshooting-performance-in-azure-sql-database\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2020-05-01T13:00:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-05-04T16:04:19+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/04\/AzurePerformance.jpg\" \/>\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\/troubleshooting-performance-in-azure-sql-database\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/troubleshooting-performance-in-azure-sql-database\/\",\"name\":\"Troubleshooting Performance in Azure SQL - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2020-05-01T13:00:30+00:00\",\"dateModified\":\"2020-05-04T16:04:19+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/troubleshooting-performance-in-azure-sql-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/troubleshooting-performance-in-azure-sql-database\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/troubleshooting-performance-in-azure-sql-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Troubleshooting Performance in Azure SQL\"}]},{\"@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":"Troubleshooting Performance in Azure SQL - Erin Stellato","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\/troubleshooting-performance-in-azure-sql-database\/","og_locale":"en_US","og_type":"article","og_title":"Troubleshooting Performance in Azure SQL - Erin Stellato","og_description":"At some point, whether you\u2019re a DBA, developer, or application administrator, you\u2019re going to find yourself troubleshooting performance in Azure.\u00a0 Within the Azure Portal you have the capability to look at performance for an Azure SQL via the Performance overview and Query Performance insight pages. If you haven\u2019t worked with SQL Server previously, this is […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/troubleshooting-performance-in-azure-sql-database\/","og_site_name":"Erin Stellato","article_published_time":"2020-05-01T13:00:30+00:00","article_modified_time":"2020-05-04T16:04:19+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/04\/AzurePerformance.jpg"}],"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\/troubleshooting-performance-in-azure-sql-database\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/troubleshooting-performance-in-azure-sql-database\/","name":"Troubleshooting Performance in Azure SQL - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2020-05-01T13:00:30+00:00","dateModified":"2020-05-04T16:04:19+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/troubleshooting-performance-in-azure-sql-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/troubleshooting-performance-in-azure-sql-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/troubleshooting-performance-in-azure-sql-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Troubleshooting Performance in Azure SQL"}]},{"@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\/1113"}],"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=1113"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1113\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=1113"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=1113"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=1113"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}