SQL Application Role: sp_setapprole execution time is over 3000 ms

Question

Hi, I use SQL Application Role 10 years ago without problems, recently my .net app is slow, when I use profiler tool to trace the commands I see the duration of sp_setapprole command is high

I need help

thanks

Answer 1

There is a known issue with using sp_setapprole with cookie like you do. There is a memory leak in the TokenAndPermStore. I have not heard about that causing excessive execution times, but that would not be unlikely.

You can run this query, to see if you are a victim to the leak:

SELECT TOP 15
    [type] AS [Memory Clerk]
    ,CONVERT(DECIMAL(16,3),SUM(pages_kb) / 1024.0) AS [Memory Usage(MB)]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]
ORDER BY sum(pages_kb) DESC

Look for USERSTORE_TOKENPERM.

To clear it, try

DBCC FREESYSTEMCACHE('TokenAndPermUserStore')

Re-run the query above to see that it had effect.

Answer 2

Hi @Christian Chevarria Mar ,

Welcome to Microsoft Q&A!
Please see this link which is a similar thread which you can refer to.
https://www.sqlservercentral.com/forums/topic/sql2008r2-and-blocking-using-sp_setapprole-and-sp_unsetapprole
Hope this can help you.
It is a good choice to flushed the cache using:
DBCC FREESYSTEMCACHE (‘TokenAndPermUserStore’)
And you can also set up an assignment to complete it during peak times.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.