DMV for lock and memory usage

View the lock applied to the spid connecting to the current database

SET transaction isolation level read uncommitted
Select db_name (resource_database_id) as databasename
, Request_session_id
, Resource_type
, Case
When resource_type = 'object'
Then object_name (resource_associated_entity_id)
When resource_type in ('key', 'page', 'rid ')
Then (select object_name (object_id)
From SYS. partitions P
Where p. hobt_id = L. resource_associated_entity_id)
End as resource_type_name
, Request_status
, Request_mode
From SYS. dm_tran_locks L
Where request_session_id! @ Spid
Order by request_session_id

Result:

If you want to view more locks, adjust the where condition.

 

View idle sessions without closing the transaction

 

SET transaction isolation level read uncommitted
Select es. session_id, es. login_name, es. host_name, Est. Text
, CN. last_read, CN. last_write, es. program_name
From SYS. dm_exec_sessions es
Inner join SYS. dm_tran_session_transactions St
On es. session_id = ST. session_id
Inner join SYS. dm_exec_connections CN
On es. session_id = cn. session_id
Cross apply SYS. dm_exec_ SQL _text (CN. most_recent_ SQL _handle) est
Left Outer Join SYS. dm_exec_requests er
On st. session_id = ER. session_id
And er. session_id is null

 

View blocked statements and their waiting time

 

SET transaction isolation level read uncommitted
Select
Waits. wait_duration_ms/1000 as waitinseconds
, Blocking. session_id as blockingsessionid
, Db_name (blocked. database_id) as databasename
, Sess. login_name as blockinguser
, Sess. host_name as blockinglocation
, Blockingsql. Text as blockingsql
, Blocked. session_id as blockedsessionid
, Blockedsess. login_name as blockeduser
, Blockedsess. host_name as blockedlocation
, Blockedsql. Text as blockedsql
, Substring (blockedsql. Text, (blockedreq. statement_start_offset/2) + 1,
(Case when blockedreq. statement_end_offset =-1
Then Len (convert (nvarchar (max), blockedsql. Text) * 2
Else blockedreq. statement_end_offset
End-blockedreq. statement_start_offset)/2) + 1)
As [blocked individual query]
, Waits. wait_type
From SYS. dm_exec_connections as blocking
Inner join SYS. dm_exec_requests as blocked
On blocking. session_id = blocked. blocking_session_id
Inner join SYS. dm_exec_sessions sess
On blocking. session_id = sess. session_id
Inner join SYS. dm_tran_session_transactions St
On blocking. session_id = ST. session_id
Left Outer Join SYS. dm_exec_requests er
On st. session_id = ER. session_id
And er. session_id is null
Inner join SYS. dm_ OS _waiting_tasks as waits
On blocked. session_id = waits. session_id
Cross apply SYS. dm_exec_ SQL _text (blocking. most_recent_ SQL _handle)
As blockingsql
Inner join SYS. dm_exec_requests as blockedreq
On waits. session_id = blockedreq. session_id
Inner join SYS. dm_exec_sessions as blockedsess
On waits. session_id = blockedsess. session_id
Cross apply SYS. dm_exec_ SQL _text (blocked. SQL _handle) as blockedsql
Order by waitinseconds

 

View queries that have waited for more than 30 seconds

SET transaction isolation level read uncommitted
Select
Waits. wait_duration_ms/1000 as waitinseconds
, Blocking. session_id as blockingsessionid
, Sess. login_name as blockinguser
, Sess. host_name as blockinglocation
, Blockingsql. Text as blockingsql
, Blocked. session_id as blockedsessionid
, Blockedsess. login_name as blockeduser
, Blockedsess. host_name as blockedlocation
, Blockedsql. Text as blockedsql
, Db_name (blocked. database_id) as databasename
From SYS. dm_exec_connections as blocking
Inner join SYS. dm_exec_requests as blocked
On blocking. session_id = blocked. blocking_session_id
Inner join SYS. dm_exec_sessions sess
On blocking. session_id = sess. session_id
Inner join SYS. dm_tran_session_transactions St
On blocking. session_id = ST. session_id
Left Outer Join SYS. dm_exec_requests er
On st. session_id = ER. session_id
And er. session_id is null
Inner join SYS. dm_ OS _waiting_tasks as waits
On blocked. session_id = waits. session_id
Cross apply SYS. dm_exec_ SQL _text (blocking. most_recent_ SQL _handle)
As blockingsql
Inner join SYS. dm_exec_requests as blockedreq
On waits. session_id = blockedreq. session_id
Inner join SYS. dm_exec_sessions as blockedsess
On waits. session_id = blockedsess. session_id
Cross apply SYS. dm_exec_ SQL _text (blocked. SQL _handle) as blockedsql
Where Waits. wait_duration_ms> 30000
Order by waitinseconds

 

Buffer buffer occupied by each database

 

Set Tran isolation level read uncommitted
Select
Isnull (db_name (database_id), 'resourcedb') as databasename
, Cast (count (row_count) * 8.0/(1024.0) as decimal ))
As [size (MB)]
From SYS. dm_ OS _buffer_descriptors
Group by database_id
Order by databasename

 

Result:

The cache size and page number of each table in the current database

 

SET transaction isolation level read uncommitted
select
object_name (P. [object_id]) as [tablename]
, (count (*) * 8)/1024 as [buffer size (MB)]
, isnull (I. name, '-- heap --') as objectname
, count (*) as numberof8kpages
from sys. allocation_units as a
Inner join sys. dm_ OS _buffer_descriptors as B
On. allocation_unit_id = B. allocation_unit_id
Inner join sys. partitions as P
Inner join sys. indexes I on p. index_id = I. index_id
and P. [object_id] = I. [object_id]
On. container_id = P. hobt_id
where B. database_id = db_id ()
and P. [object_id]> 100
group by P. [object_id], I. name
order by numberof8kpages DESC

 

The result is as follows:

 

 

Database-level wait Io

Set Tran isolation level read uncommitted
Select db_name (database_id) as [databasename]
, Sum (cast (io_stall/1000.0 as decimal () as [IO stall (SECs)]
, Sum (cast (num_of_bytes_read/1024.0/1024.0 as decimal )))
As [IO read (MB)
, Sum (cast (num_of_bytes_written/1024.0/1024.0 as decimal )))
As [IO written (MB)
, Sum (cast (num_of_bytes_read + num_of_bytes_written)
/1024.0/1024.0 as decimal () as [totalio (MB)
From SYS. dm_io_virtual_file_stats (null, null)
Group by database_id
Order by [IO stall (SECs)] DESC

 

The result is as follows:

 

View Io status by file

Set Tran isolation level read uncommitted
Select db_name (database_id) as [databasename]
, File_id
, Sum (cast (io_stall/1000.0 as decimal () as [IO stall (SECs)]
, Sum (cast (num_of_bytes_read/1024.0/1024.0 as decimal )))
As [IO read (MB)]
, Sum (cast (num_of_bytes_written/1024.0/1024.0 as decimal )))
As [IO written (MB)]
, Sum (cast (num_of_bytes_read + num_of_bytes_written)
/1024.0/1024.0 as decimal () as [totalio (MB)]
From SYS. dm_io_virtual_file_stats (null, null)
Group by database_id, file_id
Order by [IO stall (SECs)] DESC

 

The result is as follows: