SQL DBA Insights: SQL Server Query Store Using DMV

1. To Find Highly Impact Performance queries:

Description:

Below Query is used to track down long running transactions. The total_elapsed_time column represents the Total elapsed time, in microseconds, for successfully completed executions. Regardless of whether it has a high execution count or is a low execution count but long running transaction.

When users are complaining about severe problems, this query gives you a quick insight into the major query activity. It may not give you the root cause of an issue – therefore you many need to spend time on a more methodical approach such Waits and Queues analysis .

Query:

SELECT TOP 5 t.text
             AS
             'SQL Text',
             st.execution_count,
             Isnull(st.total_elapsed_time / st.execution_count, 0)
             AS 'AVG Excecution Time',
             st.total_worker_time / st.execution_count
             AS 'AVG Worker Time',
             st.total_worker_time,
             st.max_logical_reads,
             st.max_logical_writes,
             st.creation_time,
             Isnull(st.execution_count / Datediff(second, st.creation_time,
                                         Getdate()), 0) AS
             'Calls Per Second'
FROM   sys.dm_exec_query_stats st
       CROSS apply sys.Dm_exec_sql_text(st.sql_handle) t
ORDER  BY st.total_elapsed_time DESC

12. To Find the Current Running Query

Description:

DMV returns a list of executing requests on SQL Server. To view the SQL code , there’s a reference to sys.dm_exec_sql_text which returns the text based on the sql_handle.

Use the query to get a quick overview of any slow running queries. Then drill down into the request to see why it’s causing a problem. Such as SQL Server – sys.dm_exec_sessions and troubleshooting sql memory usage

Query:

SELECT sqltext.text,
       req.session_id,
       req.status,
       req.command,
       req.cpu_time,
       req.total_elapsed_time
FROM   sys.dm_exec_requests req
       CROSS apply sys.Dm_exec_sql_text(sql_handle) AS sqltext

3. Monitoring connections (Retrieves information on the current connection)

Description:

Below Query is used to view the current user who are all Accessing the database with

the information like Login name, Host name, Authentication Type and connected Time Etc.

Query:

SELECT c.session_id,
       c.net_transport,
       c.encrypt_option,
       c.auth_scheme,
       s.host_name,
       s.program_name,
       s.client_interface_name,
       s.login_name,
       s.nt_domain,
       s.nt_user_name,
       s.original_login_name,
       c.connect_time,
       s.login_time
FROM   sys.dm_exec_connections AS c
       JOIN sys.dm_exec_sessions AS s
         ON c.session_id = s.session_id
--WHERE  c.session_id = @@SPID;

SELECT TOP 10 *
FROM   sys.dm_exec_connections
WHERE  session_id = 114

4. Monitoring a Killed/Rollback Command

Description:

The Dynamic Management View(DMV) Query is used to find the SQL Rollback Transaction. It Returns information about each request that is executing within SQL Server.

Query:

SELECT der.session_id,
       der.command,
       der.status,
       der.percent_complete
FROM   sys.dm_exec_requests AS der
WHERE  command IN ( 'killed/rollback', 'rollback' )

5. To find a missing index

Description:

Below Query is used to identify missing indexes on any database.

Note:

1.We should not create more than 5-10 indexes per table.

2.We should pay attention to Avg_Estimated_Impact when you are going to create an index.

Query:

SELECT TOP 20 Round(s.avg_total_user_cost * s.avg_user_impact *
                                  ( s.user_seeks + s.user_scans ), 0) AS
              [Total Cost],
              d.[statement]                                           AS
              [Table Name],
              equality_columns,
              inequality_columns,
              included_columns
FROM   sys.dm_db_missing_index_groups g
       INNER JOIN sys.dm_db_missing_index_group_stats s
               ON s.group_handle = g.index_group_handle
       INNER JOIN sys.dm_db_missing_index_details d
               ON d.index_handle = g.index_handle
ORDER  BY [total cost] DESC

SELECT TOP 25

dm_mid.database_id AS DatabaseID,

dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,

dm_migs.last_user_seek AS Last_User_Seek,

OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],

'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')

+ CASE

WHEN dm_mid.equality_columns IS NOT NULL

AND dm_mid.inequality_columns IS NOT NULL THEN '_'

ELSE ''

END

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')

+ ']'

+ ' ON ' + dm_mid.statement

+ ' (' + ISNULL (dm_mid.equality_columns,'')

