-- 查詢執(zhí)行中sql語句
SELECT TOP 500
[session_id],
[request_id],
[start_time] AS '開始時間',
getdate() as 當前時間,
[status] AS '狀態(tài)',
[command] AS '命令',
dest.[text] AS 'sql語句',
DB_NAME([database_id]) AS '數(shù)據(jù)庫名',
[blocking_session_id] AS '正在阻塞其他會話的會話ID',
[wait_type] AS '等待資源類型',
[wait_time] AS '等待時間',
[wait_resource] AS '等待的資源',
[reads] AS '物理讀次數(shù)',
[writes] AS '寫次數(shù)',
[logical_reads] AS '邏輯讀次數(shù)',
[row_count] AS '返回結(jié)果行數(shù)'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
ORDER BY 開始時間
-- 查詢SqlServer總體的內(nèi)存使用情況
select type , sum(virtual_memory_reserved_kb) VM_Reserved , sum(virtual_memory_committed_kb) VM_Commited , sum(awe_allocated_kb) AWE_Allocated , sum(shared_memory_reserved_kb) Shared_Reserved , sum(shared_memory_committed_kb) Shared_Commited --, sum(single_pages_kb) --SQL2005图谷、2008 --, sum(multi_pages_kb) --SQL2005、2008 from sys.dm_os_memory_clerks group by type order by type
-- 查詢當前數(shù)據(jù)庫緩存的所有數(shù)據(jù)頁面阱洪，哪些數(shù)據(jù)表便贵，緩存的數(shù)據(jù)頁面數(shù)量 -- 從這些信息可以看出，系統(tǒng)經(jīng)常要訪問的都是哪些表冗荸，有多大承璃？
select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*) from sys.allocation_units a, sys.dm_os_buffer_descriptors b, sys.partitions p where a.allocation_unit_id=b.allocation_unit_id and a.container_id=p.hobt_id and b.database_id=db_id() group by p.object_id,p.index_id order by buffer_pages desc
-- 查詢緩存的各類執(zhí)行計劃，及分別占了多少內(nèi)存
-- 可以對比動態(tài)查詢與參數(shù)化SQL（預(yù)定義語句）的緩存量
select cacheobjtype , objtype , sum(cast(size_in_bytes as bigint))/1024 as size_in_kb , count(bucketid) as cache_count from sys.dm_exec_cached_plans group by cacheobjtype, objtype order by cacheobjtype, objtype
-- 查詢緩存中具體的執(zhí)行計劃蚌本，及對應(yīng)的SQL
-- 將此結(jié)果按照數(shù)據(jù)表或SQL進行統(tǒng)計盔粹，可以作為基線，調(diào)整索引時考慮
-- 查詢結(jié)果會很大程癌，注意將結(jié)果集輸出到表或文件中
SELECT usecounts , refcounts , size_in_bytes , cacheobjtype , objtype , TEXT FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) ORDER BY objtype DESC ;?
轉(zhuǎn)載自：https://www.suanlizi.com/kf/1743594266581667840.html
