Hi,
We’re seeing an issue with SQL server where the performance of the server degrades over time when there are a large amount of EXECUTE AS USER statements.
We’re hosting a legacy system based on SharePoint which is generating these transactions so in the short term we are unable to obtain the source to refactor this code.
To minimally replicate the issue we can run the following code:
DECLARE @counter INT = 1;
DECLARE @cookie VARBINARY(100);
WHILE @counter <= 5000
BEGIN
EXECUTE AS USER = N'DataReader' WITH COOKIE INTO @cookie;
REVERT WITH COOKIE = @cookie
SET @counter += 1;
END
The first run takes just over 2 seconds, and then subsequent runs take progressively longer to complete.
Run | Duration |
1 | 00:02.343 |
2 | 00:03.437 |
3 | 00:04.984 |
4 | 00:07.094 |
5 | 00:09.875 |
6 | 00:13.578 |
Restarting the SQL server service restores performance back to the start.
From running the SQL Profiler, it seems as if the bottleneck is with the EXECUTE AS USER statement, and the REVERT statement takes approximately 0ms every time.
From running various numbers through this I’ve found that running batches of 30 per second doesn’t seem to degrade performance, but after a while, 60 per second does.
Furthermore, if we run just:
EXECUTE AS USER = N'DataReader'
REVERT
And we’re not storing the cookie, then I can run 50k in the loop, it takes 0.5 seconds, and doesn’t degrade performance with subsequent runs.
We’re running this test internally on SQL Server 2016, but we’ve also run the same test on Azure SQL, which I can only assume is running the latest version. It exhibits the same behaviour, so it would appear that simply upgrading versions wouldn’t solve the problem.
Does anyone know what’s going on here? Is the performance being degraded by storage of these cookies? If so, where are they even being stored and is there any way to mitigate this or periodically clear the cookie cache to get some of the lost performance back?
Any thoughts on the matter welcome!
Mat
