The plan cache has two limits: The total size and the total number of all plans. The size and entry count limits are explained in the following white paper:
The maximum number of entries that a plan cache can hold is four times the bucket count. You can verify this information by running the following queries:
select name, type, buckets_count from sys.dm_os_memory_cache_hash_tableswhere name IN ( 'SQL Plans' , 'Object Plans' , 'Bound Trees' ) select name, type, pages_kb, entries_count from sys.dm_os_memory_cache_counterswhere name IN ( 'SQL Plans' , 'Object Plans' , 'Bound Trees' )
For example, on 64-bit systems, the number of buckets for the SQL Server plan cache is 40,009. Therefore, the maximum number of entries that can fit inside the SQL Server plan cache is 160,036.
If you have a workload that uses different ad hoc queries, this limit can become a bottleneck. The change that is made by this hotfix addresses this situation. After you install this hotfix and enable the change by using the startup trace flag "-T 174," the bucket count is increased to 160,001 on 64-bit systems. The plan cache is then able to hold a maximum of 640,004 plans.