Cached query plan issues
As we discussed earlier in the sys.dm_exec_query_stats section, the SQL Database Engine maintains execution statistics for all the queries that are currently in the cache. There is a wealth of information in this DMV that we can use to troubleshoot several different query performance-related issues. We will cover a few issues here, but be sure to reference the BPCheck script in the Tiger Toolbox (https://aka.ms/bpcheck) for a more comprehensive example of queries to identify these scenarios and others.
Single-use plans (query fingerprints)
In the EXECUTE vs. sp_executesql section of Chapter 5, Writing Elegant T-SQL Queries, we discussed how to send ad hoc T-SQL queries to the SQL Database Engine in a way that allows for plan reuse (also see the Plan caching and re-use section in Chapter 1, Understanding Query Processing, for the importance of plan reuse). If we are not sure whether or not our application is successfully parameterizing queries and leveraging...
