SQL Saturday OKC 2016 Edition Inside the SQL

SQL Saturday OKC 2016 Edition Inside the SQL Server Query Store Bob Ward Principal Architect – Tiger Team Microsoft bobward@microsoft. com @bobwardms http: //aka. ms/bobsql 11 -13 August 2016. NIMHANS Convention Centre, Bangalore, India. Want deck and demos now? http: //aka. ms/bobwardms

Objectives and Takeaways Understand What is the Query Store and Why it can be useful for Query Performance Understood How the Query Store works to know its capabilities and limitations Understand how to troubleshoot problems that may occur while using the Query Store A rich persisted database of query execution over time for SQL Server and Azure It is the central starting point for Query Performance Tuning and Troubleshooting

Just Do It! Demo

Where do we go next? • • • The What and the Why What is tracked and persisted How does it really work? How do I manage it and what if doesn’t work? In-Memory OLTP and Azure SQL Database here

The What and the Why • A store of compiled queries, plans, and stats – It is a database (store) of query history made up of memory structures and system tables – Contains statements, plans, properties, and statistics – A Query Flight Recorder Survives crashes • Why is it useful – – Records critical information Records for limited time Doesn’t record everything Analyze performance and details of cacheable queries and plans that are no longer in cache Analyze the history of queries and plans across server restarts Look at properties and statistics for queries not available in DMVs Allows a simpler method to force plans vs using plan guides

What can you do with this? page • • • Analyze and fix queries with plans changes over time What queries consume the most/least cpu, i/o, memory over time? Compare performance before/after a change (A/B Testing) Plan stability after upgrading to SQL Server 2016 Are ad-hoc queries killing performance filling up your cache? • • • What is the standard deviation of performance for a query? What % of time is the performance of my query spent on compile? What is the performance of queries with different set_options? Keep performance information in the backup of the database Force a plan based on historical performance using a simple id

What is tracked and persisted Plan store Runtime stats

What is Tracked Query? Any T-SQL DML statement Independent of what is cached Each statement in an object is a query Statement text appears in parameterized form Exceptions: • SET SHOWPLAN* • Queries executed from different db context In Out SELECT DDL UPDATE INSERT DELETE CREATE, ALTER, DROP, … BULK INSERT “Commands” DBCC, KILL, BACKUP, …

Query Store and Statistics Compilation Timestamp – first and last Count Duration – total, avg, last Bind (CPU and Duration) – total, avg, last Optimize (CPU and Duration) – total, avg, last Memory – total, avg, and max

What else should I know? “Config Settings” – sys. query_context_settings • set_options • cursor_options • schema_id DMVs vs Query Store • • here compilation statistics plan properties including engine version and db compat level details and stats for plan variations used for a query over time stdev sumsquare for execution stats stored Recorded as queries are compiled No polling required to “save off” or determine diffs Record query execution stats even if aborted

Exploring the Query Store Demo

THE QUERY STORE ARCHITECTURE

Compiling and the Plan Store

Execution and Runtime Stats Interval Timeframe for aggregation of stats for plan Flush Interval How often we persist to disk

How does persist work? command like '%QUERY STORE%' DATA_FLUSH_INTERVAL_SECONDS (Default 15 mins) QDS_PERSIST_TASK_MAIN_LOOP_SLEEP 60 secs

Query Store Memory Structures Memory Clerk/Object Description MEMORYCLERK_QUERYDISKSTORE_HASHMAP • • Hash table of queries and plans for instance/node Largest memory consumer Uses MEMOBJ_QUERYDISKSTORE (NUMA enabled) Repopulated from disk at startup MEMORYCLERK_QUERYDISKSTORE • • • General clerk for overall Query Store for instance Should be fairly fixed in size and small Uses MEMOBJ_QUERYDISKSTORE (NUMA enabled) MEMOBJ_QUERYSTOREPARTITIONEDHEAP • CPU partitioned heap for execution stats for instance USERSTORE_QDSSTMT • Temp buffers to store statements before persisted CACHESTORE_QDSCONTEXTSETTINGS • Track unique context settings across all queries CACHESTORE_QDSRUNTIMESTATS • Cache of aggregated runtime stats before persisted

