If you work with SQL Server or Azure SQL Database, then one of the things you might want to check is the distribution of query plans in the plan cache. If there is a large number of ad-hoc plans in the cache, then there is a good chance that your server is busy compiling the same execution plans many times, wasting a lot of precious memory.
If the same ad-hoc query is executed many times, only with different predicate values, then each instance of the query generates its own plan and consumes memory in the plan cache. If the distribution of values in the corresponding table is more or less uniform, then most likely all of these execution plans are actually the same. What a waste!
You can find the ad-hoc queries with the highest number of instances based on the query hash. You can then calculate the number of instances, the number of compilations per minute and the total amount of memory for each such query.
To make it easier on you, here is a script that does exactly that.
Now, for each query, you should examine it, check the distribution of values in the corresponding tables and columns, and decide whether it is appropriate to change the query into a parameterized query or even a stored procedure. Usually, this will yield better results, since it will save a lot of CPU and memory resources. But you should test and be careful because sometimes it might make things worse due to parameter sniffing.
Also, if you have a large number of ad-hoc queries, then you should enable the Optimize for Ad-hoc Workloads instance configuration. When it's enabled, the plan for an ad-hoc query will not be stored in the plan cache the first time it is encountered. If the same query is encountered the second time, it will then be stored in the plan cache. This optimization improves memory usage, and this is why it is recommended to set it by default.
Comentarios