Please consider the envrionment before printing
The Persisted Grants table needs regularly cleaning up, how you do this could very much effect the performance and stability of your IdentityServer deployment
As an OAuth provider, IdentityServer can issue the following token types
JWT tokens, once issued, are forgotten by IdentityServer, because the token contains sufficient information for the recipient to validate and inspect the token. However, this is not the case for Reference and Refresh tokens. For Reference and Refresh tokens, the recipient gets a token that it can use to exchange with IdentityServer to get the actual material required. IdentityServer maintains a database table that contains the mapping from reference/refresh tokens to the associated material (called PersistedGrants).
Reference and Refresh tokens don't live forever; they will expire. Therefore expired tokens should be cleaned up from the database. IdentityServer has an out-of-the-box implementation that uses Entity Framework to remove expired entries by first loading them all into the current DbContext and then calling delete on each one. It is controlled by the following options in the bootstrap code.
IdentityServer
.AddOperationalStore(options =>
{
options.EnableTokenCleanup = true;
options.TokenCleanupInterval = 3600;
})A more efficient approach is to use a piece of SQL to delete all entries if they have expired.
DELETE
FROM PersistedGrants
WHERE Expiration < @CurrentDateTime;The query is undoubtedly an improvement, as
However, this query could still create an issue if the table contains many expired tokens, as the database may choose to create a table lock as opposed to many row locks*. Creating a table lock stops any other connection reading or changing the table and so prevents the issuing of new tokens or fetching non-expired ones. Rewriting the command to delete 1000 items at a time mitigates this issue.
DELETE TOP(1000)
FROM PersistedGrants
WHERE Expiration < GETDATE();The final improvement is to turn the entire process into a stored procedure and have it execute on the database every hour.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE PersistedGrantCleanup
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CurrentDateTime as datetime2 = GETDATE();
WHILE (@@ROWCOUNT > 0)
BEGIN
DELETE TOP(1000)
FROM PersistedGrants
WHERE Expiration < @CurrentDateTime;
END
END
GOOnce configured, remember to switch off the automatic process in IdentityServer.
.AddOperationalStore(options =>
{
options.EnableTokenCleanup = false;
});If you are want help performance turning your IdentityServer or just a general health check, contact us today to book a session with one of our experts.
