SQL SERVER 查看锁表 ，查看数据库占用系统内存

-- 如果要指定数据库就把注释去掉
SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'
-- 整体全看
SELECT TOP 10
    [session_id],
    [request_id],
    [start_time] AS '开始时间',
    [status] AS '状态',
    [command] AS '命令',
    dest.[text] AS 'sql语句', 
    DB_NAME([database_id]) AS '数据库名',
    [blocking_session_id] AS '正在阻塞其他会话的会话ID',
    [wait_type] AS '等待资源类型',
    [wait_time] AS '等待时间',
    [wait_resource] AS '等待的资源',
    [reads] AS '物理读次数',
    [writes] AS '写次数',
    [logical_reads] AS '逻辑读次数',
    [row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der 
CROSS APPLY 
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE [session_id]>50 
ORDER BY [cpu_time] DESC;

SELECT TOP 10 
    dest.[text] AS 'sql语句'
FROM sys.[dm_exec_requests] AS der 
    CROSS APPLY 
    sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE [session_id]>50  
    ORDER BY [cpu_time] DESC;

---查看整个数据库占用系统内存的方法
SELECT 
    ISNULL(DB_NAME(DATABASE_ID),'RESOURCEDB') AS DATABASENAME,
    CAST(COUNT(ROW_COUNT) * 8.0 /(1024.0) AS DECIMAL(28,2)) AS '内存占用(MB)'
FROM SYS.dm_os_buffer_descriptors
    GROUP BY database_id
    ORDER BY DATABASENAME;