Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sp_Blitz and sp_BlitzFirst: add warning about high USERSTORE_TOKENPERM cache size #2134

Open
erikdarlingdata opened this issue Sep 27, 2019 · 8 comments

Comments

@erikdarlingdata
Copy link
Contributor

@erikdarlingdata erikdarlingdata commented Sep 27, 2019

Back when I wrote this check in, I apparently tacked it onto the warning about the plan cache being erased recently. I feel like that could easily get missed since the message for it is already pretty long.

If you look here, the code is already written to get the size.

The question is: is this important enough to be its own check?

If so, is 2 GB the right size to warn at?

@RobAshton-IM

This comment has been minimized.

Copy link

@RobAshton-IM RobAshton-IM commented Dec 13, 2019

"... is 2 GB the right size to warn at?"

Tough one. If it's 2 GB of 4 GB in total, then I'm interested for sure. If it's 2 GB of 256 GB, then probably not so much.

Is it worth pulling in from SQL OS how much memory is available for all memory caches to fight over to see if USERSTORE_TOKENPERM is using a lot more than its fair share of the available space?

Looking at this post from Bulent Ozkir), and this thread on SQL Server Central gives me a few ideas of variations on this theme to point out different reasons why USERSTORE_TOKENPERM has blown out of proportion. Perhaps there's a new group of checks here?

@erikdarlingdata

This comment has been minimized.

Copy link
Contributor Author

@erikdarlingdata erikdarlingdata commented Mar 1, 2020

@RobAshton-IM I don't think I'm going to work on this one. If you want to, feel free and let me know. Otherwise, I'll probably close this out.

@BrentOzar BrentOzar changed the title Warning about USERSTORE_TOKENPERM: Break it out? sp_Blitz and sp_BlitzFirst: add warning about high USERSTORE_TOKENPERM cache size Mar 2, 2020
@BrentOzar

This comment has been minimized.

Copy link
Member

@BrentOzar BrentOzar commented Mar 2, 2020

Yeah, I'll tackle it. I've got a client fighting this issue, and I'm pretty sure there's a bug in SQL Server around it. They have a Microsoft support call open, and we're still trying to track it down.

I just ran a quick analysis on the SQL ConstantCare data. There's a good chunk of the population where the USERSTORE_TOKENPERM cache size is >20% of the buffer pool. Unfortunately, sys.dm_os_memory_clerks is one of the larger tables we have in SQL ConstantCare (because I didn't do the right filtering in early exe versions), so the queries take a heck of a long time. (Like, an hour or more.) We just rolled out a newer version of the exe that won't gather as much data, so I should be able to start analyzing this faster. I wanna try to figure out if it correlates with a particular build or version - I'm seeing it on 2016 and 2017 for sure.

For now, I'm guessing we'll flag it at 10% or more of the buffer pool (to help catch the worst offenders) and get the dialog going. I'll code that for the March release - if anybody has input, holler.

@BrentOzar

This comment has been minimized.

Copy link
Member

@BrentOzar BrentOzar commented Mar 2, 2020

(Also, just to be horrifyingly clear - that >20% thing is hitting servers with 64GB+ RAM, too, not just small ones. I was excluding anything with less than 32GB RAM from my analysis.)

@erikdarlingdata

This comment has been minimized.

Copy link
Contributor Author

@erikdarlingdata erikdarlingdata commented Mar 2, 2020

Oh wow, 20 percent! that's nutso.

@erikdarlingdata

This comment has been minimized.

Copy link
Contributor Author

@erikdarlingdata erikdarlingdata commented Mar 2, 2020

I'm going to add a check in BlitzCache for this. Can you add it to the labels at some point?

@BrentOzar

This comment has been minimized.

Copy link
Member

@BrentOzar BrentOzar commented Mar 24, 2020

You bet! Added it, and got your sp_BlitzCache pull request in for the March release. Thanks sir!

@BrentOzar BrentOzar mentioned this issue Mar 24, 2020
BrentOzar added a commit that referenced this issue Mar 24, 2020
Sorting by priority, adding new check 69 for userstore_tokenperm cache. Working on #2134.
BrentOzar added a commit that referenced this issue Mar 31, 2020
Adds new CheckID 233 for USERSTORE_TOKENPERM >= 10% of overall memory. Working on #2134.
BrentOzar added a commit that referenced this issue Mar 31, 2020
…OKENPERM_check

#2134 sp_Blitz USERSTORE_TOKENPERM check
@BrentOzar

This comment has been minimized.

Copy link
Member

@BrentOzar BrentOzar commented Mar 31, 2020

Coded in sp_Blitz, will add to sp_BlitzFirst later.

BrentOzar added a commit that referenced this issue Apr 3, 2020
Leftover from live session. Working on #2134.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
3 participants
You can’t perform that action at this time.