Performance DMV Queries in SQL Server T-SQL scripts


select *from sys.dm _ os _ wait _ stats

Used for checking wait resources in OS level

select *from sys.dm _ exec _ sessions

Check what sessions are currently active

select *from sys.dm _ exec _ requests

Check what requests are currently running


Select *from sys.dm_exec_query_stats

Returns aggregated performance statistics(CPU,RAM,IO) for a cached query plan. Returns one row per statement within the plan

Select *from sys.dm_exec_cached_plans – provides detailed information about a cached plan,
such as the number of times it has been used, its size, and so on. Returns a row for each cached plan


sys.dm_os_sql_text(s.sql_handle)--Details for sql text base on handle

Select *from sys.dm_exec_query_plan – returns in XML format the query plan, identified
by a plan_handle, for a SQL batch.


Select *from sys.dm_tran_locks--Returns locking info

Select *from sys.dm_os_wait_stats--Returns info for blocking

Select *from sys.dm_tran_session_transactions---Alternate to dbcc opentran and provides info at db level

Select *from sys.dm_tran_active_transactions--Provides currently running queries at session level

Select *from sys.dm_tran_database_transactions--Provides Databases level transactions and T log usage

select *from sys.dm_tran_active_snapshot_database_transactions--It returns records for all active transactions that either create or read row
versions from the version store in tempdb

select *from sys.dm_tran_currrent_snapshot--Provides single-column DMV returns the transaction_sequence_num of all the active transactions
in the version store relevant to the current transaction at the timethe current SNAPSHOT transaction started. No results are returned
if the current transaction is not a SNAPSHOT transaction.

select *from sys.dm_tran_transactions_snapshot--

Select *from sys.dm_tran_version_store--Provides version store for tempdb

select *from sys.dm_db_index_usage_stats----Provides index details,seeks,scans

select *from sys.dm_db_missing_index_details---provides details of missing indexes

Select *from sys.dm_db_missing_index_columns – a DMF that accepts an index_handle parameter and returns a table providing details of columns that would comprise the
suggested missing index

sys.dm_db_missing_index_group_stats – a DMV that returns detailed information pertaining to metrics on groups of missing indexes

select *from sys.dm_db_missing_index_groups – a DMV that provides details of missing indexes in a specific group

Select *from sys.dm_db_index_physical_stats DMV to investigate fragmentation in indexes and heaps, and to determine a rebuild/reorganize strategy based on real need

select *from sys.dm_db_partition_stats (database related) – returns disk space oriented statistics (row count, page counts, and so on) for each object in a partition

Select *from sys.dm_io_virtual_file_stats(NULL, NULL)--returns info for files(data,log) read,writes

Select *from sys.dm_os_wait_stats---Returns details about wait stats in server

Select *from sys.dm_os_performance_counters----Gives details for performance counters

Select *from sys.dm_os_sys_info---Returns system information

Select *from sys.dm_os_schedulers---Gives info on OS scheduler running on CPU

select *from sys.dm_os_sys_memory--Gives details for system Memory,sql server memory High\Low usage

select *from sys.dm_os_Process_memory--Give details on used ohysical memory and memory pressure if any

Select *from sys.dm_os_buffer_descriptors DMV returns cached pages for all user and system databases, including pages

Select *from sys.dm_os_latch_stats – stats on the low-level resource locks that SQL Server uses to lock physical pages in memory, and more.

-----------------------------------------------------------------
Connections, Sessions and Requests---------------Chapter 1
---------------------------------------

select *from sys.dm_exec_connections
Returns information about the connections established to this instance of SQL Server and the details of each connection

select *from sys.dm_exec_sessions
Check information about each user and internal system
session on a SQL Server instance including session settings, security, and cumulative
CPU, memory, and I/O usage,

select *from sys.dm_exec_requests
Provides a range of query execution statistics, such
as elapsed time, wait time, CPU time, and so on. It returns one row for every query currently executing.
Commands running such as insert,update,Delete,etc


select *from sys.dm_exec_sql_text
Returns the text of the SQL batch identified by a sql_handle

select *from sys.dm_exec_query_plan
Returns, in XML format, the query plan, identified by a plan_handle

