Query Store is a new functionality introduced since SQL Server 2016, I really love this.
What is Query Store: SQL Server Query Store feature provides you with insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes.
Why I have to use Query Store: Quey store automatically capture a history of queries, plan, and runtime statistics, and retain these for your review. well if you want to choose the hard path to solve performance issues then don't use query store.
How to use Query Store: Well I like this question, I will try to explain whatever I understood.
1. Go to SQL Server Mangement Studio
2. Object Explorer, right-click on a database, and select properties
3. From Database Properties window select Query Store Page
4. From Operation Mode( Requested) Select read write or read only
*** you cannot enable Query Store for master and tempdb
After enabling Query store, go to required Database --> Query Store
Query Store will log information about each query including:
1. Number of executions
2. execution time
3. Memory consumption
4. Logical Reads
5. Logical Writes
6. Physical Reads
7. Number of execution plan changes
To reduce the load on the server, this information is aggregated into a fixed window. If you need more precise data, you should look to Extended Events.
Now open regressed queries view. you will see a similer window like below.
This tool will allow you to see regressions based on any of the recorded metrics. If you see a regression, you have the option to force SQL Server to use an older execution plan.
Subscribe to:
Post Comments (Atom)
How to find table row count?
--Use below query to find table row count select so.name,sp.rows from sys.objects so inner join sys.partitions sp on so.object_id = sp.obj...
-
Warning: the following job steps cannot be reached with the current job step flow logic If you get this error, I would guess that you hav...
-
Data Type SQL Server SSIS SSIS OLE DB ADO.NET Variables Pipeline Buffer bigint Int64 DT_I8 LARGE_INTEGER I...
-
SELECT SERVERPROPERTY('MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName], SERVERPROPERTY('I...
No comments:
Post a Comment