查看性能记数器
SELECT * FROM sys.dm_os_performance_counters;
-- 执行过的线程所遇到的所有等待(不是当前正在运行的线程, 为自上次重置统计信息或启动服务器以来累积的数据),可分析靠前的几个等待较高的事件。
select * from sys.dm_os_wait_stats order by wait_time_ms desc;
该动态视图的细节，请查看帮助文档。
重置该动态视图
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO
正在等待某些资源的任务的等待队列
select * from sys.dm_os_waiting_tasks order by wait_duration_ms desc;
内存使用：
查看当前由 SQL Server 分配的内存对象(KB):
select sum((page_size_in_bytes/1024)*max_pages_allocated_count) from sys.dm_os_memory_objects;
查看系统内存当前信息:
select * from sys.dm_os_sys_memory --这个动态视图只在sql 2008以上DBS才有 select cpu_count, hyperthread_ratio, scheduler_count, physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb, virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb, bpool_committed * 8 / 1024 as bpool_committed_mb, bpool_commit_target * 8 / 1024 as bpool_target_mb, bpool_visible * 8 / 1024 as bpool_visible_mb from sys.dm_os_sys_info
限制SQL Server使用的最小,最大内存(MB)：
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'min server memory', 300; GO sp_configure 'max server memory', 850; GO RECONFIGURE; GO
CPU使用情况:
SELECT TOP 50 total_worker_time/execution_count AS '每次执行占用CPU(微秒)', execution_count as '执行次数', total_worker_time as '总共占用CPU(微秒)', creation_time as '创建时间', last_execution_time as '最后执行时间', min_worker_time as '最低每次占用CPU', max_worker_time as '最高每次占用cpu', total_physical_reads as '总共io物理读取次数', total_logical_reads as '总共逻辑读取次数', total_logical_writes as '总共逻辑写次数', total_elapsed_time as '完成此计划的执行所占用的总时间(微秒)', (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS 'SQL内容' FROM sys.dm_exec_query_stats ORDER BY 1 DESC
下面的查询显示SQL 等待分析和前10 个等待的资源
select top 10 *
from sys.dm_os_wait_stats
where wait_type not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')
order by wait_time_ms desc;
SQL Server中的活动会话数:
exec sp_who 'active' print @@rowcount
SQL Server等待情况
select * from sys.dm_os_waiting_tasks ; --当前等待事件
select * from sys.dm_os_wait_stats --历史等待次数,是sqlserver启动后的累计值,需使用下一条语句清空
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
找出进程阻塞:
运行下面的查询可确定阻塞的会话
select blocking_session_id, wait_duration_ms, session_id from sys.dm_os_waiting_tasks where blocking_session_id is not null
spid 正在阻塞另一个 spid，可在数据库中创建以下存储过程，然后执行该存储过程。此存储过程会报告此阻塞情况。键入 sp_who 可找出 @spid；@spid 是可选参数。
create proc dbo.sp_block (@spid bigint=NULL) as select t1.resource_type, 'database'=db_name(resource_database_id), 'blk object' = t1.resource_associated_entity_id, t1.request_mode, t1.request_session_id, t2.blocking_session_id from sys.dm_tran_locks as t1, sys.dm_os_waiting_tasks as t2 where t1.lock_owner_address = t2.resource_address and t1.request_session_id = isnull(@spid,t1.request_session_id)
以下是使用此存储过程的示例。
exec sp_block exec sp_block @spid = 7 select sum((page_size_in_bytes/1024)*max_pages_allocated_count) from sys.dm_os_memory_objects; select * from sys.dm_os_sys_info; select * from sys.dm_os_performance_counters
A. 获取有关按平均 CPU 时间排在最前面的五个查询的信息
以下示例返回前五个查询的 SQL 语句文本和平均 CPU 时间。
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY total_worker_time/execution_count DESC;
B. 提供批处理执行统计信息
以下示例返回按批执行的 SQL 查询的文本，并提供有关它们的统计信息。
SELECT s2.dbid, s1.sql_handle, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement, execution_count, plan_generation_num, last_execution_time, total_worker_time, last_worker_time, min_worker_time, max_worker_time, total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 WHERE s2.objectid is null ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;
数据库文件性能,文件io性能统计(必须重启sql server服务,才能清零该计数器)
select DB_NAME(database_id) DB_NAME, file_id,io_stall_read_ms ,num_of_reads ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms' ,io_stall_write_ms,num_of_writes ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms' ,io_stall_read_ms + io_stall_write_ms as io_stalls ,num_of_reads + num_of_writes as total_io ,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms' from sys.dm_io_virtual_file_stats(null,null) order by avg_io_stall_ms desc;
查看分区表money，各个分区的行数和边界值.
select partition = $partition.分区函数名(userid) ,rows = count(*) ,minval = min(userid) ,maxval = max(userid) from dbo.money with(nolock) group by $partition.分区函数名(userid) order by partition;
数据库文件性能,文件io性能统计(必须重启sql server服务,才能清零该计数器),
SELECT DB_NAME(vfs.DbId) DatabaseName, mf.name, mf.physical_name, vfs.BytesRead, vfs.BytesWritten, vfs.IoStallMS, vfs.IoStallReadMS, vfs.IoStallWriteMS, vfs.NumberReads, vfs.NumberWrites, (Size*8)/1024 Size_MB FROM ::fn_virtualfilestats(NULL,NULL) vfs INNER JOIN sys.master_files mf ON mf.database_id = vfs.DbId AND mf.FILE_ID = vfs.FileId GO
性能计数器动态视图, 和使用windows性能计数器效果一致。
SELECT [counter_name], [cntr_value] FROM sys.dm_os_performance_counters
WHERE ([instance_name] = '' OR [instance_name] = '_Total') AND (
([object_name] LIKE ('%Plan Cache%') AND [counter_name] IN
('Cache Hit Ratio', 'Cache Hit Ratio Base')) OR
([object_name] LIKE ('%Buffer Manager%') AND [counter_name] IN
('Buffer Cache Hit Ratio', 'Buffer Cache Hit Ratio Base', 'Page reads/sec', 'Page writes/sec')) OR
([object_name] LIKE ('%General Statistics%') AND [counter_name] IN
('Active Temp Tables', 'User Connections')) OR
([object_name] LIKE ('%Databases%') AND [counter_name] IN
('Transactions/sec', 'Log Cache Hit Ratio', 'Log Cache Hit Ratio Base', 'Log Flushes/sec',
'Log Bytes Flushed/sec', 'Backup/Restore Throughput/sec')) OR
([object_name] LIKE ('%Access Methods%') AND [counter_name] IN
('Full Scans/sec', 'Range Scans/sec', 'Probe Scans/sec', 'Index Searches/sec', 'Page Splits/sec')) OR
([object_name] LIKE ('%Memory Manager%') AND [counter_name] IN
('Target Server Memory (KB)', 'Target Server Memory(KB)', 'Total Server Memory (KB)')) OR
([object_name] LIKE ('%SQL Statistics%') AND [counter_name] IN
('SQL Compilations/sec', 'SQL Re-Compilations/sec'))