-----------------------------------------------------
-- Get a count of SQL connections by IP address
-------------
SELECT dec.client_net_address ,
des.program_name ,des.login_time,
des.host_name ,
--des.login_name ,
COUNT(dec.session_id) AS connection_count
FROM sys.dm_exec_sessions AS des
INNER JOIN sys.dm_exec_connections AS dec
ON des.session_id = dec.session_id
-- WHERE LEFT(des.host_name, 2) = 'WK'
GROUP BY dec.client_net_address ,
des.program_name ,
des.host_name,des.login_time
-- des.login_name
-- HAVING COUNT(dec.session_id) > 1
ORDER BY des.program_name,
dec.client_net_address ;
------------------------------------------------------
--Get list of who connected via SSMS
-------------
SELECT dec.client_net_address ,
des.host_name ,
dest.text
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_connections dec
ON des.session_id = dec.session_id
CROSS APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
WHERE des.program_name LIKE 'Microsoft SQL Server Management Studio%'
ORDER BY des.program_name ,
dec.client_net_address
-----------------------------------------------------------------
----Get list of who is connected and what is he running
select b.session_id,a.host_name,a.program_name,a.login_name,db_name(c.dbid) as dbname,
c.text,query_plan from sys.dm_exec_sessions a
join sys.dm_exec_connections b on a.session_id=b.session_id
join sys.dm_exec_query_stats d on d.sql_handle=b.most_recent_sql_handle
cross apply sys.dm_exec_sql_text(b.most_recent_sql_handle) c
cross apply sys.dm_exec_query_plan(d.plan_handle)
-------------------------------------------------------
---Login with more than one session
------------
SELECT login_name ,
COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY login_name
ORDER BY login_name
------------------------------------------------------
--Identify Inactive sessions
------------------
DECLARE @days_old SMALLINT
SELECT @days_old = 5
SELECT des.session_id ,
des.login_time ,
des.last_request_start_time ,
des.last_request_end_time ,
des.[status] ,
des.[program_name] ,
des.cpu_time ,
des.total_elapsed_time ,
des.memory_usage ,
des.total_scheduled_time ,
des.total_elapsed_time ,
des.reads ,
des.writes ,
des.logical_reads ,
des.row_count ,
des.is_user_process
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_tran_session_transactions dtst
ON des.session_id = dtst.session_id
WHERE des.is_user_process = 1
AND DATEDIFF(dd, des.last_request_end_time, GETDATE()) > @days_old
AND des.status != 'Running'
ORDER BY des.last_request_end_time
------------------------------------------------------------------
---Identify running queries\percent complete\part of currently running query\CPU time.
----------------------------------
SELECT der.statement_start_offset ,
der.statement_end_offset ,db_name(der.database_id) as database_name,start_time,der.status,
last_wait_type,wait_time,der.cpu_time
percent_complete,
SUBSTRING(dest.text, der.statement_start_offset / 2,
( CASE WHEN der.statement_end_offset = -1
THEN DATALENGTH(dest.text)
ELSE der.statement_end_offset
END - der.statement_start_offset ) / 2)
AS statement_executing ,
dest.text AS [full statement code]
FROM sys.dm_exec_requests der
INNER JOIN sys.dm_exec_sessions des
ON des.session_id = der.session_id
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest
WHERE des.is_user_process = 1
AND der.session_id <> @@spid
ORDER BY der.session_id ;
--------------------------------------------------------------------
----Identify current running scripts and sessions on databases-------------Chapter 2
-----------------------------
select s.session_id,db_name (s.database_id) as [database],text as [current running script],
c.connect_time,c.net_transport,c.client_net_address,
s.host_name,s.program_name,s.login_name,r.command,p.query_plan
from sys.dm_exec_connections c
inner join sys.dm_exec_sessions s on s.session_id=c.session_id
inner join sys.dm_exec_requests r on s.session_id=r.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) t
cross apply sys.dm_exec_query_plan(r.plan_handle) p
WHERE s.is_user_process = 1
----------------------------------------------------------------------------
-----Script better than sp_who2
---------
SELECT des.session_id ,
des.status ,
des.login_name ,
des.[HOST_NAME] ,
der.blocking_session_id ,
DB_NAME(der.database_id) AS database_name ,
der.command ,
des.cpu_time ,
des.reads ,
des.writes ,
dec.last_write ,
des.[program_name] ,
der.wait_type ,
der.wait_time ,
der.last_wait_type ,
der.wait_resource ,
CASE des.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level ,
OBJECT_NAME(dest.objectid, der.database_id) AS OBJECT_NAME ,text as [Query running],
deqp.query_plan
FROM sys.dm_exec_sessions des(no lock)
LEFT JOIN sys.dm_exec_requests der()
ON des.session_id = der.session_id
LEFT JOIN sys.dm_exec_connections dec
ON des.session_id = dec.session_id
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp
WHERE des.session_id <> @@SPID
ORDER BY des.session_id
--------------------------------------------------------------------------------------------------------------
Query Plans----Chapter 3
-------------------
sys.dm_exec_query_stats – returns aggregated performance statistics for a
cached query plan. Returns one row per statement within the plan
-------------------
sys.dm_exec_procedure_stats – returns aggregated performance statistics
for cached stored procedures (SQL Server 2008 only). Returns one row per
stored procedure.
-------------------
sys.dm_exec_cached_plans – provides detailed information about a cached plan,
such as the number of times it has been used, its size, and so on. Returns a row for each
cached plan
-------------------
sys.dm_exec_query_optimizer_info – returns statistics regarding the operation
of the query optimizer, to identify any potential optimization problems. For example,
you can find out how many queries have been optimized since the last time the server
was restarted.
--------------------
we need to pass the identifier for that plan batch, the plan_handle, to one of
the DMFs below.
--------
sys.dm_exec_query_plan – returns in XML format the query plan, identified
by a plan_handle, for a SQL batch.
-------
sys.dm_exec_text_query_plan – returns in text format the query plan,
identified by a plan_handle, for a SQL batch or, via the use of this DMF's offset
columns, a specific statement within that batch.
---------
sys.dm_exec_plan_attributes – provides information about various attributes of
a query plan, identified by a plan_handle, such as the number of queries currently
using a given execution plan. It returns one row for each attribute.
-----------
Query to check Usecount(No.of time plan used from cache),Execution count(No.of times query executed),Query,Query Plan
---------------------
select usecounts,execution_count,text,query_plan from sys.dm_exec_cached_plans p
inner join sys.dm_exec_query_stats s on s.plan_handle=p.plan_handle
cross apply sys.dm_exec_sql_text(s.sql_handle)
cross apply sys.dm_exec_query_plan(s.plan_handle)
--where execution_count>usecounts
order by usecounts asc
-----------------
Query to check which plans are being frequently cached
----------------
SELECT top 10
decp.usecounts ,
decp.cacheobjtype ,
decp.objtype ,
deqp.query_plan ,
dest.text
FROM sys.dm_exec_cached_plans decp
CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
ORDER BY usecounts DESC ;--order by usecounts asc(to check which plans are not caching correctly)
----------------
Query to check ad-hoc plans
-------
SELECT TOP ( 100 )
[text] ,
cp.size_in_bytes,query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
cross apply sys.dm_exec_query_plan(plan_handle)
WHERE cp.cacheobjtype = 'Compiled Plan'
AND cp.objtype = 'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC ;
--------------------------
Query to check expensive stored procedures
---------
SELECT TOP ( 10)
p.name AS [SP Name] ,
deps.total_logical_reads AS [TotalLogicalReads] ,
deps.total_logical_reads / deps.execution_count AS [AvgLogicalReads] ,
deps.execution_count ,
ISNULL(deps.execution_count / DATEDIFF(Second, deps.cached_time,
GETDATE()), 0) AS [Calls/Second] ,
deps.total_elapsed_time ,
deps.total_elapsed_time / deps.execution_count AS [avg_elapsed_time] ,
deps.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats
AS deps ON p.[object_id] = deps.[object_id]
WHERE deps.database_id = DB_ID()
ORDER BY deps.total_logical_reads DESC ;
----------------------------------------
Query to check blocking,locks,executing query
------------
SELECT DTL.[request_session_id] AS [session_id] ,
DB_NAME(DTL.[resource_database_id]) AS [Database] ,
DTL.resource_type ,
CASE WHEN DTL.resource_type IN ( 'DATABASE', 'FILE', 'METADATA' )
THEN DTL.resource_type
WHEN DTL.resource_type = 'OBJECT'
THEN OBJECT_NAME(DTL.resource_associated_entity_id,
DTL.[resource_database_id])
WHEN DTL.resource_type IN ( 'KEY', 'PAGE', 'RID' )
THEN ( SELECT OBJECT_NAME([object_id])
FROM sys.partitions
WHERE sys.partitions.hobt_id =
DTL.resource_associated_entity_id
)
ELSE 'Unidentified'
END AS [Parent Object] ,
DTL.request_mode AS [Lock Type] ,
DTL.request_status AS [Request Status] ,
DER.[blocking_session_id] ,
DES.[login_name] ,
CASE DTL.request_lifetime
WHEN 0 THEN DEST_R.TEXT
ELSE DEST_C.TEXT
END AS [Statement]
FROM sys.dm_tran_locks DTL
LEFT JOIN sys.[dm_exec_requests] DER
ON DTL.[request_session_id] = DER.[session_id]
INNER JOIN sys.dm_exec_sessions DES
ON DTL.request_session_id = DES.[session_id]
INNER JOIN sys.dm_exec_connections DEC
ON DTL.[request_session_id] = DEC.[most_recent_session_id]
OUTER APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle])
AS DEST_C
OUTER APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_R
WHERE DTL.[resource_database_id] = DB_ID()
AND DTL.[resource_type] NOT IN ( 'DATABASE', 'METADATA' )
ORDER BY DTL.[request_session_id] ;

-------------------------------------------------------------------
Query to check blocked resource and locks,root blocking,multi blocking
---------------------
select *from sys.dm_os_waiting_tasks a
inner join sys.dm_tran_locks b on a.resource_address=b.lock_owner_address
inner join sys.dm_exec_requests c on a.session_id=c.session_id
inner join sys.dm_exec_sessions d on a.blocking_session_id=d.session_id
cross apply sys.dm_exec_sql_text(c.sql_handle)
cross apply sys.dm_exec_query_plan(c.plan_handle)
--------------------------------------------------------------------
Query to check blocking at granular level
---------------------------
SELECT DTL.[resource_type] AS [resource type] ,
CASE WHEN DTL.[resource_type] IN ( 'DATABASE', 'FILE', 'METADATA' )
THEN DTL.[resource_type]
WHEN DTL.[resource_type] = 'OBJECT'
THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.[resource_type] IN ( 'KEY', 'PAGE', 'RID' )
THEN ( SELECT OBJECT_NAME([object_id])
FROM sys.partitions
WHERE sys.partitions.[hobt_id] =
DTL.[resource_associated_entity_id]
)
ELSE 'Unidentified'
END AS [Parent Object] ,
DTL.[request_mode] AS [Lock Type] ,
DTL.[request_status] AS [Request Status] ,
DOWT.[wait_duration_ms] AS [wait duration ms] ,
DOWT.[wait_type] AS [wait type] ,
DOWT.[session_id] AS [blocked session id] ,
DES_blocked.[login_name] AS [blocked_user] ,
SUBSTRING(dest_blocked.text, der.statement_start_offset / 2,
( CASE WHEN der.statement_end_offset = -1
THEN DATALENGTH(dest_blocked.text)
ELSE der.statement_end_offset
END - der.statement_start_offset ) / 2 )AS [blocked_command] ,
DOWT.[blocking_session_id] AS [blocking session id] ,
DES_blocking.[login_name] AS [blocking user] ,
DEST_blocking.[text] AS [blocking command] ,
DOWT.resource_description AS [blocking resource detail]
FROM sys.dm_tran_locks DTL
INNER JOIN sys.dm_os_waiting_tasks DOWT
ON DTL.lock_owner_address = DOWT.resource_address
INNER JOIN sys.[dm_exec_requests] DER
ON DOWT.[session_id] = DER.[session_id]
INNER JOIN sys.dm_exec_sessions DES_blocked
ON DOWT.[session_id] = DES_Blocked.[session_id]
INNER JOIN sys.dm_exec_sessions DES_blocking
ON DOWT.[blocking_session_id] = DES_Blocking.[session_id]
INNER JOIN sys.dm_exec_connections DEC
ON DTL.[request_session_id] = DEC.[most_recent_session_id]
CROSS APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle])
AS DEST_Blocking
CROSS APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_Blocked
WHERE DTL.[resource_database_id] = DB_ID()
---------------------------------------------------------------------------------
Query to check active queries running on server
---------------------------------
SELECT DTAT.transaction_id ,
DTAT.[name] ,
DTAT.transaction_begin_time ,
CASE DTAT.transaction_type
WHEN 1 THEN 'Read/write'
WHEN 2 THEN 'Read-only'
WHEN 3 THEN 'System'
WHEN 4 THEN 'Distributed'
END AS transaction_type ,
CASE DTAT.transaction_state
WHEN 0 THEN 'Not fully initialized'
WHEN 1 THEN 'Initialized, not started'
WHEN 2 THEN 'Active'
WHEN 3 THEN 'Ended' -- only applies to read-only transactions
WHEN 4 THEN 'Commit initiated'-- distributed transactions only
WHEN 5 THEN 'Prepared, awaiting resolution'
WHEN 6 THEN 'Committed'
WHEN 7 THEN 'Rolling back'
WHEN 8 THEN 'Rolled back'
END AS transaction_state ,
CASE DTAT.dtc_state
WHEN 1 THEN 'Active'
WHEN 2 THEN 'Prepared'
WHEN 3 THEN 'Committed'
WHEN 4 THEN 'Aborted'
WHEN 5 THEN 'Recovered'
END AS dtc_state
FROM sys.dm_tran_active_transactions DTAT
INNER JOIN sys.dm_tran_session_transactions DTST
ON DTAT.transaction_id = DTST.transaction_id
WHERE [DTST].[is_user_transaction] = 1
ORDER BY DTAT.transaction_begin_time
----------------------------------------------------------------------------
Query to check log usage,query text and its plan.
-------------------------------------------
SELECT DTST.[session_id],
DES.[login_name] AS [Login Name],
DB_NAME (DTDT.database_id) AS [Database],
DTDT.[database_transaction_begin_time] AS [Begin Time],
-- DATEDIFF(ms,DTDT.[database_transaction_begin_time], GETDATE()) AS [Durationms],
CASE DTAT.transaction_type
WHEN 1 THEN 'Read/write'
WHEN 2 THEN 'Read-only'
WHEN 3 THEN 'System'
WHEN 4 THEN 'Distributed'
END AS [Transaction Type],
CASE DTAT.transaction_state
WHEN 0 THEN 'Not fully initialized'
WHEN 1 THEN 'Initialized, not started'
WHEN 2 THEN 'Active'
WHEN 3 THEN 'Ended'
WHEN 4 THEN 'Commit initiated'
WHEN 5 THEN 'Prepared, awaiting resolution'
WHEN 6 THEN 'Committed'
WHEN 7 THEN 'Rolling back'
WHEN 8 THEN 'Rolled back'
END AS [Transaction State],
DTDT.[database_transaction_log_record_count] AS [Log Records],
DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used],
DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd],
DEST.[text] AS [Last Transaction Text],
DEQP.[query_plan] AS [Last Query Plan]
FROM sys.dm_tran_database_transactions DTDT
INNER JOIN sys.dm_tran_session_transactions DTST
ON DTST.[transaction_id] = DTDT.[transaction_id]
INNER JOIN sys.[dm_tran_active_transactions] DTAT
ON DTST.[transaction_id] = DTAT.[transaction_id]
INNER JOIN sys.[dm_exec_sessions] DES
ON DES.[session_id] = DTST.[session_id]
INNER JOIN sys.dm_exec_connections DEC
ON DEC.[session_id] = DTST.[session_id]
LEFT JOIN sys.dm_exec_requests DER
ON DER.[session_id] = DTST.[session_id]
CROSS APPLY sys.dm_exec_sql_text (DEC.[most_recent_sql_handle]) AS DEST
OUTER APPLY sys.dm_exec_query_plan (DER.[plan_handle]) AS DEQP
ORDER BY DTDT.[database_transaction_log_bytes_used] DESC;
-- ORDER BY [Duration ms] DESC;
--------------------------------------------------------------------------------------
Script to check Tempdb version store and read committed snapsht isolation level
-------------------------------------
SELECT DTTS.[transaction_sequence_num] ,
trx_current.[session_id] AS current_session_id ,
DES_current.[login_name] AS [current session login] ,
DEST_current.text AS [current session command] ,
DTTS.[snapshot_sequence_num] ,
trx_existing.[session_id] AS existing_session_id ,
DES_existing.[login_name] AS [existing session login] ,
DEST_existing.text AS [existing session command]
FROM sys.dm_tran_transactions_snapshot DTTS
INNER JOIN sys.[dm_tran_active_snapshot_database_transactions]
trx_current
ON DTTS.[transaction_sequence_num] =
trx_current.[transaction_sequence_num]
INNER JOIN sys.[dm_exec_connections] DEC_current
ON trx_current.[session_id] =
DEC_current.[most_recent_session_id]
INNER JOIN sys.[dm_exec_sessions] DES_current
ON DEC_current.[most_recent_session_id] =
DES_current.[session_id]
INNER JOIN sys.[dm_tran_active_snapshot_database_transactions]
trx_existing
ON DTTS.[snapshot_sequence_num] =
trx_existing.[transaction_sequence_num]
INNER JOIN sys.[dm_exec_connections] DEC_existing
ON trx_existing.[session_id] =
DEC_existing.[most_recent_session_id]
INNER JOIN sys.[dm_exec_sessions] DES_existing
ON DEC_existing.[most_recent_session_id] =
DES_existing.[session_id]
CROSS APPLY sys.[dm_exec_sql_text]
(DEC_current.[most_recent_sql_handle]) DEST_current
CROSS APPLY sys.[dm_exec_sql_text]
(DEC_existing.[most_recent_sql_handle]) DEST_existing
ORDER BY DTTS.[transaction_sequence_num] ,
DTTS.[snapshot_sequence_num] ;
------------------------------------------------------------------------------------
Scripts to check which db is causing version store(Tempdb space)
--------------------------
SELECT DB_NAME(DTVS.database_id) AS [Database Name] ,
DTVS.[transaction_sequence_num] ,
DTVS.[version_sequence_num] ,
CASE DTVS.[status]
WHEN 0 THEN '1'
WHEN 1 THEN '2'
END AS [pages] ,
DTVS.[record_length_first_part_in_bytes]
+ DTVS.[record_length_second_part_in_bytes] AS [record length (bytes)]
FROM sys.dm_tran_version_store DTVS
ORDER BY DB_NAME(DTVS.database_id) ,
DTVS.transaction_sequence_num ,
DTVS.version_sequence_num