+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns

IS NOT NULL THEN ',' ELSE

'' END

+ ISNULL (dm_mid.inequality_columns, '')

+ ')'

+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement

FROM sys.dm_db_missing_index_groups dm_mig

INNER JOIN sys.dm_db_missing_index_group_stats dm_migs

ON dm_migs.group_handle = dm_mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details dm_mid

ON dm_mig.index_handle = dm_mid.index_handle

WHERE dm_mid.database_ID = DB_ID()

ORDER BY Avg_Estimated_Impact DESC

6. To Find last time of update on a table

Description:

The Below Query is used to find the last date and time of an update on a sql table. The SQL Server DMV sys.dm_db_index_usage_stats can supply enough information to allow the DBA to make a good estimate of the time.

Query:

SELECT tbl.NAME,
       ius.last_user_update,
       ius.user_updates,
       ius.last_user_seek,
       ius.last_user_scan,
       ius.last_user_lookup,
       ius.user_seeks,
       ius.user_scans,
       ius.user_lookups
FROM   sys.dm_db_index_usage_stats ius
       INNER JOIN sys.tables tbl
               ON ( tbl.object_id = ius.object_id )
WHERE  ius.database_id = Db_id()

àlast_user_update - Time of last user update.

àuser_updates - Number of updates by user queries.

àlast_user_seek - Time of last user seek .

àlast_user_scan - Time of last user scan.

àlast_user_lookup - Time of last user lookup.

àuser_seeks - Number of seeks by user queries.

àuser_scans - Number of scans by user queries.

àuser_lookups - Number of bookmark lookups by user queries

7. Last time a stored procedure was executed

Description:

The following query will assist in telling you what time a stored procedure was last executed – assuming the query hasn’t been flushed from the cache.

Query:

SELECT Object_name(m.object_id),
       Max(qs.last_execution_time)
FROM   sys.sql_modules m
       LEFT JOIN (sys.dm_exec_query_stats qs
                  CROSS apply sys.Dm_exec_sql_text (qs.sql_handle) st)
              ON m.object_id = st.objectid
                 AND st.dbid = Db_id()
GROUP  BY Object_name(m.object_id);

8. To Monitoring Blocked Queries:

Description:

The Below Query is used to find the Blocked Queries with high CPU usage, which is taking a high performance on the CPU usage.

Query:

SELECT highest_cpu_queries.plan_handle,
       highest_cpu_queries.total_worker_time,
       q.dbid,
       q.objectid,
       Object_name(q.objectid)AS a,
       q.number,
       q.encrypted,
       q.[text]
FROM   (SELECT TOP 50 qs.plan_handle,
                      qs.total_worker_time
        FROM   sys.dm_exec_query_stats qs
        ORDER  BY qs.total_worker_time DESC) AS highest_cpu_queries
       CROSS apply sys.Dm_exec_sql_text(plan_handle) AS q
ORDER  BY highest_cpu_queries.total_worker_time DESC;

9.To find out the Last n queries executed on the database:

Description:

Below Query will be used to find out the Last n queries executed on the database.

Query:

SELECT TOP 10 qt.query_sql_text, q.query_id,

qt.query_text_id, p.plan_id, rs.last_execution_time

FROM sys.query_store_query_text AS qt

JOIN sys.query_store_query AS q

ON qt.query_text_id = q.query_text_id

JOIN sys.query_store_plan AS p

ON q.query_id = p.query_id

JOIN sys.query_store_runtime_stats AS rs

ON p.plan_id = rs.plan_id

ORDER BY rs.last_execution_time DESC;

10. To Find out the Number of executions for each query

Query

SELECT q.query_id, qt.query_text_id, qt.query_sql_text,

SUM(rs.count_executions) AS total_execution_count

FROM sys.query_store_query_text AS qt

JOIN sys.query_store_query AS q

ON qt.query_text_id = q.query_text_id

JOIN sys.query_store_plan AS p

ON q.query_id = p.query_id

JOIN sys.query_store_runtime_stats AS rs

ON p.plan_id = rs.plan_id

GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text

ORDER BY total_execution_count DESC;

11.To Find out the longest average execution time within specific Interval time

Description:

Below Query will provide the number of queries with the longest average execution time within last hour.

Query:

SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,

qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,

rs.last_execution_time

FROM sys.query_store_query_text AS qt

JOIN sys.query_store_query AS q

