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 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.
- How To Find Poorly Performing SQL Server Queries To Tune Using Query Store
- SQL Server Community Tools: The Wrap Up And Combined Link
- SQL Server Community Tools: How To Dig Deeper With Expert Mode With sp_QuickieStore
- SQL Server Community Tools: Formatting sp_QuickieStore Output So It’s Easier To Understand