{"id":17121,"date":"2022-02-16T15:45:12","date_gmt":"2022-02-16T14:45:12","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\/"},"modified":"2022-02-16T15:45:12","modified_gmt":"2022-02-16T14:45:12","slug":"access-check-results-entries-too-large-resulting-in-a-memory-pressure","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\/","title":{"rendered":"Access Check Results entries too large, resulting in a memory pressure"},"content":{"rendered":"<p>As part of a MSSQL consolidation project, with application servers located in one domain and the databases in another, we received an alert indicating that some databases were not available.<br \/>\nAfter starting the investigation, we discovered that the Availability Group (AAG), to which these databases belong, was temporary taken in RESOLVING_NORMAL:<\/p>\n<pre class=\"brush: text; gutter: true; first-line: 1\">\nThe state of the local availability replica in availability group 'xxxxxxxx has changed from 'PRIMARY_NORMAL' to 'RESOLVING_NORMAL'.  The state changed because the lease between the local availability replica and Windows Server Failover Clustering (WSFC) has expired.\n<\/pre>\n<p>Since this state does not allow databases to accept connections to maintain High Availability consistency, this explains why we received such an alert. In other words, it is the symptom and not the root-cause.<br \/>\nWhen we continue the investigation, the lease has expired because the following major issue has appeared at the instance level:<\/p>\n<pre class=\"brush: text; gutter: true; first-line: 1\">\nStack Signature for the dump is 0x000000000000030A\nProcess 0:0:0 (0x744) Worker 0x0000019C97866160 appears to be non-yielding on Scheduler 15. Thread creation time: 13281025838286. Approx Thread CPU Used: kernel 171 ms, user 35062 ms. Process Utilization 18%. System Idle 47%. Interval: 70427 ms.\nTimeout waiting for external dump process 18424.\n<\/pre>\n<p>In other words, this thread did no respond in time and SQL Server considered it blocked. This explains the previous behavior, and also the generation of a Mini Dump.<\/p>\n<p>For information, the instance is installed in SQL Server 2019 Enterprise and had the latest Cumulative Update when the issue occurred.<br \/>\nA review of the Mini Dump, only the publicly readable portion, did not allow to find the root-cause.<br \/>\nWe also did not find any KBs or opened tickets which could explain the error we were facing. The investigation of other logs (OS, Cluster, XE) did not bring additional clues.<\/p>\n<p>Requiring more information to understand what happened, we opened a ticket at the Microsoft SQL Server Support. We provided the dump file and also relative logs of the incident.<br \/>\nAfter two days, we got an answer from the Support: the Security caches located in the Memory Clerk grow significantly and implied memory pressure. This was due to a high number of distinct Ad Hoc queries running on the consolidated instance.<br \/>\nBut how is it possible?<\/p>\n<p>As a short reminder, Access Check Results (ACRs) are evaluated when database objects are accessed by SQL Server. These results are stored in cache, called <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/database-engine\/configure-windows\/access-check-cache-server-configuration-options?view=sql-server-ver15\" target=\"_blank\" rel=\"noopener noreferrer\">Access Check Cache<\/a>.<br \/>\nTherefore, ACRs can be reused for the same query which allows to not recalculate them each time ; reducing cpu overhead and improving execution time at a cost of some memory.<br \/>\nAs described in this <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/database-engine\/configure-windows\/access-check-cache-server-configuration-options?view=sql-server-ver15\" target=\"_blank\" rel=\"noopener noreferrer\">Microsoft documentation<\/a>, in rare circumstances we may want to either reduce the size of the cache if too much memory is used or increase the size of the cache if a high CPU usage is detected (because of ACRs calculation of course).<\/p>\n<p>In our scenario, the goal was to limit the number of entries in TokenAndPermUserStore to avoid future memory pressures, which implied the Mini Dump.<br \/>\nThat is why the Microsoft Support recommended to either:<br \/>\n&#8211; Apply <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/database-console-commands\/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15\" target=\"_blank\" rel=\"noopener noreferrer\">Trace Flag 4618<\/a> &#8211;  Limits the number of entries in TokenAndPermUserStore cache store to 1024<br \/>\n&#8211; Apply <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/database-console-commands\/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15\" target=\"_blank\" rel=\"noopener noreferrer\">Trace Flag 4618 + Trace Flags 4610<\/a> &#8211; Limits the number of entries in TokenAndPermUserStore cache store to 8192<\/p>\n<p>We decided to apply Trace Flags 4618 and 4610 to set the TokenAndPermUserStore cache store to 8192 entries. Of course these changes had to be applied on all replicas which belong to the same AAG.<br \/>\nTo apply changes dynamically without restarting the instance, we used <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/database-console-commands\/dbcc-traceon-transact-sql?view=sql-server-ver15\" target=\"_blank\" rel=\"noopener noreferrer\">DBCC TRACEON<\/a>. To persist the change, we added the trace flags in Startup Parameters of the service.<br \/>\nFor more information, please refer to the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/database-console-commands\/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15\" target=\"_blank\" rel=\"noopener noreferrer\">Microsoft Documentation<\/a>.<\/p>\n<p>Regarding the detection of the Mini Dump, we were quite lucky the first time because we were alerted through the availability of the databases. But this was because the databases were members of an AAG and primary on the node. In other words, we were informed indirectly about the generation of the dump.<br \/>\nToday, to be informed directly, we are using the PowerShell module <a href=\"https:\/\/dbachecks.readthedocs.io\/\" target=\"_blank\" rel=\"noopener noreferrer\">dbachecks<\/a> to detect new SQL dumps. This module offers plenty of other relevant checks, that is why I highly recommend to check it out!<\/p>\n<p>So far, we did not notice new dumps and we consider this topic as solved. Hoping this blog will help you!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>As part of a MSSQL consolidation project, with application servers located in one domain and the databases in another, we received an alert indicating that some databases were not available. After starting the investigation, we discovered that the Availability Group (AAG), to which these databases belong, was temporary taken in RESOLVING_NORMAL: The state of the [&hellip;]<\/p>\n","protected":false},"author":14,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[51],"type_dbi":[],"class_list":["post-17121","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-sql-server"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Access Check Results entries too large, resulting in a memory pressure - dbi Blog<\/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.dbi-services.com\/blog\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Access Check Results entries too large, resulting in a memory pressure\" \/>\n<meta property=\"og:description\" content=\"As part of a MSSQL consolidation project, with application servers located in one domain and the databases in another, we received an alert indicating that some databases were not available. After starting the investigation, we discovered that the Availability Group (AAG), to which these databases belong, was temporary taken in RESOLVING_NORMAL: The state of the [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-02-16T14:45:12+00:00\" \/>\n<meta name=\"author\" content=\"Nathan Courtine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nathan Courtine\" \/>\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\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\\\/\"},\"author\":{\"name\":\"Nathan Courtine\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/38305b5ebdcdb4fb784fa31d760862d1\"},\"headline\":\"Access Check Results entries too large, resulting in a memory pressure\",\"datePublished\":\"2022-02-16T14:45:12+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\\\/\"},\"wordCount\":636,\"commentCount\":0,\"keywords\":[\"SQL Server\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\\\/\",\"name\":\"Access Check Results entries too large, resulting in a memory pressure - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2022-02-16T14:45:12+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/38305b5ebdcdb4fb784fa31d760862d1\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Access Check Results entries too large, resulting in a memory pressure\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/38305b5ebdcdb4fb784fa31d760862d1\",\"name\":\"Nathan Courtine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"caption\":\"Nathan Courtine\"},\"description\":\"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \\\/ Banking, etc.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/nathan-courtine\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Access Check Results entries too large, resulting in a memory pressure - dbi Blog","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.dbi-services.com\/blog\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\/","og_locale":"en_US","og_type":"article","og_title":"Access Check Results entries too large, resulting in a memory pressure","og_description":"As part of a MSSQL consolidation project, with application servers located in one domain and the databases in another, we received an alert indicating that some databases were not available. After starting the investigation, we discovered that the Availability Group (AAG), to which these databases belong, was temporary taken in RESOLVING_NORMAL: The state of the [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\/","og_site_name":"dbi Blog","article_published_time":"2022-02-16T14:45:12+00:00","author":"Nathan Courtine","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Nathan Courtine","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\/"},"author":{"name":"Nathan Courtine","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"headline":"Access Check Results entries too large, resulting in a memory pressure","datePublished":"2022-02-16T14:45:12+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\/"},"wordCount":636,"commentCount":0,"keywords":["SQL Server"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\/","url":"https:\/\/www.dbi-services.com\/blog\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\/","name":"Access Check Results entries too large, resulting in a memory pressure - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-02-16T14:45:12+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/access-check-results-entries-too-large-resulting-in-a-memory-pressure\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Access Check Results entries too large, resulting in a memory pressure"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1","name":"Nathan Courtine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","caption":"Nathan Courtine"},"description":"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17121","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=17121"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17121\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=17121"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=17121"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=17121"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=17121"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}