SQL Server 监控

作者: ZhiXiong | 来源:发表于2019-09-24 21:53 被阅读0次

EF性能优化（一）
为什么要监控sql语句，以及如何监控，都有哪几种方式可以监控。
SQL Server 监控
【排查死锁】SQL Server 的状态值 Runnable、R
SQL Server on Ubuntu
SQL Server数据库高级进阶之索引优化实战演练
SQL
SQL Server和的MySQL区别
SQL server数据库使用
SQL Server数据库使用外网IP实例连接服务器

使用中的数据库连接数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'User Connections';

平均每秒事务数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Transactions/sec' and instance_name = '_Total'

缓存命中率:
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value, OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

平均每秒SQL编译数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'SQL Compilations/sec';

平均每秒SQL重编译数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'SQL Re-Compilations/sec';

每秒全表扫描数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Full Scans/sec';

平均每秒batch数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Batch Requests/sec';

每秒用户错误数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Errors/sec' and instance_name = '_Total';

每秒闩锁等待数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Latch Waits/sec';

每秒锁等待次数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Lock Waits/sec'and instance_name = '_Total';

每秒锁请求次数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Lock Requests/sec'and instance_name = '_Total';

每秒锁超时次数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Lock Requests/sec'and instance_name = '_Total';

每秒锁超时次数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Lock Timeouts/sec'and instance_name = '_Total';

平均锁等待延迟:(半成品,等待更新)
select * from sys.dm_os_performance_counters where counter_name = 'Lock Wait Time (ms)' and instance_name = '_Total';

每秒死锁次数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Number Of Deadlocks/sec'and instance_name = '_Total';

每秒检查点写入Page数:
select cntr_value from sys.dm_os_performance_counters where counter_name = 'Checkpoint pages/sec';

数据库状态：
select name, state, state_desc from sys.databases;

AG数据库状态：
select a.name, b.database_state, b.database_state_desc from sys.databases as a, sys.dm_hadr_database_replica_states as b where a.database_id = b.database_id and b.is_local=1;

数据库文件状态：
select a.name, b.physical_name, b.state, b.state_desc from sys.databases as a, sys.master_files as b where a.database_id = b.database_id;

慢查询:
SELECT creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;