SELECT DB_NAME(DTVS.[database_id]) ,
SUM(DTVS.[record_length_first_part_in_bytes]
+ DTVS.[record_length_second_part_in_bytes]) AS [total store bytes
consumed]
FROM sys.dm_tran_version_store DTVS
GROUP BY DB_NAME(DTVS.[database_id]) ;

-----------------------------------------------------------------------------------------
Script to check highest consuming version store record within tempdb
-------------
WITH version_store ( [rowset_id], [bytes consumed] )
AS ( SELECT TOP 1
[rowset_id] ,
SUM([record_length_first_part_in_bytes]
+ [record_length_second_part_in_bytes])
AS [bytes consumed]
FROM sys.dm_tran_version_store
GROUP BY [rowset_id]
ORDER BY SUM([record_length_first_part_in_bytes]
+ [record_length_second_part_in_bytes])
)
SELECT VS.[rowset_id] ,
VS.[bytes consumed] ,
DB_NAME(DTVS.[database_id]) AS [database name] ,
DTASDT.[session_id] AS session_id ,
DES.[login_name] AS [session login] ,
DEST.text AS [session command]
FROM version_store VS
INNER JOIN sys.[dm_tran_version_store] DTVS
ON VS.rowset_id = DTVS.[rowset_id]
INNER JOIN sys.[dm_tran_active_snapshot_database_transactions]
DTASDT
ON DTVS.[transaction_sequence_num] =
DTASDT.[transaction_sequence_num]
INNER JOIN sys.dm_exec_connections DEC
ON DTASDT.[session_id] = DEC.[most_recent_session_id]
INNER JOIN sys.[dm_exec_sessions] DES
ON DEC.[most_recent_session_id] = DES.[session_id]
CROSS APPLY sys.[dm_exec_sql_text](DEC.[most_recent_sql_handle])
DEST ;
-----------------------------------------------------------------------------------
Check which query,session,user using version store
--------------------
SELECT DTTS.[transaction_sequence_num] ,
trx_current.[session_id] AS current_session_id ,
DES_current.[login_name] AS [current session login] ,
DEST_current.text AS [current session command] ,
DTTS.[snapshot_sequence_num] ,
trx_existing.[session_id] AS existing_session_id ,
DES_existing.[login_name] AS [existing session login] ,
DEST_existing.text AS [existing session command]
FROM sys.dm_tran_transactions_snapshot DTTS
INNER JOIN sys.[dm_tran_active_snapshot_database_transactions]
trx_current
ON DTTS.[transaction_sequence_num] =
trx_current.[transaction_sequence_num]
INNER JOIN sys.[dm_exec_connections] DEC_current
ON trx_current.[session_id] =
DEC_current.[most_recent_session_id]
INNER JOIN sys.[dm_exec_sessions] DES_current
ON DEC_current.[most_recent_session_id] =
DES_current.[session_id]
INNER JOIN sys.[dm_tran_active_snapshot_database_transactions]
trx_existing
ON DTTS.[snapshot_sequence_num] =
trx_existing.[transaction_sequence_num]
INNER JOIN sys.[dm_exec_connections] DEC_existing
ON trx_existing.[session_id] =
DEC_existing.[most_recent_session_id]
INNER JOIN sys.[dm_exec_sessions] DES_existing
ON DEC_existing.[most_recent_session_id] =
DES_existing.[session_id]
CROSS APPLY sys.[dm_exec_sql_text]
(DEC_current.[most_recent_sql_handle]) DEST_current
CROSS APPLY sys.[dm_exec_sql_text]
(DEC_existing.[most_recent_sql_handle]) DEST_existing
ORDER BY DTTS.[transaction_sequence_num] ,
DTTS.[snapshot_sequence_num] ;
-------------------------------------------------------------------------------
Check which query,session,user using version store
-----------------------------------
WITH version_store ( [rowset_id], [bytes consumed] )
AS ( SELECT TOP 1
[rowset_id] ,
SUM([record_length_first_part_in_bytes]
+ [record_length_second_part_in_bytes])
AS [bytes consumed]
FROM sys.dm_tran_version_store
GROUP BY [rowset_id]
ORDER BY SUM([record_length_first_part_in_bytes]
+ [record_length_second_part_in_bytes])
)
SELECT VS.[rowset_id] ,
VS.[bytes consumed] ,
DB_NAME(DTVS.[database_id]) AS [database name] ,
DTASDT.[session_id] AS session_id ,
DES.[login_name] AS [session login] ,
DEST.text AS [session command]
FROM version_store VS
INNER JOIN sys.[dm_tran_version_store] DTVS
ON VS.rowset_id = DTVS.[rowset_id]
INNER JOIN sys.[dm_tran_active_snapshot_database_transactions]
DTASDT
ON DTVS.[transaction_sequence_num] =
DTASDT.[transaction_sequence_num]
INNER JOIN sys.dm_exec_connections DEC
ON DTASDT.[session_id] = DEC.[most_recent_session_id]
INNER JOIN sys.[dm_exec_sessions] DES
ON DEC.[most_recent_session_id] = DES.[session_id]
CROSS APPLY sys.[dm_exec_sql_text](DEC.[most_recent_sql_handle])
DEST ;
-------------------------------------------------------------------------------
                    Chapter ---------5
