Monitoring Performance By Using the Query Store By SQL Server 2016

Query Store:

Query store feature captures a history of queries, plans and run time statics and retains for DBA’s review.

There are number of metrics inside you can measure.

  1. CPU time.
  2. duration.
  3. execution count.
  4. memory consumption.
  5. logical reads/writes.
  6. physical reads.

To enable this feature you can configure from SSMS or TSQL -Script.

To enable the feature with TSQL

Syntax :

ALTER DATABASE <dbname> SET QUERY_STORE = ON;

 

FROM SSMS

Click on Database Properties, Select Query Store.

on General Properties Enable set to —True.

Querystore

 

 

 

 

 

 

 

 

 

 

After enabling this feature, I ran few quires and.

To view statistic information, Expand Database that enable for Query store(my case I enabled query store on Adventureworks2014)

Expand Query store and select Top Resources Consuming quires. it will display graphs along with plans.

querystore

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s