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.