Troubleshooting Security Cache Growth In SQL Server (USERSTORE_TOKENPERM And TokenAndPermUserStore)

Summary

In 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

00:00

🎤 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.

05:01

🛠️ 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.

10:02

🧰 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.

Keywords

💡Security Cache

The security cache in SQL Server stores information related to logins and permissions. When this cache grows unchecked, it can lead to various system performance issues such as CPU spikes, memory problems, and stack dumps. In the video, the speaker explains that managing the security cache is crucial to avoiding these problems.

💡CPU Spikes

CPU spikes refer to sudden increases in CPU usage that can affect system performance. The speaker identifies CPU spikes as one of the main symptoms of an unchecked security cache, which can cause the system to perform inefficiently. Monitoring and managing cache size can help mitigate these issues.

💡Plan Cache

The plan cache stores execution plans in SQL Server for reuse, which can speed up query execution. However, if the security cache grows too large, it can negatively impact the plan cache by using up valuable memory that could otherwise be allocated for storing these execution plans, leading to slower queries.

💡Memory Management

Memory management refers to how resources are allocated and utilized in a system. In the video, the speaker emphasizes how the unchecked growth of the security cache can consume memory that would otherwise be used for other essential functions like query execution or caching data pages. Effective memory management involves clearing caches and adjusting settings to optimize performance.

💡DBCC Commands

DBCC (Database Console Commands) are a set of SQL Server commands used for database management and maintenance. The speaker mentions the command 'DBCC FREE SYSTEM CACHE' as a way to manually clear the security cache when it grows too large. This helps free up memory and maintain system performance.

💡Trace Flags

Trace flags are used in SQL Server to enable or disable specific database behaviors for troubleshooting. The video discusses two trace flags, 4610 and 4618, which can help prevent the security cache from growing too large by managing memory usage. These flags must be set as startup trace flags to be effective.

💡SP Pressure Detector

SP Pressure Detector is a stored procedure mentioned by the speaker that helps detect memory pressure on SQL Server. It provides insights into how much memory is being consumed by different components, such as the security cache. The speaker uses this tool to monitor whether the security cache is growing too large.

💡User Store Token Perm

User Store Token Perm refers to a specific type of memory cache in SQL Server that stores user permissions and tokens. The video describes how this cache can grow excessively due to certain operations, such as user impersonation or switching between users in queries, leading to performance degradation.

💡Application Impersonation

Application impersonation occurs when a system or query switches between different user accounts to perform tasks that require varying permissions. The speaker mentions that this practice can significantly inflate the security cache, contributing to system inefficiencies if not managed properly.

💡Performance Tuning

Performance tuning refers to optimizing a system to improve its efficiency and speed. In the context of the video, the speaker offers training on SQL Server performance tuning, focusing on identifying and mitigating issues like an overgrown security cache. Proper tuning can enhance server stability and performance.

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