CODEWITHSUNDEEP
sql-serversql-server-2017
Mat
Mat

Reputation: 1

Large USERSTORE_OBJPERM in SQL Server

I'm trying to make our SQL Server go faster and have noticed that no stored procedures are staying in the plan cache for any length of time. Most have of the plans have been created in the last hour or so.

Running the script below I see that the USERSTORE_OBJPERM is around 3GB and is the 2nd biggest memory cache on the server after the SQL BUFFERPOOL.

SELECT top 100 * FROM sys.dm_os_memory_clerks where type = 'USERSTORE_OBJPERM'

I've run the same script on a few other of our production servers and none of the USERSTORE_OBJPERM on the other servers are any where near as large around 200MBs.

My question is has anyone seen a USERSTORE_OBJPERM at around 3GB and what might of caused it.

I ran the following to try and clear the cache, it went down down by a 100mb or so and instantly started rising again. DBCC FREESYSTEMCACHE ('ObjPerm - DatabaseName')

Results of script

SQL Server version is 2017 Enterprise with CU22 applied.

Many Thanks in advance for any tips or advice provided Cheers Mat

Upvotes: 0

Views: 50

Answers (1)

Mat
Mat

Reputation: 1

Fixed.

It seems the issue was caused by an application using service broker.

The application was running a script to check permissions every 30 seconds.

Fortunately there was an option to switch the permission check off.

The USERSTORE_OBJPERM cache size is now 200MBs instead of 3GB and stored procedure plans are staying in the cache.

Upvotes: 0

Related Questions