Query Store Maintenance sys. database_query_store_options Enabling, Clearing, and State • • ON = Enable; OFF = Disabled (default, existing state and data kept); CLEAR = TRUNCATE tables READ_WRITE = Default when ON; READ_ONLY = intentional or problem (desired != actual) Size, limits, and retention • • • Default max size = 100 Mb (limited by overall database size). If you hit max, state = READ_ONLY Default max plans per query = 200 (this is silent but its well… 200!) Best Default days queries kept in store = 30 days Practices Capture and cleanup efficiently • • Query capture mode of AUTO (Default is ALL) = capture relevant queries based on execution count and resource consumption. NONE = Stop capture new queries but track and persist stats for existing. Sized based cleanup of AUTO (Default is AUTO) = Remove oldest queries and least expensive when 90% of max (until 80% reached). Overrides time retention (days kept in store) Capture and cleanup efficiently • • • sp_query_store_remove_plan = delete specific plan and associated runtime stats sp_query_store_remove_query = delete query, associated query text, plans and runtime stats sp_query_store_reset_exec_stats = Delete runtime stats for specific plan

Facts You Should Know Non EE/Dev edition have auto cleanup issue Query plan garbled? Get plan_id and then get XML plan. Working on a fix • Queries are only tracked if in enabled db context • Use ALTER to modify objects or “duplicate” queries will be tracked • Forcing plans is better than Plan Guides Force by a simple id You can change the text of a procedure • There is a cost (ad-hoc and In-Memory workloads) • stmt_sql_handle matches statement not batch Join with dm_exec_query_stats Efficient index for query_store_query_text Find handle from text (even for parameterized queries) • Encrypted procs hides text as with DMVs or catalog views • We have seen negligible impact from using Query Store with TPC

Query Store Troubleshooting Query store reverts to read-only mode • Database not in state to allow writing (emergency, read-only, single user, …) • Max storage size hit • Memory limits (Azure SQL Database only) Failure forcing plans • Query still compiles and executes • Unforcing a plan clears previous force fail reason • You must remove the failed force plan to allow new plan to be stored Restore backup considerations • Set to READ_ONLY if using for analysis • If you restore to a different database name forced plans may fail Tracing and Monitoring • XEvent : 60+ events named query_store* • Perfmon: SQL Server: Query Store Index no longer exists, Rename database, ….

Let’s Look Inside Demo

Query Store and In-Memory OLTP here Enabled != Runtime Stats enabled for native procs • sp_xtp_control_query_exec_stats – specific natively compiled procedure • sp_xtp_control_proc_exec_stats – all natively compiled procedures in database • Recompile proc if Query Store enabled after compile or you clear store or remove query/plan Statistics and Properties • Use is_natively_compiled from query_store_plan to identify natively compiled procedures • Compilation duration does not include time spent in C code compilation/generation Other Facts • Forcing plans works as with regular queries • Query capture mode does not affect natively compiled procedures (it is always ALL) • Memory grant metrics not captured for natively compiled procedures

Query Store and Azure SQL Database Functionality and Capability identical • • Catalog Views all available Query Store in database aligned with Azure “contained” concept Available for all service tiers SSMS Reports Supported Differences • • ON by default Query capture = AUTO; We do limit memory for Query Store and may revert to read-only mode memory overcommitted You may need to upgrade to a higher service tier Powers other Functionality • • Query Performance Insight SQL Database Advisor

Power BI and Query Store Demo

Resources • • Query Store team blog post Query Store: A flight data recorder for your database Feature documentation Query store default settings

A Big Thanks to Our Sponsors

Thank you for your time! Share your selfie with hashtag #SSGAS 2016 and win cool prizes Connect us with us: @SQLServer. Geeks facebook. com/groups/the. SQLGeeks www. SQLServer. Geeks. com admin@SQLServer. Geeks. com