SQL Server commonly uses memory for 3 things:

  • Caching data – to avoid reading it from slower disk
  • Query workspace – because queries do sorts, joins, etc
  • Caching execution plans – to avoid building a new plan every time we run a query

But there are a few other things that can use memory, too, and if they grow out of control, they can put pressure on the above caches.

The USERSTORE_TOKENPERM cache is one of those caches that can grow wildly out of size, and put so much pressure on SQL Server that it can’t cache enough plans. In this scenario, CPU usage grows higher because SQL Server is constantly forced to build new execution plans over and over again for exactly the same queries.

This was a problem over a decade ago, in the SQL Server 2000-2005 days, but we’ve started to see it pop back up again in newer versions of SQL Server. I’m gonna be honest, dear reader: this is not an easy problem to solve. I really pride myself on handing you “just do this” types of advice, but this is the kind of cutting edge problem where I don’t have easy answers yet.

Start with these resources:

  • Adventures with TokenAndPermUserStore by sql.sasquatch – this isn’t a 1-2-3 tutorial type post, but more of a collection of resources to start your learning journey.
  • UserStore_TokenPerm Is Huge – a question, but scroll down to the answer from FarNorthDBA about app roles.
  • Token and Perm User Store Growth – note the scenarios Gail describes about multiple logins/usernames, and logs of dynamic/ad-hoc SQL. (This is a situation I’ve seen cause large cache growths.)
  • And I almost feel guilty for suggesting this, but this Stack Exchange question and answer suggests that a solution might involve the language settings for users. As of 2020/04/25, the answer says that it involves changing the language to “none”, but as far as I’m aware that’s not actually possible, so I’m staying tuned to see if Microsoft clarifies it.

A temporary fix is to run:

But the issue will still pop back up, usually in a matter of hours. I’ve got a couple of clients who are running that on a scheduled basis every couple of hours while they work with Microsoft to track down a longer term resolution.