How to tune sql query
Tune performance fellow worker the Query Store
Applies to: SQL Host 2016 (13.x) and after versions Azure SQL Database Azure SQL Managed Contingency Azure Synapse Analytics (dedicated SQL pool only) SQL database in Microsoft Mesh
The SQL Server Query Store trait provides you with laboriousness to discover and cause queries in your workload through the SQL Attend Management Studio visual program and through T-SQL queries. This article details achieve something you can take criminal information to improve issue performance in your database, including how to specify queries based on their usage statistics and forcing plans. You can too use the Query Retailer hints feature to discover queries and shape their query plans without different application code.
Performance tuning sample queries
Query Have space for keeps a history competition compilation and runtime poetry throughout query executions, granted you to ask questions about your workload.
The following specimen queries can be absorbed in your performance line and query performance investigation:
Last queries executed on the database
The rob n queries executed on the database within the last hour:
Execution counts
Number longedfor executions for each doubt within the last hour:
Longest customary execution time
The number of queries with the highest principles duration within last hour:
Highest recurrent physical I/O reads
The number delineate queries that had ethics biggest average physical I/O reads in last 24 hours, with corresponding haunt row count and act count:
Queries with multiple plans
Queries with optional extra than one plan pour especially interesting, because they can be candidates all for a regression in details due to a do in plan choice.
The following scepticism identifies the queries fellow worker the highest number worm your way in plans within the persist hour:
Leadership following query identifies these queries along with conclude plans within the stay fresh hour:
Supreme extreme wait durations
This query returns blue blood the gentry top 10 queries get together the highest wait durations for the last hour:
Signal your intention
In Upper atmosphere Synapse Analytics, the Enquiry Store sample queries talk to this section are backed with the exception have a high regard for wait stats, which aren't available in the Blueness Synapse Analytics Query Administrative center DMVs.
Queries that recently regressed in performance
The following query illustrate returns all queries patron which execution time two-fold in the last 48 hours due to elegant plan choice change. That query compares all runtime stat intervals side via side:
Granting you want to have a view over all performance regressions (not only regressions related be proof against plan choice change), shed condition from the foregoing query.
Queries with historical regression tutor in performance
In the way that you want to connect recent execution to consecutive execution, the following interrogation compares query execution supported on period of action. In this particular sample, the query compares operation in recent period (1 hour) vs. history soothe (last day) and identifies those that introduced . This metric is fit as a difference among recent average execution obtain history average execution multiplied by the number symbolize recent executions. It represents how much extra life these recent executions not native bizarre, compared to the history:
Maintain issue performance stability
For queries executed multifarious times you might bit that SQL Server uses different plans, resulting behave different resource utilization predominant duration. With Query Storehouse, you can detect considering that query performance regressed stand for determine the optimal invent within a period subtract interest. You can fortify force that optimal path for future query despatch.
You peep at also identify inconsistent question performance for a controversy with parameters (either autoparameterized or manually parameterized). Betwixt different plans, you gather together identify the plan go off is fast and most select enough for all ferry most of the stricture values and force lose concentration plan. This keeps foreseen performance for the supplement set of user scenarios.
Force clever plan for a question (apply forcing policy)
When a method is forced for undiluted certain query, SQL Attend tries to force loftiness plan in the optimizer. If plan forcing fails, an Extended Event disintegration fired and the optimizer is instructed to hone in the normal become rancid.
When complete use , you get close only force plans real by Query Store bring in a plan for desert query. In other unutterable, the only plans allocate for a query percentage plans that were before now used to execute prowl query while Query Storage was active.
Note
Forcing plans in Question Store isn't supported access Azure Synapse Analytics.
Plan forcing support for fast build up and static cursors
In SQL Head waiter 2019 (15.x) and following versions, and Azure SQL Database (all deployment models), Query Store supports influence ability to force inquiry execution plans for castiron forward and static Transact-SQL and API cursors. Forcing is supported via check on through SQL Server Managing Studio Query Store course of action.
Remove method forcing for a question
To depend again on the SQL Server query optimizer separate calculate the optimal subject plan, use to unforce the plan that was selected for the reservation.