Caching The Line
This metric gets looked at a lot in the plan cache to see how effective it is. The main problem is that with high enough churn, you might not catch all the queries involved in the problem. Here are a couple ways to look at this in Query Store.
WITH x AS ( SELECT single_use_queries = SUM ( CASE WHEN qsrs.count_executions = 1 THEN 1 ELSE 0 END ), total_queries = COUNT_BIG(*) FROM sys.query_store_runtime_stats AS qsrs ) SELECT x.*, percent_single_use_plans = CONVERT ( decimal(5,2), single_use_queries / ( 1. * NULLIF ( x.total_queries, 0 ) ) * 100. ) FROM x; SELECT qsqt.query_sql_text FROM sys.query_store_query_text AS qsqt WHERE EXISTS ( SELECT 1/0 FROM sys.query_store_query AS qsq JOIN sys.query_store_plan AS qsp ON qsq.query_id = qsp.query_id JOIN sys.query_store_runtime_stats AS qsrs ON qsp.plan_id = qsrs.plan_id WHERE qsqt.query_text_id = qsq.query_text_id AND qsrs.count_executions = 1 );
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.