{"id":4732,"date":"2017-06-06T08:58:58","date_gmt":"2017-06-06T15:58:58","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4732"},"modified":"2019-01-04T11:08:46","modified_gmt":"2019-01-04T19:08:46","slug":"increased-sos_scheduler_yield-waits-on-virtual-machines","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/increased-sos_scheduler_yield-waits-on-virtual-machines\/","title":{"rendered":"Increased SOS_SCHEDULER_YIELD waits on virtual machines"},"content":{"rendered":"<p style=\"text-align: justify;\">A few months ago while I was teaching wait statistics, I was asked whether there\u2019s any expected differences with waits stats when SQL Server is running in a virtual machine.<\/p>\n<p style=\"text-align: justify;\">My answer was yes \u2013 there\u2019s a possibility of seeing longer wait times if something prevents the VM from running, as the wait times are based on the <em><a href=\"https:\/\/docs.microsoft.com\/en-us\/cpp\/intrinsics\/rdtsc\" target=\"_blank\" rel=\"noopener noreferrer\">__rdtsc<\/a><\/em> counter difference (essentially the processor clock tick count) between the wait starting and ending.<\/p>\n<p style=\"text-align: justify;\">In VMware or Hyper-V, if a thread inside of SQLOS is waiting for a resource, and the VM has to wait to be scheduled to execute by the hypervisor due to the host being oversubscribed with vCPUs based on the hardware pCPUs, then the actual resource wait time noted in SQL Server will include that time that the VM was unable to run, and so the wait time will appear to be longer than it would have been had the VM not been delayed.<\/p>\n<p style=\"text-align: justify;\">It\u2019s an interesting discussion on whether this is problematic or not, but my view is that it could lead to someone chasing a SQL Server performance problem that\u2019s actually a VM performance problem. Note: this isn\u2019t a problem with the hypervisor, this is because of a misconfiguration of the virtual environment.<\/p>\n<p style=\"text-align: justify;\">Anyway, after the class I got to thinking about thread scheduling in general on a VM that is periodically delayed from running and whether it could cause any other interesting effects around wait statistics.<\/p>\n<p style=\"text-align: justify;\">Specifically, I was concerned about <em>SOS_SCHEDULER_YIELD<\/em> waits. This is a special wait type that occurs when a thread is able to run for 4ms of CPU time (called the <em>thread quantum<\/em>) without needing to get suspended waiting for an unavailable resource. In a nutshell, a thread must call into the SQLOS layer every so often to see whether it has exhausted its thread quantum, and if so it must voluntarily yield the processor. When that happens, a context switch occurs, and so a wait type must be registered: <em>SOS_SCHEDULER_YIELD<\/em>. A deeper explanation of this wait type is in my waits library <a href=\"https:\/\/www.sqlskills.com\/help\/waits\/sos_scheduler_yield\/\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>.<\/p>\n<p style=\"text-align: justify;\">My theory was this: if a VM is prevented from running for a few milliseconds or more, that could mean that a thread that\u2019s executing might exhaust its thread quantum without actually getting 4ms of CPU time, and so yield the processor causing an <em>SOS_SCHEDULER_YIELD<\/em> wait to be registered. If this happened a lot, it could produce a set of wait statistics for a virtualized workload that appears to have lots of <em>SOS_SCHEDULER_YIELD<\/em>s, when in fact it\u2019s actually a VM performance problem and the <em>SOS_SCHEDULER_YIELD<\/em> waits are really \u2018fake\u2019.<\/p>\n<p style=\"text-align: justify;\">I discussed this with my good friend Bob Ward from the SQL Product Group and after some internal discussions, they concurred that it\u2019s a possibility because the thread quantum exhaustion time is calculated using the <em>__rdtsc<\/em> intrinsic when the thread starts executing, so any delay in the VM running could produce the effect I proposed.<\/p>\n<p style=\"text-align: justify;\">Given that I\u2019m a virtual machine neophyte, I asked Jonathan to run some tests inside of our VMware lab environment to see if he could show the issue happening. He ran a known workload that we use in our Immersion Events to demonstrate the performance impact of host oversubscription, causing a VM to be delayed, and lo and behold, he saw a substantially elevated level of <em>SOS_SCHEDULER_YIELD<\/em> waits (around 20x more) for the\u00a0workload, compared to running the same workload on the same VM without any delays.<\/p>\n<p style=\"text-align: justify;\">These same tests were repeated in our Hyper-V lab environment that is identical in hardware and VM configuration to the VMware environment and similar levels of elevated <em>SOS_SCHEDULER_YIELD<\/em> waits were also seen, so the issue is definitely not specific to any given hypervisor or virtual platform, it\u2019s purely related to the host being oversubscribed for the workloads being run and the SQL Server VM having to wait for CPU resources to continue execution.<\/p>\n<p style=\"text-align: justify;\">I\u2019m deliberately not presenting Jonathan\u2019s test results here because I\u2019m not qualified to explain VMware esxtop output or Hyper-V performance counter values and how they correlate to the <em>SOS_SCHEDULER_YIELD<\/em> numbers to show the problem occurring. Jonathan will do a follow-up post in the next week or two that explains the results from a virtualization perspective.<\/p>\n<p style=\"text-align: justify;\">However, with a simple set of tests we were able to show that with a VM that gets delayed from running, a SQL Server workload can show a much higher level of <em>SOS_SCHEDULER_YIELD<\/em> waits because of the use of the <em>__rdtsc<\/em> intrinsic to calculate thread quantum exhaustion times.<\/p>\n<p style=\"text-align: justify;\">This is really interesting because this is a VM performance issue *causing* a wait type to appear, not just causing waits to be longer.<\/p>\n<p style=\"text-align: justify;\">You should definitely consider this phenomenon if you\u2019re investigating a high number of <em>SOS_SCHEDULER_YIELD<\/em> waits, a workload performance problem, and your workload is running in a VM. Jonathan explains how to correlate these waits with signs of a VM performance problem in <a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/cpu-ready-impact-on-sos_scheduler_yield\/\" target=\"_blank\" rel=\"noopener noreferrer\">this post<\/a>.<\/p>\n<p style=\"text-align: justify;\">Hope this helps!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A few months ago while I was teaching wait statistics, I was asked whether there\u2019s any expected differences with waits stats when SQL Server is running in a virtual machine. My answer was yes \u2013 there\u2019s a possibility of seeing longer wait times if something prevents the VM from running, as the wait times are [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66,101],"tags":[],"class_list":["post-4732","post","type-post","status-publish","format-standard","hentry","category-performance-tuning","category-wait-stats"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Increased SOS_SCHEDULER_YIELD waits on virtual machines - Paul S. Randal<\/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\/paul\/increased-sos_scheduler_yield-waits-on-virtual-machines\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Increased SOS_SCHEDULER_YIELD waits on virtual machines - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"A few months ago while I was teaching wait statistics, I was asked whether there\u2019s any expected differences with waits stats when SQL Server is running in a virtual machine. My answer was yes \u2013 there\u2019s a possibility of seeing longer wait times if something prevents the VM from running, as the wait times are [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/increased-sos_scheduler_yield-waits-on-virtual-machines\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2017-06-06T15:58:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-01-04T19:08:46+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/increased-sos_scheduler_yield-waits-on-virtual-machines\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/increased-sos_scheduler_yield-waits-on-virtual-machines\/\",\"name\":\"Increased SOS_SCHEDULER_YIELD waits on virtual machines - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2017-06-06T15:58:58+00:00\",\"dateModified\":\"2019-01-04T19:08:46+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/increased-sos_scheduler_yield-waits-on-virtual-machines\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/increased-sos_scheduler_yield-waits-on-virtual-machines\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/increased-sos_scheduler_yield-waits-on-virtual-machines\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Increased SOS_SCHEDULER_YIELD waits on virtual machines\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Increased SOS_SCHEDULER_YIELD waits on virtual machines - Paul S. Randal","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\/paul\/increased-sos_scheduler_yield-waits-on-virtual-machines\/","og_locale":"en_US","og_type":"article","og_title":"Increased SOS_SCHEDULER_YIELD waits on virtual machines - Paul S. Randal","og_description":"A few months ago while I was teaching wait statistics, I was asked whether there\u2019s any expected differences with waits stats when SQL Server is running in a virtual machine. My answer was yes \u2013 there\u2019s a possibility of seeing longer wait times if something prevents the VM from running, as the wait times are [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/increased-sos_scheduler_yield-waits-on-virtual-machines\/","og_site_name":"Paul S. Randal","article_published_time":"2017-06-06T15:58:58+00:00","article_modified_time":"2019-01-04T19:08:46+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/increased-sos_scheduler_yield-waits-on-virtual-machines\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/increased-sos_scheduler_yield-waits-on-virtual-machines\/","name":"Increased SOS_SCHEDULER_YIELD waits on virtual machines - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2017-06-06T15:58:58+00:00","dateModified":"2019-01-04T19:08:46+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/increased-sos_scheduler_yield-waits-on-virtual-machines\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/increased-sos_scheduler_yield-waits-on-virtual-machines\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/increased-sos_scheduler_yield-waits-on-virtual-machines\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Increased SOS_SCHEDULER_YIELD waits on virtual machines"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4732","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4732"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4732\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4732"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4732"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4732"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}