One thing I hate to see while troubleshooting performance problems on clients' systems is a failure to reuse query plans. Before SQL Server can execute a batch file or stored procedure, it must first determine the most proper and efficient way to execute each statement within that batch file or stored procedure. This process results in a query plan that's stored in the procedure cache. (Don’t let the name fool you: All query plans reside in the procedure cache, regardless of the type of database object they represent.)
Now, the purpose of storing the query plan is simple. Generating the query plan is often a relatively expensive operation. You wouldn't want to go through SQL Server's optimization process every time you execute the batch file or stored procedure because it would always add time to each execution. It makes more sense take a small hit upfront once, then reuse the query plan for each subsequent execution of the same batch file or stored procedure. This configuration saves an enormous amount of time and resources on busy systems—more so than most people realize. In fact, I've seen more than my fair share of systems crippled because they don't reuse query plans.
Before I go much further, I highly recommend taking a look at Kalen Delaney's "Controlling Parameterization" and "Reusing Query Plans," which offer detailed discussions of plan reuse and the procedure cache. Also, be sure to check out the Microsoft article "Plan Caching in SQL Server 2008," the majority of which is relevant to SQL Server 2005.
Troubled Roots
Most of the problems associated with not reusing query plans stem from an overuse of ad hoc queries. In this case, I'll define an ad hoc query as one or more statements sent from the client to SQL Server without regard to proper parameterization (as outlined in the aforementioned articles). In a nutshell, this means that potentially for each statement called, a new and unique query plan needs to get generated, even though the only difference might have been the value for the SARG in the WHERE clause.
This situation can wreak havoc on the system in several ways; most often, it results in excessive CPU usage because every submitted query needs to go through the costly optimization process described above. Another result is that a large amount of memory might be utilized for the procedure cache, particularly on the 64-bit editions of SQL Server. The procedure cache can grow to 6GB in size, mostly filled with plans that will never be reused. What a waste of good memory!
Evil Cousin
Most of you have seen those problems and are at least familiar with the concepts behind them. The symptoms are easy to spot. But you probably aren't as familiar with another deadly aspect of query plans—one that involves performance. It's related to another cache in SQL Server memory, called the TokenAndPermUserStore. As its name implies, this normally tiny cache stores tokens and permissions related to the plans in the cache. Of special note in this cache is an entry called the cumulative permission check, which has an ID of 65535.
The Microsoft article "Queries take a longer time to finish running when the size of the TokenAndPermUserStore cache grows in SQL Server 2005" goes into detail about this more insidious problem, but essentially the TokenAndPermUserStore cache maintains the security token types LoginToken, TokenPerm, UserToken, SecContextToken, and TokenAccessResult—and many TokenAccessResult entries that have the 65535 ID.
Not unlike an actual query plan, the cached entry here is intended to speed up security checks each time a given query runs. And just as each ad hoc query creates a new query plan and places it into the procedure cache, it inserts a new entry for the TokenAndPermUserStore cache, as well. On a system with reuses query plans effectively, this cache would be as large as only a few megabytes, making each new lookup fast and efficient. But on a 64-bit system with lots of ad hoc or dynamic SQL queries, this cache can grow to hundreds of megabytes in size.
As the cache grows in size, the time it takes to check the permissions of anything with a 65535 ID takes longer and longer to complete. The most noticeable symptom is that everything slows down and timeouts might occur. The overall CPU usage on the SQL Server system will generally appear to be on par with normal workloads, but less work overall will be getting done. You'll most likely see an increase in the SOS_SCHEDULER_YIELD wait type, too. You might have experienced these symptoms, but I bet you haven't attributed them to the TokenAndPermUserStore—you probably hadn't heard of it before!
Smoking Gun
How do you know whether you're a victim of this problem? That determination is fairly simple. The following query shows you the amount of memory that the TokenAndPermUserStore cache is consuming:
SELECT SUM(single_pages_kb + multi_pages_kb) AS
"CurrentSizeOfTokenCache(kb)"
FROM sys.dm_os_memory_clerks
WHERE \\[name\\] = 'TokenAndPermUserStore'
As far as the size the cache can reach before problems start manifesting themselves, that can depend on several factors. But, generally, if it's under 10MB, you should be fine. I'd start getting concerned between 10MB and 50MB. If the amount of consumed memory is over 50MB, you're probably affected by this problem. And, obviously, the higher the level, the worse your problems will be.
Resolution
This problem is most prevalent on SQL Server 2005, particularly prior to SP3. SP2 introduced some changes to help with the situation, and SP3 added further fixes. But neither service pack completely eliminated the problem (although SP3 let you adjust the amount of memory that the TokenAndPermUserStore can use).
I know many of you are using SQL Server 2005 with service packs ranging from SP1 to SP3, and combined with heavy use of ad hoc SQL, you're prone to these problems. So, do yourself a favor and read through the aforementioned articles and check your system to see where you stand. You might be surprised to find that this problem has been affecting you all along, and you never knew it.
