This is a short story about a SQL Server troubleshooting job from 2014.

The Plan Cache Hit Ratio was 60%. There was a massive number of recompiles. It was a smell that the server could not reuse the execution plans effectively.

The workload was ad-hoc, but the queries were quite uniform, so better caching was expected.

It turned out that the queries sent by the application contained a comment which had a timestamp value. Actually, despite the fact the “meat” of the queries did not have much variation, SQL Server treated them as different ones, so each query had to be recompiled. The server hashes the text of the incoming SQL request, so even 1 character change is enough for recompilation.

After removing the generated comments from the code the Plan Cache Hit Ratio went up to 90%. Probably the processor load was also decreased, but it is harder to quantify reliably.

Moral of the story: do not add varying comments to ad-hoc queries.

Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.