• DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') will not clear all the procedure cache, it will only clear token entries for the adhoc queries.

    Tracing sp:recompile or sqlstatementrecompile event will not help, as we are seeing only new compiles not recompiles. We have used sp:cachemiss to idenfity the new compiles and we have resolved the issue.

    The issue was caused by the JBOSS connection string, where it has an option to validate the SQL connection using check-valid-connection-sql, there we have used a SQL statement 'SELECT 1', which was not geting cached, becuase its compiling cost is zero and every time SELECT 1 is being executed, it gets complied. SELECT 1 is being called every time JBOSS sends a SQL statement to the SQL Server.

    We have replaced check-valid-connection-sql with background-validation option, where it doesn't validate the connection for every SQL statement.