At my customer, we had a very strange behavior of the BizTalk environment (2 BTS 2006 R2’s running on a SQL 2005 SP2 cluster).

We had an orchestration that took about 1000 milliseconds to execute at the beginning of our performance test (20000 executions per hour of this orchestration).

After many hours of testing, we started noticing a performance degrade.

After 2-3 hours the orchestration took an average of 2000 milliseconds to execute, after 8 hours already 5000 milliseconds!!! This continued without getting better again.

I did very detailed monitoring of this environment and finally found the problem…

The problem is related to SQL 2005 SP2.  This version of SQL contains a bug, having the TokenAndPerUserStore cache not cleared… As this cache gets bigger and bigger queries take longer and longer to complete (These cache entries are used for cumulative permission checks for queries).

When the response times became high (> 5000 milliseconds after hours of testing), I cleared this cache.  The result was immediate: performance went back to normal!!!

This bug is fixed with a cumulative update package or by installing SP3 of SQL Server.