------------
Check index details,seeks,scans
--------------
SELECT OBJECT_NAME(ddius.[object_id], ddius.database_id) AS [object_name] ,
ddius.index_id ,
ddius.user_seeks ,
ddius.user_scans ,
ddius.user_lookups ,
ddius.user_seeks + ddius.user_scans + ddius.user_lookups
AS user_reads ,
ddius.user_updates AS user_writes ,
ddius.last_user_scan ,
ddius.last_user_update
FROM sys.dm_db_index_usage_stats ddius
WHERE ddius.database_id > 4 -- filter out system tables
AND OBJECTPROPERTY(ddius.object_id, 'IsUserTable') = 1
AND ddius.index_id > 0 -- filter out heaps
ORDER BY ddius.user_scans DESC
----------------------------------------
List unused indexes since last sql server restart or database connection started
---------------------
-- List unused indexes
SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] ,
i.name
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE i.index_id NOT IN ( SELECT ddius.index_id
FROM sys.dm_db_index_usage_stats AS ddius
WHERE ddius.[object_id] = i.[object_id]
AND i.index_id = ddius.index_id
AND database_id = DB_ID() )
AND o.[type] = 'U'
ORDER BY OBJECT_NAME(i.[object_id]) ASC ;
---------------------------------------------
Check indexes that are being maintained but not used
--------------
SELECT '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']'
AS [statement] ,
i.[name] AS [index_name] ,
ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups]
AS [user_reads] ,
ddius.[user_updates] AS [user_writes] ,
SUM(SP.rows) AS [total_rows]
FROM sys.dm_db_index_usage_stats ddius
INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id]
AND i.[index_id] = ddius.[index_id]
INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id]
AND SP.[index_id] = ddius.[index_id]
INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]
INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
WHERE ddius.[database_id] = DB_ID() -- current database only
AND OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
AND ddius.[index_id] > 0
GROUP BY su.[name] ,
o.[name] ,
i.[name] ,
ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] ,
ddius.[user_updates]
HAVING ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
ORDER BY ddius.[user_updates] DESC ,
su.[name] ,
o.[name] ,
i.[name ]
-----------------------------------------------
Check indexes that are being rarely used
------------------------------
SELECT OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
FROM sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK )
ON ddius.[object_id] = i.[object_id]
AND i.index_id = ddius.index_id
WHERE OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
AND ddius.database_id = DB_ID()
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER BY [Difference] DESC ,
[Total Writes] DESC
------------------------------------------------
check indexes which had higher reads and rarely used for writes
---------------------------
SELECT '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']'
AS [statement] ,
i.[name] AS [index_name] ,
ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups]
AS [user_reads] ,
ddius.[user_updates] AS [user_writes] ,
ddios.[leaf_insert_count] ,
ddios.[leaf_delete_count] ,
ddios.[leaf_update_count] ,
ddios.[nonleaf_insert_count] ,
ddios.[nonleaf_delete_count] ,
ddios.[nonleaf_update_count]
FROM sys.dm_db_index_usage_stats ddius
INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id]
AND i.[index_id] = ddius.[index_id]
INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id]
AND SP.[index_id] = ddius.[index_id]
INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]
INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
INNER JOIN sys.[dm_db_index_operational_stats](DB_ID(), NULL, NULL,
NULL)
AS ddios
ON ddius.[index_id] = ddios.[index_id]
AND ddius.[object_id] = ddios.[object_id]
AND SP.[partition_number] = ddios.[partition_number]
AND ddius.[database_id] = ddios.[database_id]
WHERE OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
AND ddius.[index_id] > 0
AND ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
ORDER BY ddius.[user_updates] DESC ,
su.[name] ,
o.[name] ,
i.[name]
----------------------------------------------------------------
Script to check missing indexes
---------------------------------------------
SELECT OBJECT_NAME(ddios.object_id, ddios.database_id) AS object_name ,
i.name AS index_name ,
ddios.index_id ,
ddios.partition_number ,
ddios.page_lock_wait_count ,
ddios.page_lock_wait_in_ms ,
CASE WHEN DDMID.database_id IS NULL THEN 'N'
ELSE 'Y'
END AS missing_index_identified
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
INNER JOIN sys.indexes i ON ddios.object_id = i.object_id
AND ddios.index_id = i.index_id
LEFT OUTER JOIN ( SELECT DISTINCT
database_id ,
object_id
FROM sys.dm_db_missing_index_details
) AS DDMID ON DDMID.database_id = ddios.database_id
AND DDMID.object_id = ddios.object_id
WHERE ddios.page_lock_wait_in_ms > 0
ORDER BY ddios.page_lock_wait_count DESC
---------------------------------------------
Script to check which indexes could have been used
--------------------
SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )
AS [index_advantage] ,
dbmigs.last_user_seek ,
dbmid.[statement] AS [Database.Schema.Table] ,
dbmid.equality_columns ,
dbmid.inequality_columns ,
dbmid.included_columns ,
dbmigs.unique_compiles ,
dbmigs.user_seeks ,
dbmigs.avg_total_user_cost ,
dbmigs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )
INNER JOIN sys.dm_db_missing_index_groups AS dbmig WITH ( NOLOCK )
ON dbmigs.group_handle = dbmig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS dbmid WITH ( NOLOCK )
ON dbmig.index_handle = dbmid.index_handle
WHERE dbmid.[database_id] = DB_ID()
ORDER BY index_advantage DESC ;
-----------------------------------------------------------------
-------Chapter 6----Physical disk IO
--------
The write-ahead
logging mechanism used by SQL Server means that the details of INSERT, UPDATE and
DELETE transactions are always first written to the transaction log (LDF) file on disk.
A data page that contains data that has been committed to the transaction log file but
not to the data (MDF) file is referred to as a "dirty page." A process called the "lazy writer"
manages the writing of dirty pages to physical disk when there is time, or when no more
data can be placed into the cache, while another process, called the "checkpoint" process,
----------------------------------------
Script to row count for each table in a database
-----------------
SELECT object_schema_name(ddps.object_id) +
'.' + OBJECT_NAME(ddps.object_id) AS name ,
SUM(ddps.row_count) AS row_count
FROM sys.dm_db_partition_stats AS ddps
JOIN sys.indexes ON indexes.object_id = ddps.object_id
AND indexes.index_id = ddps.index_id
WHERE indexes.type_desc IN ( 'CLUSTERED', 'HEAP','NONCLUSTERED' )
and objectproperty(ddps.object_id,'IsMSShipped') = 0
GROUP BY ddps.object_id
order by row_count desc
--------------------------------------------
------------Chapter 7--------OS level DMV`s
------------
Scrip to check most common wait stats
----------
SELECT TOP 3
wait_type ,
waiting_tasks_count ,
wait_time_ms / 1000.0 AS wait_time_sec ,
CASE WHEN waiting_tasks_count = 0 THEN NULL
ELSE wait_time_ms / 1000.0 / waiting_tasks_count
END AS avg_wait_time_sec ,
max_wait_time_ms / 1000.0 AS max_wait_time_sec ,
( wait_time_ms - signal_wait_time_ms ) / 1000.0 AS resource_wait_time_sec
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN --tasks that are actually good or expected
--to be waited on
( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'WAITFOR' )
ORDER BY waiting_tasks_count DESC
---------------------------------------------------
Scrip to check longest wait stats
----------
-- Isolate top waits for server instance since last restart
-- or statistics clear
WITH Waits
AS ( SELECT wait_type ,
wait_time_ms
/ 1000. AS wait_time_sec ,
100. * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS pct ,
ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE' )
)
SELECT wait_type ,
CAST(wait_time_sec AS DECIMAL(12, 2)) AS wait_time_sec ,
CAST(pct AS DECIMAL(12, 2)) AS wait_time_percentage
FROM Waits
WHERE pct > 1
ORDER BY wait_time_sec DESC
-----------------------------------
Script to check OS bit edition(32\64 bit)
---------------
--Determine if this is a 32- or 64-bit SQL Server edition
DECLARE @ServerAddressing AS TINYINT
SELECT @serverAddressing = CASE WHEN CHARINDEX('64',
CAST(SERVERPROPERTY('Edition')
AS VARCHAR(100))) > 0
THEN 64
ELSE 32
END ;
SELECT cpu_count / hyperthread_ratio AS SocketCount ,
physical_memory_kb / 1024 / 1024 AS physical_memory_mb ,
virtual_memory_kb / 1024 / 1024 AS sql_max_virtual_memory_mb ,
-- same with other bpool columns as they are page oriented.
-- Multiplying by 8 takes it to 8K, then / 1024 to convert to mb
committed_kb * 8 / 1024 AS buffer_pool_committed_mb ,
--64 bit OS does not have limitations with addressing as 32 did
CASE WHEN @serverAddressing = 32
THEN CASE WHEN virtual_memory_kb / 1024 /
( 2048 * 1024 ) < 1
THEN 'off'
ELSE 'on'
END
ELSE 'N/A on 64 bit'
END AS [/3GB switch]
FROM  sys.dm_os_sys_info
-------------------------------------
Scrip to check Memory usage DB wise and object wise
----------------------
SELECT DB_NAME(database_id) AS [Database Name] ,
COUNT(*) * 8 / 1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- exclude system databases
AND database_id <> 32767 -- exclude ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC ;
-- Breaks down buffers by object (table, index) in the buffer pool
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName] ,
p.index_id ,
COUNT(*) / 128 AS [Buffer size(MB)] ,
COUNT(*) AS [Buffer_count]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors
AS b ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100 -- exclude system objects
GROUP BY p.[object_id] ,
p.index_id
ORDER BY buffer_count DESC ;
---------------------------------------------
Script to check latch waits
--------------------
SELECT latch_class ,
waiting_requests_count AS waitCount ,
wait_time_ms AS waitTime ,
max_wait_time_ms AS maxWait
FROM sys.dm_os_latch_stats
ORDER BY wait_time_ms DESC
------------------------

Sending query results to HTML Format through e-mail in SQL Server

--html--

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)


SET @xml = CAST(( select User ID AS 'td','', Name AS 'td','',
       SECurity AS 'td','', LST_LOGIN AS 'td','',LOGIN_FAIL AS 'td','',
       LOGIN_inactive AS 'td'
FROM  DBA..table_Nmae
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
--select @xml


SET @body ='<html><body><H3>DBA_audit permissions Monthly Report</H3>
<table border = 1>
<tr>
<th> User ID </th> <th> Name </th> <th> SECurity </th> <th> LST_LOGIN </th> <th> LOGIN_FAIL </th>
<th> LOGIN_inactive </th>  </tr>' 


SET @body = @body + @xml +'</table></body></html>'


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ajayMail',
@body = @body,
@body_format ='HTML',
@recipients = 'ajeyudub@sqlajay.com',
@subject = 'DBA_audit permissions Monthly Report' ;

Sending query results to Excel through e-mail in SQL Server

declare @body1 varchar(4000)
declare @body2 varchar(4000)
DECLARE @tab char(1) = CHAR(9)
DECLARE @CRLF char(2)
SELECT @CRLF=CHAR(13)+CHAR(10)

--@query_result_separator=

set @body1 = 'Please find the attached list. '

  +@CRLF+ N' Thanks, SQL DBA Team.'

EXEC msdb.dbo.sp_send_dbmail

    @profile_name='ajaymail',

    @recipients='security@sqlajay.com',
@blind_copy_recipients='dbateam@sqlajay.com',

    @subject = 'DBA database Monthly Report.',

    @body = @body1,

    @body_format = 'HTML',
             
    @query = 'Select USER_ID AS ID, NAME as Username, SECURITY as SecurityPermission, LST_LOGIN AS LastLogin from DBA..table_name',

    @query_result_header = 1,
@query_result_separator =@tab,

    @exclude_query_output = 1,

    @append_query_error = 1,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = 'DBA.csv',

    @query_result_no_padding = 1