ON qt.query_text_id = q.query_text_id

JOIN sys.query_store_plan AS p

ON q.query_id = p.query_id

JOIN sys.query_store_runtime_stats AS rs

ON p.plan_id = rs.plan_id

WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())

ORDER BY rs.avg_duration DESC;

12.To Find out the biggest average physical IO reads

Description:

The number of queries that had the biggest average physical IO reads in last 24 hours, with corresponding average row count and execution count

Query:

SELECT TOP 10 rs.avg_physical_io_reads, qt.query_sql_text,

q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id,

rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions

FROM sys.query_store_query_text AS qt

JOIN sys.query_store_query AS q

ON qt.query_text_id = q.query_text_id

JOIN sys.query_store_plan AS p

ON q.query_id = p.query_id

JOIN sys.query_store_runtime_stats AS rs

ON p.plan_id = rs.plan_id

JOIN sys.query_store_runtime_stats_interval AS rsi

ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id

WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())

ORDER BY rs.avg_physical_io_reads DESC;

13.Queries with multiple plans

Description:

These queries are especially interesting because they are candidates for regressions due to plan choice change. The following query identifies these queries along with all plans:

Query:

WITH Query_MultPlans

(

SELECT COUNT(*) AS cnt, q.query_id

FROM sys.query_store_query_text AS qt

JOIN sys.query_store_query AS q

ON qt.query_text_id = q.query_text_id

JOIN sys.query_store_plan AS p

ON p.query_id = q.query_id

GROUP BY q.query_id

HAVING COUNT(distinct plan_id) > 1

)

SELECT q.query_id, object_name(object_id) AS ContainingObject,

query_sql_text, plan_id, p.query_plan AS plan_xml,

p.last_compile_start_time, p.last_execution_time

FROM Query_MultPlans AS qm

JOIN sys.query_store_query AS q

ON qm.query_id = q.query_id

JOIN sys.query_store_plan AS p

ON q.query_id = p.query_id

JOIN sys.query_store_query_text qt

ON qt.query_text_id = q.query_text_id

ORDER BY query_id, plan_id;

14.Queries that recently regressed in performance (comparing different point in time)

Description:

The following query example returns all queries for which execution time doubled in last 48 hours due to a plan choice change. Query compares all runtime stat intervals side by side. (comparing Different Point in time)

Query:

SELECT

qt.query_sql_text,

q.query_id,

qt.query_text_id,

rs1.runtime_stats_id AS runtime_stats_id_1,

rsi1.start_time AS interval_1,

p1.plan_id AS plan_1,

rs1.avg_duration AS avg_duration_1,

rs2.avg_duration AS avg_duration_2,

p2.plan_id AS plan_2,

rsi2.start_time AS interval_2,

rs2.runtime_stats_id AS runtime_stats_id_2

FROM sys.query_store_query_text AS qt

JOIN sys.query_store_query AS q

ON qt.query_text_id = q.query_text_id

JOIN sys.query_store_plan AS p1

ON q.query_id = p1.query_id

JOIN sys.query_store_runtime_stats AS rs1

ON p1.plan_id = rs1.plan_id

JOIN sys.query_store_runtime_stats_interval AS rsi1

ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id

JOIN sys.query_store_plan AS p2

ON q.query_id = p2.query_id

JOIN sys.query_store_runtime_stats AS rs2

ON p2.plan_id = rs2.plan_id

JOIN sys.query_store_runtime_stats_interval AS rsi2

ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id

WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())

AND rsi2.start_time > rsi1.start_time

AND p1.plan_id <> p2.plan_id

AND rs2.avg_duration > 2*rs1.avg_duration

ORDER BY q.query_id, rsi1.start_time, rsi2.start_time;

15.Queries that are waiting the most

Description:

This query will return top 10 queries that wait the most.

Query:

SELECT TOP 10

qt.query_text_id,

q.query_id,

p.plan_id,

sum(total_query_wait_time_ms) AS sum_total_wait_ms

FROM sys.query_store_wait_stats ws

JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id

JOIN sys.query_store_query q ON p.query_id = q.query_id

JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id

GROUP BY qt.query_text_id, q.query_id, p.plan_id

ORDER BY sum_total_wait_ms DESC

16.Queries that recently regressed in performance (comparing recent vs. history execution)

Description:

