I rely on Query Store every day—it lets me uncover performance issues in minutes, even without knowing the app code. If you want real insight into plan choices, regressions, and control over execution plans, you need to turn it on. Learn how to configure it, use its DMVs, spot plan changes fast, and finally answer, “Why was my query slow?”
Turn It On: The Power of Query Store for Rapid Performance Tuning Monica Morehouse (Rathbun) Consultant, Denny Cherry and Associates Consulting 1. Drag & drop your photo onto the slide. 2. Resize & crop so the key part of the photo will show up here. You will want the picture to go to ALL THE WAY to the top, bottom, and left side. On the right, position it so it only just barely covers the red line. IMPORTANT: Only resize photos using the CORNER SQUARE to maintain proportions (never have distorted photos in your slides). Use that to get the HEIGHT you need (so it touches the top and bottom of the slide. Use CROP (double click the photo and it will show up under “picture format” tab) to get the photo to the WIDTH you need. Presentation Rules Always Ask Questions Interrupt me This is a two-way conversation let’s learn from each other’s experiences QUERY STORE BAD REP Bad Reputation Why? (In the beginning…….) Query Store Capture Mode in SQL Server 2016 and 2017 the default for this AUTO ALL which which reduced overhead your created capturecapture overhead on youron server. server. “SIMPLY CHANGED “DBA’s and Bosses ALL to AUTO and were SAD” POOF GONE” If it runs MORE THAN 3 TIMES it CAPTURES IT else doesn't MILLIONS OF DATABASES There is no reason NOT to TURN IT ON! CONFIGURATION BASICS Database Scoped USE [master] GO ALTER DATABASE [AdventureWorks2019] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO) CAUTION ALL means capture the one offs, and you don’t want this AUTO – need to run multiple times WHAT IS QUERY STORE and WHY DO I CARE? QUERY STORE FOR TUNING REGRESSED QUERIES Plans that have gotten WORSE over a time period COMPARING PLANS THINGS TO LOOK AT
What’s different?
Use to see tuning improvements
Compare after upgrades COMPARING PLANS EXAMPLE DIVE INTO THE PROPERTIES QUERIES WITH FORCED PLANS FORCE LAST GOOD PLAN SADLY, NOT ON IN FABRIC ALTER DATABASE SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); CPU Gain > 10 seconds Number of errors in new plan > than recommend Plan Verified Force Plan is Better than Current sys.dm_db_tuning_recommendations (Transact-SQL) FORCED PLAN RECOMMENDATIONS GAINS UNFORCED WHY? Reason Description ONLINE_INDEX_BUILD query tries to modify data while target table has an index that is being built online INVALID_STARJOIN plan contains invalid StarJoin specification TIME_OUT Optimizer exceeded number of allowed operations while searching for plan specified by forced plan NO_DB A database specified in the plan does not exist HINT_CONFLICT Query cannot be compiled because plan conflicts with a query hint DQ_NO_FORCING_SUPPORTED plan conflicts with use of distributed query or full-text operations. NO_PLAN forced plan could not be verified to be valid for the query NO_INDEX Index specified in plan no longer exists VIEW_COMPILE_FAILED problem in an indexed view referenced in the plan GENERAL_FAILURE: problem in an indexed view referenced in the plan REASONS FOR UNFORCE FIND ANOMALIES QUICKLY OVERALL RESOURCE CONSUMPTION OVERALL RESOURCE CONSUMPTION FIND PERFORMANCE WINS QUICKLY OVERALL RESOURCE CONSUMPTION QUERY STORE HINT SQL 2022 Hint by query id EXEC sp_query_store_set_hints @query_id=6, @value = N'OPTION(RECOMPILE)'; GO QUERY STORE HINTS QUERY STORE IN THE CLOUD Understanding where the insights come from QUERY PERFORMANCE INSIGHTS QUERY DETAILS Note the Query ID this links directing to Query Store See RunTime Stats per hour Pay Attention to CPU/Data IO Execution Counts can be HUGE indicator of death by 1000 cuts PERFORMANCE DASHBOARD FABRIC READ ONLY REPLICAS Things to watch out for when you offload your workload and need to performance tune. QUERY STORE IS REPLICATED Data is Primary Workload This is of NO USE TO YOU! Sad, I know NOW LIVE - CAUTION – PREVIEW ON SQL 2022 (with Trace Flag) 2025 DEFAULTED Shared Channel that Keeps Secondary Up to Date Larger Query Store Delayed Durability on Overload QUERY STORE ON Primary STORES Secondary ALTER DATABASE [Database_Name] FOR SECONDARY SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE ); DEMO How was the session? Complete Session Surveys in for your chance to WIN PRIZES! Monica Rathbun MRathbun@sqlespresso.com @SQLEspresso sqlespresso.com /in/sqlespresso