To get the query memory usage of currently executing queries run the following:
SELECT
TEXT
,query_plan
,requested_memory_kb
,granted_memory_kb
,used_memory_kb
FROM sys.dm_exec_query_memory_grants emg
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(emg.plan_handle)
ORDER BY emg.requested_memory_kb DESC
The following DMV query retrieves the usage statistics for existing indexes.
User Seeks – A high number indicates a well utilized index.
User Scans – Number of times the index has been scanned. Could indicate improper ordering of the composite columns
User Lookups – Indicates a different index was used for criteria and the actual [...]
One of the most common database performance issue most companies face is the improper choice of indexes or the lack of indexes. Most essential, is the clustered index. A well-chosen clustered index will save CPU, Memory, and IO. Choosing the clustered index for a table goes hand-in-hand with the writing of the [...]
SELECT DISTINCT
objname = object_name(p.object_id)
FROM sys.partitions p
JOIN sys.dm_tran_locks t1
ON p.hobt_id = t1.resource_associated_entity_id
SELECT TOP 50
qs.execution_count
,OBJECT_NAME(objectid)
,query_text = SUBSTRING(
qt.text,
qs.statement_start_offset/2,
(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)
,qt.dbid
,dbname = db_name(qt.dbid)
,qt.objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY qs.execution_count DESC
SELECT
OBJECT_NAME(objectid)
,BlockTime = total_elapsed_time – total_worker_time
,execution_count
,total_logical_reads
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle)
ORDER BY total_elapsed_time – total_worker_time DESC