The next query compares query execution based periods of execution. In this particular example the query compares execution in recent period (1 hour) vs. history period (last day) and identifies those that introduced additional_duration_workload. This metrics is calculated as a difference between recent average execution and history average execution multiplied by the number of recent executions. It actually represents how much of additional duration recent executions introduced compared to history:

Query:

--- "Recent" workload - last 1 hour

DECLARE @recent_start_time datetimeoffset;

DECLARE @recent_end_time datetimeoffset;

SET @recent_start_time = DATEADD(hour, -1, SYSUTCDATETIME());

SET @recent_end_time = SYSUTCDATETIME();

--- "History" workload

DECLARE @history_start_time datetimeoffset;

DECLARE @history_end_time datetimeoffset;

SET @history_start_time = DATEADD(hour, -24, SYSUTCDATETIME());

SET @history_end_time = SYSUTCDATETIME();

WITH

hist AS

(

SELECT

p.query_id query_id,

CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,

SUM(rs.count_executions) count_executions,

COUNT(distinct p.plan_id) num_plans

FROM sys.query_store_runtime_stats AS rs

JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id

WHERE (rs.first_execution_time >= @history_start_time

AND rs.last_execution_time < @history_end_time)

OR (rs.first_execution_time <= @history_start_time

AND rs.last_execution_time > @history_start_time)

OR (rs.first_execution_time <= @history_end_time

AND rs.last_execution_time > @history_end_time)

GROUP BY p.query_id

recent AS

(

SELECT

p.query_id query_id,

CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,

SUM(rs.count_executions) count_executions,

COUNT(distinct p.plan_id) num_plans

FROM sys.query_store_runtime_stats AS rs

JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id

WHERE (rs.first_execution_time >= @recent_start_time

AND rs.last_execution_time < @recent_end_time)

OR (rs.first_execution_time <= @recent_start_time

AND rs.last_execution_time > @recent_start_time)

OR (rs.first_execution_time <= @recent_end_time

AND rs.last_execution_time > @recent_end_time)

GROUP BY p.query_id

)

SELECT

results.query_id query_id,

results.query_text query_text,

results.additional_duration_workload additional_duration_workload,

results.total_duration_recent total_duration_recent,

results.total_duration_hist total_duration_hist,

ISNULL(results.count_executions_recent, 0) count_executions_recent,

ISNULL(results.count_executions_hist, 0) count_executions_hist

FROM

(

SELECT

hist.query_id query_id,

qt.query_sql_text query_text,

ROUND(CONVERT(float, recent.total_duration/

recent.count_executions-hist.total_duration/hist.count_executions)

*(recent.count_executions), 2) AS additional_duration_workload,

ROUND(recent.total_duration, 2) total_duration_recent,

ROUND(hist.total_duration, 2) total_duration_hist,

recent.count_executions count_executions_recent,

hist.count_executions count_executions_hist

FROM hist

JOIN recent

ON hist.query_id = recent.query_id

JOIN sys.query_store_query AS q

ON q.query_id = hist.query_id

JOIN sys.query_store_query_text AS qt

ON q.query_text_id = qt.query_text_id

) AS results

WHERE additional_duration_workload > 0

ORDER BY additional_duration_workload DESC

OPTION (MERGE JOIN);

17.To Find out the Query Store currently active

Description:

Query Store stores its data inside the user database and that is why it has size limit (configured with MAX_STORAGE_SIZE_MB). If data in Query Store hits that limit Query Store will automatically change state from read-write to read-only and stop collecting new data.

Description for column “actual_state_desc” :

OFF (0)
READ_ONLY (1)
READ_WRITE (2)
ERROR (3)

Query:

SELECT actual_state, actual_state_desc, readonly_reason,

current_storage_size_mb, max_storage_size_mb

FROM sys.database_query_store_options;

18.Basic Queries and Plans

Description:

The query returns information about queries and plans in the query store at basic level.

Query:

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*

FROM sys.query_store_plan AS Pl

JOIN sys.query_store_query AS Qry

ON Pl.query_id = Qry.query_id

JOIN sys.query_store_query_text AS Txt

ON Qry.query_text_id = Txt.query_text_id ;

19.To Delete ad-hoc queries (Free up Space)

Description:

This deletes the queries that were only executed only once and that are more than 24 hours old.

Query:

DECLARE @id int

DECLARE adhoc_queries_cursor CURSOR

FOR

SELECT q.query_id

FROM sys.query_store_query_text AS qt

JOIN sys.query_store_query AS q

ON q.query_text_id = qt.query_text_id

JOIN sys.query_store_plan AS p

ON p.query_id = q.query_id

JOIN sys.query_store_runtime_stats AS rs

ON rs.plan_id = p.plan_id

GROUP BY q.query_id

HAVING SUM(rs.count_executions) < 2

AND MAX(rs.last_execution_time) < DATEADD (hour, -24, GETUTCDATE())

ORDER BY q.query_id ;

OPEN adhoc_queries_cursor ;

FETCH NEXT FROM adhoc_queries_cursor INTO @id;

WHILE @@fetch_status = 0

BEGIN

PRINT @id

EXEC sp_query_store_remove_query @id

FETCH NEXT FROM adhoc_queries_cursor INTO @id

END

CLOSE adhoc_queries_cursor ;

DEALLOCATE adhoc_queries_cursor;

20.To Get the Table Wise Count On a Database

Description:

Below query will be used to list size of the table ant their Row Counts on a particular database.

Query:

SELECT sc.NAME + '.' + ta.NAME TableName,
       Sum(pa.rows)            RowCnt
FROM   sys.tables ta
       INNER JOIN sys.partitions pa
               ON pa.object_id = ta.object_id
       INNER JOIN sys.schemas sc
               ON ta.schema_id = sc.schema_id
WHERE  ta.is_ms_shipped = 0
       AND pa.index_id IN ( 1, 0 )
GROUP  BY sc.NAME,
          ta.NAME
ORDER  BY Sum(pa.rows) DESC

(Or)

SELECT T.NAME AS [TABLE NAME],
       I.rows AS [ROWCOUNT]
FROM   sys.tables AS T
       INNER JOIN sys.sysindexes AS I
               ON T.object_id = I.id
                  AND I.indid < 2
ORDER  BY I.rows DESC

21. To Get the Column Name and their datatypes Details.

Description:

Below query will be used to Retrieve the of list of table and their Row Counts, data types, length etc.

Query:

SELECT Object_name(c.object_id)    AS Objectname,
       c.NAME                      'Column Name',
       t.NAME                      'Data type',
       c.max_length                'Max Length',
       c.PRECISION,
       c.scale,
       c.is_nullable,
       Isnull(i.is_primary_key, 0) 'Primary Key'
FROM   sys.columns c
       INNER JOIN sys.types t
               ON c.user_type_id = t.user_type_id
       LEFT OUTER JOIN sys.index_columns ic
                    ON ic.object_id = c.object_id
                       AND ic.column_id = c.column_id
       LEFT OUTER JOIN sys.indexes i
                    ON ic.object_id = i.object_id
                       AND ic.index_id = i.index_id
WHERE  Object_name(c.object_id) IN (
       'FlightScheduleDetail', 'PRMMovementSummary' )
ORDER  BY Object_name(c.object_id),
          c.NAME

22. Calculates the size of individual database objects (in megabytes).

Description:

Below Query will be used to find the space used by Databases

Query:

SELECT sys.objects.NAME,
       Sum(reserved_page_count) * 8.0 / 1024
FROM   sys.dm_db_partition_stats,
       sys.objects
WHERE  sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP  BY sys.objects.NAME;

SELECT SCHEMA_NAME(so.schema_id) AS SchemaName

,SUM(ps.reserved_page_count) * 8.0 / 1024 AS SizeInMB

FROM sys.dm_db_partition_stats ps

JOIN sys.indexes i

ON i.object_id = ps.object_id

AND i.index_id = ps.index_id

JOIN sys.objects so

ON i.object_id = so.object_id

WHERE so.type = 'U'

GROUP BY so.schema_id

ORDER BY OBJECT_SCHEMA_NAME(so.schema_id), SizeInMB DESC

23. To Find the Index Health Status

Description:

To findout the indexes whose fragmentation level is greater than 40

percent and the page count > 1000. These indexes will definitely impact the performance of a query.

Note: Below are indexes Recommendation to “Rebuild”.

Query:

SELECT db_name(a.database_id) [Db Name]

,object_name(a.object_id) Table_Name

,a.index_id

,b.name

,a.avg_fragmentation_in_percent

,record_count,a.avg_fragment_size_in_pages,page_count,fragment_countFROM sys.dm_db_index_physical_stats (DB_ID(), null,NULL, NULL, 'DETAILED') AS a

JOIN sys.indexes AS b

ON a.object_id = b.object_id

AND a.index_id = b.index_id

where a.database_id = db_id()and

a.avg_fragmentation_in_percent>20/*and (OBJECT_NAME(b.object_id) like 'PX%' or OBJECT_NAME(b.object_id) like 'PM%')*/order by a.object_id

24. List Size of the Table with Row Counts

Description:

Below query will be used to list size of the table in MB with Row Counts and as well as the size of each table.

Query:

SELECT

s.Name AS SchemaName,

t.Name AS TableName,

p.rows AS RowCounts,

CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,

CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,

CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB

FROM sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

GROUP BY t.Name, s.Name, p.Rows

ORDER BY s.Name, t.Name

25. Returning row count aggregates for a query

Description:

The following example returns row count aggregate information (total rows, minimum rows, maximum rows and last rows) for queries.

Query:

SELECT qs.execution_count,

SUBSTRING(qt.text,qs.statement_start_offset/2 +1,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2

ELSE qs.statement_end_offset end -

qs.statement_start_offset

)/2

) AS query_text,

qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid,

qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE qt.text like '%SELECT%'

ORDER BY qs.execution_count DESC;

26.To find the Buffer Size of Database

Description:

Below query will be used to find the Buffer size of each databases.

Query:

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value

FROM sys.dm_os_performance_counters

WHERE RTRIM([object_name]) LIKE '%Buffer Manager'

AND counter_name = 'Database Pages';

;WITH src AS

(

SELECT

database_id, db_buffer_pages = COUNT_BIG(*)

FROM sys.dm_os_buffer_descriptors

--WHERE database_id BETWEEN 5 AND 32766

GROUP BY database_id

)

SELECT

[db_name] = CASE [database_id] WHEN 32767

THEN 'Resource DB'

ELSE DB_NAME([database_id]) END,

db_buffer_pages,

db_buffer_MB = db_buffer_pages / 128,

db_buffer_percent = CONVERT(DECIMAL(6,3),

db_buffer_pages * 100.0 / @total_buffer)

FROM src

ORDER BY db_buffer_MB DESC;

27.To Find out the List of Heap tables

Description:

A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap. Data is stored in the heap without specifying an order.

Query:

SELECT schema_name(T.schema_id),T.Name 'HEAP TABLE'

FROM sys.indexes I

INNER JOIN sys.tables T

ON I.object_id = T.object_id

