Skip to Content

Efficient Cleaning Up of the Persisted Grant Table

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
  • Reference
  • Refresh

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.

 .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

  • It is just one SQL command
  • There is no unnecessary fetching of data

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();
  • SQL Server will promote to a table lock if more than 5000 row locks. See Microsoft article for more information.

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
GO

Once 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.

Last updated: August 16, 2022

  • Hawkins Inc
  • Repower
  • Bosch
  • RandA
  • Plymouth NHS
  • American Heart Association
  • Systopia
  • Deliotte

Awards & Certifications