sql server 性能排查

sqlserver占用cpu高100%排查，可根据下面语句查询当前耗时最高的sql语句，以及读写次数。

SELECT TOP 50
 [session_id],
 [request_id],
 [cpu_time],
 [start_time] AS '开始时间',
 [status] AS '状态',
 [command] AS '命令',
 dest.[text] AS 'sql语句',
 DB_NAME([database_id]) AS '数据库名',
 [blocking_session_id] AS '正在阻塞其他会话的会话ID',
 der.[wait_type] AS '等待资源类型',
 [wait_time] AS '等待时间',
 [wait_resource] AS '等待的资源',
 [dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
 [reads] AS '物理读次数',
 [writes] AS '写次数',
 [logical_reads] AS '逻辑读次数',
 [row_count] AS '返回结果行数'
 FROM sys.[dm_exec_requests] AS der
 INNER JOIN [sys].[dm_os_wait_stats] AS dows
 ON der.[wait_type]=[dows].[wait_type]
 CROSS APPLY
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
 WHERE [session_id]>50
 ORDER BY [cpu_time] DESC

附录其它常用语句

--------------------SQL Server启动时间
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;


--------------------SQL Server版本
select @@version;


--------------------数据库正在执行的sql
select text,wait_resource,wait_time,wait_type,session_id,blocking_session_id
from sys.dm_exec_requests cross apply sys.dm_exec_sql_text(sql_handle);

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName 
from sys.dm_tran_locks where resource_type='OBJECT';

--------------------死锁
select a.text seesion_text,a.wait_type,a.wait_time,a.session_id,a.blocking_session_id,b.text blocking_session_text from  
 (select text,wait_resource,wait_type,wait_time,session_id,blocking_session_id from sys.dm_exec_requests  
 cross apply sys.dm_exec_sql_text(sql_handle) where wait_time>1000) a left join 
 (select c.session_id,t.text from sys.dm_exec_connections as c cross apply sys.dm_exec_sql_text(most_recent_sql_handle) as t) b  
 on b.session_id=a.blocking_session_id;
 

--------------------获取一次性缓存计划的数量
SELECT objtype, cacheobjtype, 
  AVG(usecounts) AS Avg_UseCount, 
  SUM(refcounts) AS AllRefObjects, 
  SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS Size_MB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts = 1
GROUP BY objtype, cacheobjtype;

SELECT objtype, cacheobjtype, 
  AVG(usecounts) AS Avg_UseCount, 
  SUM(refcounts) AS AllRefObjects, 
  SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS Size_MB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts > 1
GROUP BY objtype, cacheobjtype;


--------------------操作系统内存建议
-- You want to see "Available physical memory is high"
SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)], 
       available_physical_memory_kb/1024 AS [Available Memory (MB)], 
       total_page_file_kb/1024 AS [Total Page File (MB)], 
	   available_page_file_kb/1024 AS [Available Page File (MB)], 
	   system_cache_kb/1024 AS [System Cache (MB)],
       system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);


--------------------操作系统内存情况
SELECT 
	physical_memory_in_use_kb/1024 Physical_memory_in_use_MB, 
    large_page_allocations_kb/1024 Large_page_allocations_MB, 
    locked_page_allocations_kb/1024 Locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 VAS_reserved_MB, 
    virtual_address_space_committed_kb/1024 VAS_committed_MB, 
    virtual_address_space_available_kb/1024 VAS_available_MB,
    page_fault_count Page_fault_count,
    memory_utilization_percentage Memory_utilization_percentage, 
    process_physical_memory_low Process_physical_memory_low, 
    process_virtual_memory_low Process_virtual_memory_low
FROM sys.dm_os_process_memory;


--------------------恢复日志描述模型，复用等，日志大小和日志文件大小
SELECT db.[name] AS [Database Name], SUSER_SNAME(db.owner_sid) AS [Database Owner], db.recovery_model_desc AS [Recovery Model], 
db.log_reuse_wait_desc AS [Log Reuse Wait Description], 
CAST((CAST(ls.cntr_value AS FLOAT)/1024)AS DECIMAL(18,2)) AS [Log Size (MB)],
CAST((CAST(lu.cntr_value AS FLOAT)/1024)AS DECIMAL(18,2)) AS [Log Used (MB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %], 
db.[compatibility_level] AS [DB Compatibility Level], 
db.page_verify_option_desc AS [Page Verify Option],db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on,
db.is_auto_close_on, db.is_auto_shrink_on
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) 
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%' 
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0
ORDER BY db.[name] OPTION (RECOMPILE);




--------------------按索引优势列出的所有数据库缺少索引
SELECT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage],  
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC OPTION (RECOMPILE);


--------------------查看CPU占用量最高的会话及SQL语句
select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc
 

--------------------查看缓存重用次数少，内存占用大的SQL语句
SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text] 
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql 
ORDER BY usecounts,p.size_in_bytes  desc


--------------------查看哪些进程在使用
select * from sysprocesses