WHERE I.type = 0 AND T.type = 'U'

28. Query Store space usage:

Description:

To check current, the Query Store size and limit execute the following statement in the user database.

Query:

SELECT current_storage_size_mb, max_storage_size_mb

FROM sys.database_query_store_options;

If the Query Store storage is full use the following statement to extend the storage.

ALTER DATABASE <database_name>

SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

29.To Set all Query Store options

Description:

You can set multiple Query Store options at once with a single ALTER DATABASE statement.

Syntax:

ALTER DATABASE <database name>

SET QUERY_STORE (

OPERATION_MODE = READ_WRITE,

CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),

DATA_FLUSH_INTERVAL_SECONDS = 3000,

MAX_STORAGE_SIZE_MB = 500,

INTERVAL_LENGTH_MINUTES = 15,

SIZE_BASED_CLEANUP_MODE = AUTO,

QUERY_CAPTURE_MODE = AUTO,

MAX_PLANS_PER_QUERY = 1000,

WAIT_STATS_CAPTURE_MODE = ON

);

30. Miscellaneous

i)To find out detailed information about Query Store status

SELECT * FROM sys.database_query_store_options;

ii)Setting Query Store interval

ALTER DATABASE <database_name>

SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

Note:

Arbitrary values are not allowed for INTERVAL_LENGTH_MINUTES. Use one of the following: 1, 5, 10, 15, 30, 60, or 1440 minutes.

iii)Cleaning up the space:

Description:

Query Store internal tables are created in the PRIMARY file group during database creation and that configuration cannot be changed later. If you are running out of space, you might want to clear older Query Store data by using the following statement.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

Note:

Alternatively, you might want to clear up only ad-hoc query data, since it is less relevant for query optimizations and plan analysis but takes up just as much space. (Query Provide Above 19. To Delete ad-hoc queries (Free up Space).

SQL DBA Insights

Monday 18 June 2018

SQL Server Query Store Using DMV

No comments:

Post a Comment