{"id":2200,"date":"2018-09-06T15:55:22","date_gmt":"2018-09-06T15:55:22","guid":{"rendered":"http:\/\/nenadnoveljic.com\/blog\/?p=2200"},"modified":"2018-09-13T11:51:28","modified_gmt":"2018-09-13T11:51:28","slug":"excessive-context-switching-due-to-tds-encryption","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/excessive-context-switching-due-to-tds-encryption\/","title":{"rendered":"Excessive Context Switching due to TDS Encryption"},"content":{"rendered":"

Tabular Data Stream (TDS) Encryption Mechanics<\/h1>\n

TDS encryption can cause dramatic scalability problems in SQL Server 2014. As we shall see later, those performance bottlenecks don’t arise due to the CPU overhead inherent to encryption. Rather, it’s a consequence of how the task is being scheduled within the SQL Server.<\/p>\n

First, let me summarize how TDS encryption works in SQL Server 2014, as explained here<\/a>. A thread calls a Windows OS kernel function to encrypt a message. As this happens, SQL Server hands over the thread control to Windows. <\/p>\n

This back and forth context switching causes delays in query execution, that are exposed through the wait event PREEMPTIVE_OS_ENCRYPTMESSAGE. <\/p>\n

Fortunately, Microsoft improved the implementation in the SQL Server versions 2016 and 2017 – no context switching is performed in these releases. <\/p>\n

Let me explain the methodology used for reaching the conclusions above.<\/p>\n

Measurement<\/h1>\n

The following SQL batch generates lots of messages. At the end, it collects the following metrics: time elapsed, the number of context switches and PREEMPTIVE_OS_ENCRYPTMESSAGE wait statistics.<\/p>\n

\r\nselect @@spid\r\n\r\nDBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);\r\n\r\nselect @@VERSION\r\n\r\nselect encrypt_option from sys.dm_exec_connections where session_id = @@SPID \r\n\r\nDECLARE @starttime datetime, @endtime datetime\r\nDECLARE @cnt int = 0, @ts_start int ;\r\nDECLARE @context_switches_before int, @context_switches_after int ;\r\n\r\nSET @starttime =getdate() ;\r\nselect @context_switches_before = sum(context_switches_count) \r\n  from sys.dm_os_schedulers ;\r\n\r\n--load  \r\nBEGIN\r\n    WHILE @cnt < 100000000\r\n\tBEGIN\r\n\t   SET @cnt = @cnt + 1;\r\n\tEND;\r\nEND\r\n\r\nselect @context_switches_after = sum(context_switches_count) \r\n  from sys.dm_os_schedulers ;\r\n\r\nselect ( @context_switches_after  -  @context_switches_before ) context_switches ;\r\n\r\nSET @endtime = GETDATE() ;\r\nSELECT Datediff(s, @starttime, @endtime) elapsed_seconds\r\n\r\nselect * from sys.dm_os_wait_stats where wait_type = 'PREEMPTIVE_OS_ENCRYPTMESSAGE' ;\r\n<\/code><\/pre>\n
While the workload is running, we 're collecting the task's CPU time with the following script:<\/p>\n
\r\ndeclare @spid int = 78, @i int = 1, @samples int = 120 ;\r\n\r\nbegin\r\n  while ( @i <= @samples ) \r\n  begin\r\n    select cpu_time,total_elapsed_time, status, wait_type, wait_time, \r\n\t  scheduler_id\r\n\t  from sys.dm_exec_requests r \r\n\t  where session_id = @spid\r\n\twaitfor delay '00:00:01'\r\n\tset @i = @i + 1\r\n  end\r\nend ;\r\n<\/code><\/pre>\n
A side note: it might seem easier - and more intuitive - to setup an Extended Events (XE) trace to collect the CPU time for the SQL batch. However, not only does the XE tracing provide an inaccurate information, but it also introduces a significant overhead, which would skew the test results. I elaborated on that in my previous blog post<\/a>.<\/p>\n
Results<\/h1>\n
I ran the tests on SQL Server 2014 and 2017 using both encrypted and non-encrypted connections. The following table clearly highlights the problem:<\/p>\n\n\n\n\n\t\n\n\t\n\t\n\t\n\t
<\/td>2014 enc<\/th>2014 non-enc<\/th>2017 enc<\/th>2017 non-enc<\/th>\n<\/tr>\n<\/thead>\n
elapsed time<\/td>99690<\/td>62127<\/td>66196<\/td>62442<\/td>\n<\/tr>\n
CPU time<\/td>84490<\/td>60764<\/td>65114<\/td>63165<\/td>\n<\/tr>\n
PREEMPTIVE_OS_ENCRYPTMESSAGE<\/td>28266<\/td>0<\/td>0<\/td>0<\/td>\n<\/tr>\n
context switches
\n<\/td>		1273235
\n<\/td>		1117<\/td>508<\/td>500<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n
Legend:<\/p>\n\n\n\n\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t
2014 enc<\/td>SQL Server 2014 with encrpytion configured<\/td>\n<\/tr>\n
2014 non-enc<\/td>SQL Server 2014 without encrpytion configured<\/td>\n<\/tr>\n
2017 enc<\/td>SQL Server 2017 with encrpytion configured<\/td>\n<\/tr>\n
2017 non-enc<\/td>SQL Server 2017 without encrpytion configured<\/td>\n<\/tr>\n
elapsed time<\/td>elapsed time in ms<\/td>\n<\/tr>\n
CPU time<\/td>the time the SQL batch spent on CPU in ms<\/td>\n<\/tr>\n
PREEMPTIVE_OS_ENCRYPTMESSAGE<\/td>the total time waited on PREEMPTIVE_OS_ENCRYPTMESSAGE for the whole instance during the test in ms<\/td>\n<\/tr>\n
context switches<\/td>the total number of context switches for the whole instance during the test<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n
Summary<\/h1>\n
TDS encryption has a negligible performance impact in SQL Server 2017. In contrast, turning the encryption on can be detrimental to performance in SQL Server 2014, especially when lots of messages are being sent to the clients. This performance decrease isn't caused by the encryption itself - it's rather a consequence of a suboptimal thread handling.<\/p>\n","protected":false},"excerpt":{"rendered":"
TDS encryption can have a dramatic performance impact in SQL Server 2014 because of excessive context switching. The implementation was significantly improved in SQL Server 2016 and 2017. Continue Reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[26,17],"tags":[],"class_list":["post-2200","post","type-post","status-publish","format-standard","hentry","category-encryption","category-sql-server"],"yoast_head":"\nExcessive Context Switching due to TDS Encryption - All-round Database Topics<\/title>\n<meta name=\"description\" content=\"TDS encryption can have a dramatic performance impact in SQL Server 2014 because of excessive context switching. The implementation was significantly improved in SQL Server 2016 and 2017.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/nenadnoveljic.com\/blog\/excessive-context-switching-due-to-tds-encryption\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excessive Context Switching due to TDS Encryption - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"TDS encryption can have a dramatic performance impact in SQL Server 2014 because of excessive context switching. The implementation was significantly improved in SQL Server 2016 and 2017.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/excessive-context-switching-due-to-tds-encryption\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2018-09-06T15:55:22+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-09-13T11:51:28+00:00\" \/>\n<meta name=\"author\" content=\"Nenad Noveljic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@NenadNoveljic\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nenad Noveljic\" \/>\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\":\"Article\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/excessive-context-switching-due-to-tds-encryption\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/excessive-context-switching-due-to-tds-encryption\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"Excessive Context Switching due to TDS Encryption\",\"datePublished\":\"2018-09-06T15:55:22+00:00\",\"dateModified\":\"2018-09-13T11:51:28+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/excessive-context-switching-due-to-tds-encryption\\\/\"},\"wordCount\":340,\"commentCount\":0,\"articleSection\":[\"encryption\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/excessive-context-switching-due-to-tds-encryption\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/excessive-context-switching-due-to-tds-encryption\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/excessive-context-switching-due-to-tds-encryption\\\/\",\"name\":\"Excessive Context Switching due to TDS Encryption - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"datePublished\":\"2018-09-06T15:55:22+00:00\",\"dateModified\":\"2018-09-13T11:51:28+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"description\":\"TDS encryption can have a dramatic performance impact in SQL Server 2014 because of excessive context switching. The implementation was significantly improved in SQL Server 2016 and 2017.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/excessive-context-switching-due-to-tds-encryption\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/excessive-context-switching-due-to-tds-encryption\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/excessive-context-switching-due-to-tds-encryption\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excessive Context Switching due to TDS Encryption\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\",\"name\":\"All-round Database Topics\",\"description\":\"Nenad Noveljic\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\",\"name\":\"Nenad Noveljic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"caption\":\"Nenad Noveljic\"},\"sameAs\":[\"nenad-noveljic-9b746a6\",\"https:\\\/\\\/x.com\\\/NenadNoveljic\"],\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/author\\\/nenad\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excessive Context Switching due to TDS Encryption - All-round Database Topics","description":"TDS encryption can have a dramatic performance impact in SQL Server 2014 because of excessive context switching. The implementation was significantly improved in SQL Server 2016 and 2017.","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:\/\/nenadnoveljic.com\/blog\/excessive-context-switching-due-to-tds-encryption\/","og_locale":"en_US","og_type":"article","og_title":"Excessive Context Switching due to TDS Encryption - All-round Database Topics","og_description":"TDS encryption can have a dramatic performance impact in SQL Server 2014 because of excessive context switching. The implementation was significantly improved in SQL Server 2016 and 2017.","og_url":"https:\/\/nenadnoveljic.com\/blog\/excessive-context-switching-due-to-tds-encryption\/","og_site_name":"All-round Database Topics","article_published_time":"2018-09-06T15:55:22+00:00","article_modified_time":"2018-09-13T11:51:28+00:00","author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/excessive-context-switching-due-to-tds-encryption\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/excessive-context-switching-due-to-tds-encryption\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"Excessive Context Switching due to TDS Encryption","datePublished":"2018-09-06T15:55:22+00:00","dateModified":"2018-09-13T11:51:28+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/excessive-context-switching-due-to-tds-encryption\/"},"wordCount":340,"commentCount":0,"articleSection":["encryption","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/excessive-context-switching-due-to-tds-encryption\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/excessive-context-switching-due-to-tds-encryption\/","url":"https:\/\/nenadnoveljic.com\/blog\/excessive-context-switching-due-to-tds-encryption\/","name":"Excessive Context Switching due to TDS Encryption - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"datePublished":"2018-09-06T15:55:22+00:00","dateModified":"2018-09-13T11:51:28+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"description":"TDS encryption can have a dramatic performance impact in SQL Server 2014 because of excessive context switching. The implementation was significantly improved in SQL Server 2016 and 2017.","breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/excessive-context-switching-due-to-tds-encryption\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/excessive-context-switching-due-to-tds-encryption\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/excessive-context-switching-due-to-tds-encryption\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Excessive Context Switching due to TDS Encryption"}]},{"@type":"WebSite","@id":"https:\/\/nenadnoveljic.com\/blog\/#website","url":"https:\/\/nenadnoveljic.com\/blog\/","name":"All-round Database Topics","description":"Nenad Noveljic","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/nenadnoveljic.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa","name":"Nenad Noveljic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","caption":"Nenad Noveljic"},"sameAs":["nenad-noveljic-9b746a6","https:\/\/x.com\/NenadNoveljic"],"url":"https:\/\/nenadnoveljic.com\/blog\/author\/nenad\/"}]}},"_links":{"self":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2200","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/comments?post=2200"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2200\/revisions"}],"predecessor-version":[{"id":2218,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2200\/revisions\/2218"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=2200"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=2200"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=2200"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}