Troubleshooting Security Cache Growth In SQL Server (USERSTORE_TOKENPERM And TokenAndPermUserStore)
Summary
TLDRIn this video, Eric Darling from Darling Data discusses a common yet strange issue on SQL Server involving the security cache, which can lead to various problems such as CPU spikes and memory issues if left unchecked. He explains how to troubleshoot and resolve these issues, offering queries, scripts, and tools like the SP Pressure Detector. Eric also highlights ways to clear out the security cache and prevent it from growing excessively, and encourages users to engage with his channel, hire him for consulting, or attend his upcoming training sessions.
Takeaways
- 💡 The video addresses a recurring issue with the security cache in SQL Server, which can cause various performance problems like CPU spikes, memory issues, and stack dumps.
- 🔍 The security cache stores information about logins and other security details, but if it grows too large, it can negatively impact system performance.
- ⚠️ A bloated security cache can lead to issues such as increased CPU usage, plan cache problems, and high memory consumption, which can disrupt server performance.
- 🛠️ The presenter discusses using `DBCC FREE SYSTEM CACHE (TokenAndPermUserStore)` to manually clear out the security cache if it becomes too large.
- 📊 There are specific queries and scripts that can help monitor and manage the size of the security cache. The `sp_pressure_detector` stored procedure is one example that provides insight into memory usage.
- 🔧 Trace flags like 4610 and 4618 can be used to manage the security cache, but they need to be set as startup flags to be effective.
- 📅 Automating the clearing of the security cache can be achieved by using an agent job that runs the necessary scripts regularly, such as every hour, to prevent the cache from growing too large.
- 🔁 Application behaviors like frequent user impersonation or using `SET APP ROLE` excessively can inflate the security cache. It's important to identify and address these behaviors.
- 📂 The presenter provides a GitHub repository with scripts for monitoring and managing the security cache size, including an automated procedure that can clear the cache when it exceeds a specified threshold.
- 👍 The video also covers the importance of keeping the security cache under control to avoid compromising SQL Server performance, especially when it exceeds 2 GB in size.
Q & A
What is the main issue discussed in the video?
-The main issue discussed is the growth of the security cache in SQL Server, which can lead to various performance problems like CPU spikes, memory issues, and plan cache bloating.
What is the security cache and why can it cause problems?
-The security cache stores information related to user logins and permissions. If it grows unchecked, it can consume significant memory, leading to performance issues such as slowdowns, memory allocation problems, and erratic behavior.
What are some of the transient issues caused by an oversized security cache?
-An oversized security cache can lead to CPU spikes, plan cache bloating, memory leaks, stack dumps, and other performance-related problems in SQL Server.
How does the speaker inflate the security cache for demonstration purposes?
-The speaker inflates the security cache by running a loop using the 'set app role' command repeatedly, which artificially grows the security cache to demonstrate the issues it can cause.
What tool does the speaker recommend for checking memory usage by the security cache?
-The speaker recommends using the 'sp_pressure_detector' stored procedure, which provides information on memory consumers in SQL Server, including the size of the user store token perm cache.
What SQL command is used to manually clear the security cache?
-The SQL command used to clear the security cache is 'DBCC FREE_SYSTEM_CACHE('TokenAndPermUserStore')'. This command frees the memory used by the security cache.
What are the recommended trace flags to prevent security cache growth?
-The recommended trace flags are 4610 and 4618. However, these must be startup trace flags to be effective in preventing security cache growth.
What should users do if they cannot restart SQL Server to apply trace flags?
-If users cannot restart SQL Server, they can schedule a stored procedure to regularly monitor and clear the security cache. The speaker provides scripts that automatically clear the cache when it grows beyond a specified size.
At what size does the speaker suggest you start worrying about security cache growth?
-The speaker suggests starting to monitor the security cache closely once it surpasses 2 GB. If it grows to 4, 8, or even 16 GB, it can lead to significant performance issues.
Where can users find additional resources and scripts related to this issue?
-Users can find additional resources and scripts on the speaker's GitHub repository, which includes stored procedures, agent jobs, and diagnostic tools for monitoring and managing the security cache.
Outlines
🎤 Introduction to a Common Client-Side Issue
Eric introduces himself and the topic of the video, focusing on a strange issue encountered on client servers. This problem causes CPU spikes, plan cache anomalies, memory issues, and other transient problems. The root cause is identified as a growing security cache. He briefly promotes his channel, encouraging viewers to support by subscribing or becoming members. Eric also highlights his consulting services and training opportunities, humorously inviting viewers to interact with him at upcoming events.
🛠️ Demonstration of the Security Cache Problem
Eric explains the security cache issue in more depth, showcasing how it grows excessively when certain operations like 'set app roll' are executed repeatedly. He walks viewers through a query example, noting how the cache grows over time. Though parsing the XML data isn't ideal due to duplicated lines, Eric emphasizes focusing on when spikes occur. He hints at using his own 'pressure detector' stored procedure to monitor the growth of the security cache.
🧰 Using Tools to Manage Security Cache Growth
Eric continues discussing the tools available to detect and manage security cache growth. He demonstrates how to clear the cache manually using DBCC commands. He explains how impersonation and switching users in queries contribute to the cache's growth. Eric also mentions trace flags (4610 and 4618) that can help manage the problem, but only if set as startup flags. If issues persist, he refers viewers to his GitHub repository for scripts and procedures to automate cache clearing.
💾 Long-Term Cache Management Strategies
Eric advises on more aggressive strategies for long-term management of the security cache, recommending setting limits to automatically clear it once it surpasses 1-2 GB. He reiterates that growing caches can lead to performance issues, as they consume memory that could otherwise be used for important functions like caching data pages. For those who can't restart SQL servers to set trace flags, Eric encourages using the provided scripts to regularly clear the cache and prevent server slowdowns.
Mindmap
Keywords
💡Security Cache
💡CPU Spikes
💡Plan Cache
💡Memory Management
💡DBCC Commands
💡Trace Flags
💡SP Pressure Detector
💡User Store Token Perm
💡Application Impersonation
💡Performance Tuning
Highlights
Introduction to a recurring, unusual problem with security cache on client servers leading to issues like CPU spikes and memory problems.
Explanation of how the security cache is meant to cache security information about logins but can cause problems when it grows too large.
Security cache issues can cause memory dumps, plan cache problems, and unpredictable performance in SQL Server.
A demonstration of inflating the security cache using repeated loops of the 'set app roll' command.
The problem becomes noticeable when security cache size exceeds 2-3 gigabytes, leading to performance issues.
Use of queries to track the growth of the security cache and detailed XML results for troubleshooting.
The free stored procedure 'SP Pressure Detector' can help identify the size of memory-consuming user token permissions.
Recommendation to manually clear out the security cache using 'DBCC Free System Cache' if it grows too large.
In cases of regular cache growth, startup Trace Flags 4610 and 4618 may help prevent the problem.
For persistent issues, automated scripts from GitHub can monitor and clear the security cache periodically.
Discussion of when security cache size typically becomes a problem, usually around the 2GB mark and upwards.
The risk of letting the security cache grow unchecked includes losing valuable memory resources for other server functions.
Importance of managing memory efficiently to prevent performance degradation in SQL Server.
Detailed guidance on using SP Pressure Detector and other diagnostic tools to track and resolve memory consumption.
Closing remarks on the broader impact of memory-related issues and the benefits of using automated solutions to manage performance.
Transcripts
Eric darling here with darling data and
I I remember to turn my microphone on so
we're off to a great start here uh in
today's video we're going to talk about
a very weird problem that uh I've seen
crop up on client servers a number of
times now and lead to all sorts of weird
transient issues um CPU spikes plan cach
stuff just weird memory things stack
dumps uh you name it I've seen this
thing be the root cause of all sorts of
weird problems and of course that weird
thing is the security cache uh it sounds
like this like you know nebulous little
little doohickey that you know uh is
supposed to make life easier uh you know
by cashing Securities information about
logins and whatnot but uh if it grows
grows unchecked uh it can cause some
real big problems before we get into all
that of course we need to talk a little
bit about you and me uh and and and
things that things that I like about you
uh I like when people sign up for
memberships and contribute just a little
bit to uh making sure that this channel
stays uh free and open source for
everyone to use uh it it it it's it's
like four bucks a month if if on on the
at the low end uh if you don't have four
bucks a month I totally understand um
you know there are things that I'd
probably rather spend four bucks a month
on to um but if you if you if you don't
want to do that uh liking subscribing
commenting all that good stuff is just
another way to make my little heart go
all a flutter shut up Intel drivers uh
if you're in need of SQL Server
Consulting that's my job apparently uh I
do all this stuff and more and my rates
are reasonable so you can you can hire
me to to do what I'm going to show you
today for you it it's fun it's it's
really great
fulfilling uh really just you know life
affirming work um
if you need training in in the SQL
Server Performance Tuning space you can
get about 24 hours of it for about $150
us when you use the discount code spring
cleaning uh if you look in the the the
the show the the video description I I I
hesitate to call this a show um if you
look in the video description there's a
link with spring cleaning baked right
into it and you can you can you can use
that it's it's amazing technology uh the
the advanced URL technology here at
darling data uh if you want to see me
live in in
person and who wouldn't I'm even better
in person cuz you can throw things at me
and and I don't know G get give real
likes and
comments you can comment in real life in
real time I think that's called a
conversation weird uh I'll be in Dallas
uh Friday September the 6th doing a full
day training session and November 4th
and 5th I will be at past data Summit in
Seattle with Kendra little doing 2 days
of SQL Server performance precons so you
should come see us at those and you
should come see me in Dallas if if you
happen to be in the neighborhood so now
let's get on and talk a little bit
about how we can troubleshoot security
caches
now uh my my good and dear friend Josh
Darnell um who is who is an application
developer uh was able to figure out this
this part of the demo I don't take a lot
of credit here aside from doing some
nice formatting on it even though there
are a couple things that could do some
work apparently you know it's hard to
hard to find good help these days uh and
what the the whole point of this thing
is to inflate our security
cache so uh that's what I've done I have
inflated the security cache uh by using
uh set app roll over and over and over
and over again in a loop uh I actually
had this Loop run I actually the the
first run of it got me to about like a
gig so I ran this a few times to get it
up a little bit higher uh just cuz it
made things a little bit more
interesting for me not because it's you
know really all that fun or interesting
or cool for you uh but that that's what
I did so
um what we've got here are a couple
queries that will help you look at
security cache stuff if you look at this
one you will see that things were
cruising along going just fine for a
while and then at some point the
security cach grew uh so that's about
2.3 gigs plus about another gig from the
ACR cach store so that'll be about 3.2
gigs total uh from uh from
there this is a tough query to remember
it's not very portable it's not very
interesting uh I mean it's kind of
interesting uh actually know if you if
you look at it uh and you actually click
on the XML column uh you can get a lot
more information out uh I don't like
parsing this stuff out in the actual XM
from the XML uh to show in the tabular
result because it makes a lot of like
duplicate lines that are just kind of
messy I generally just zoom into where
like things grew or when things grew or
like if they you know Spike up from what
like a lower number to a higher number
or a high number to another higher
number uh and then I sort of just dig
around in here because you can see all
sorts of interesting stuff about you
know entries getting put in and but not
removed and the size of things and uh
it's it is you know mildly interesting
if you have the this particular fetish
if you want an easy
way excuse me an easy
way of uh of of figuring out if your
system cache if your token and perm user
store is growing a lot you can use my
free store procedure as pressure
detector uh I've get it set up here to
only look at memory and to skip some
other stuff that's not really per
pertinent to us but if you run that
right at the very
top you will have uh this section here
and you will see user store token perm
is about 3.2 gigs total which I believe
is about what we talked about it being
from the the the the XML when we did the
XML query it was like you know 2.3 plus
0.9 something gigs so that's the size of
the token perm store there now you can
clear this out manually by running
this dbcc free system cach token and
perm user store um so but the thing is
if this is a something that happens
regularly because of your application
either using set app roll or like doing
impersonation stuff like I think execute
as is another thing that can really pump
this up uh switching users back and
forth and queries for different reasons
um I've seen a bunch of applications
that you know log in as one user switch
to another user to do something switch
to another user to do a different thing
like they have different per missions
and schemas and stuff uh all those
things that will inflate the the
security caches so you can totally run
this to to to clear that out um if this
is a big long-term problem for you there
are a couple Trace flags that can help
the thing is they don't help if you just
do this these have to be startup Trace
flags for them to really make a
difference uh so if you want to look
into what 4610 and 4618 do if you're
having this problem go crazy they're
pretty use useful if you're having the
issue but only is startup Trace Flags
they don't fix a problem if you just
enable them
globally if this is a problem that
you're having a lot and the trace Flags
don't help and your security cach is
still growing over in my GitHub repo
which I'll have a link to in the the
video description I've got a few scripts
in there that can
help uh one of them is a store procedure
that will run look at the size of your
security cache and if it and there's a
parameter that you pass into to say how
big of a security cach you care about uh
if it grows beyond a certain size it'll
run that dbcc free system Cash Call and
clear it out for you I've also got an
agent job to set that up to run uh the
schedule I think is baked in for like
every hour or something if that's not
often enough you can of course adjust
the schedule but all of this stuff you
can just hit F5 on and of course if you
want to um inflate your security cach
for some reason uh or you just want the
the Standalone analysis scripts here
here you can use that also in my GitHub
repo is sp pressure detector right down
here which you can also get totally for
free you you don't have to like or
subscribe or comment on that uh but you
can get that and also view the biggest
memory consumers on your server and if
that user store token perm stuff is up
there you might want to think about you
know running the dbcc dbcc command to
clear it out maybe enabling the trace
flags and maybe using this code to set
up a job to clear it out on a regular
basis because you might be having all
sorts of weird performance issues uh and
and reliability issues because this
thing grows out of control um as for
like how big it it has to be before I
worry about it um you know
generally once it gets past the two gig
Mark is when I see things I see signs of
trouble if it gets up past like 4 8 16
20 gig somewhere in there then you're
just about guaranteed to have some
issues
so I usually I'm usually pretty
aggressive on this and I usually set
that to be about around like one one to
two gigs uh to clear out uh for the uh
store procedure there because like
really anything beyond that you're just
kind of asking for trouble in the long
term
so uh I hope that this this is not a
problem that you have I hope that you
don't have applications that that blow
out your server security cash because
memory is precious right and if you have
you know 8 16 24 gigs of of security
cache that's that's memory that your
server can't use for other stuff like
caching data Pages or query memory
grants or having a plan cache or other
things like that so it's it's a bad
problem to have uh if you are having
that problem you've got some Trace flags
that you can you can look into you've
got some scripts that you can run to
clear it out um if you you know I
honestly like you know I say try the
trace Flags but a lot of people can't
just restart SQL server with new startup
Trace Flags in place it might be safer
for you to just use the scripts there
excuse me
so look at your SQL server with SP
pressure detector if you see high user
store token perm anything like over like
the 2 gig or so Mark you might want to
think about you know clearing that out
see if the problem comes back if it
keeps coming back I've got you on the
scheduled stuff with the store procedure
right there uh the store procedure also
does some logging so you can see like
you know which runs cleared stuff out um
how big the security cache was when the
when the Run cleared so there's some
diagnostic data in there too that's
pretty helpful so
anyway thank you for watching I hope you
enjoyed yourselves I hope you learned
something uh I hope that uh every all
your dreams come true um I hope that you
just get everything you want from life
it's it's a short Endeavor and and
feeling like you are are missing out on
stuff is never a good feeling so I hope
I hope you've got no fomo I hope hope
that you get everything that your heart
desires
including this video coming to an end
that's what I desire right now because I
feel like I'm sticking the landing a
little bit here anyway uh uh thank you
for watching I'm going to upload this
and figure out what to do with my life
next all right cool thank you
関連動画をさらに表示
How To Fix Instagram Message Problem
Improve API Performance by Utilizing the Platform Cache
iOS 18 | A Maior Falha da Apple [DESEJA BAIXAR O OCC ?] Dá pra Corrigir ou é Vírus? #ios18
Los CDNs NO SIRVEN..
Bell's data modernization journey: executing for the future with Google
SMT 1-2 Web Security Overview
5.0 / 5 (0 votes)