Troubleshooting Security Cache Growth In SQL Server (USERSTORE_TOKENPERM And TokenAndPermUserStore)
In this video, I delve into a peculiar issue that has caused numerous headaches for clients-security cache growth in SQL Server. This problem can lead to a myriad of strange and transient issues, including CPU spikes, plan cache problems, memory-related anomalies, stack dumps, and more. I share my journey in troubleshooting these issues, highlighting the importance of keeping an eye on security caches, especially when they grow unchecked. The video includes practical solutions such as using my free store procedure, SP Pressure Detector, to monitor and clear out the token and perm user stores, along with detailed scripts for setting up automated clearing processes through SQL Agent jobs.
CHAPTERS
00:00:00 - Introduction
00:01:39 - Security Cache Overview
00:03:21 - Demo Setup
00:04:26 - Security Cache Queries
00:05:31 - Analyzing XML Data
00:07:10 - Trace Flags and Solutions
00:08:45 - Scripted Solutions
00:11:29 - Conclusion
━━━━━━━━━━━━━━━━━━━━━━━━━━
📚 TRAINING & COURSES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Get AI-Ready With Erik
https://training.erikdarling.com/get-...
SQL Server Performance Engineering Course
https://training.erikdarling.com/sql-...
Learn T-SQL with Erik
https://training.erikdarling.com/lear...
Everything Bundle:
https://training.erikdarling.com/?cou...
━━━━━━━━━━━━━━━━━━━━━━━━━━
🛠️ CONSULTING & SERVICES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Need SQL Server performance help?
https://training.erikdarling.com/sqlc...
━━━━━━━━━━━━━━━━━━━━━━━━━━
💬 CONNECT
━━━━━━━━━━━━━━━━━━━━━━━━━━
Ask questions at Office Hours
https://erikdarling.com/officehours/
Become a channel member
/ @erikdarlingdata
━━━━━━━━━━━━━━━━━━━━━━━━━━