SQL 维护用得到的监控语句
使用DMV来分析SQL Server启动以来累计使用CPU资源最多的语句。例如下面的语句就可以列出前50名
SELECT TOP 50 s2.dbid, (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.total_worker_time desc --第二种 select c.last_execution_time,c.execution_count,c.total_logical_reads, c.total_logical_writes,c.total_elapsed_time,c.last_elapsed_time,q.[text] from (select top 50 qs.* from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as c cross apply sys.dm_exec_sql_text(plan_handle) as q order by c.total_worker_time desc
返回最经常运行的100条语句
SELECT TOP 100 cp.cacheobjtype,cp.usecounts,cp.size_in_bytes, qs.statement_start_offset,qs.statement_end_offset,qt.dbid ,qt.objectid ,SUBSTRING(qt.text,qs.statement_start_offset/2, (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else qs.statement_end_offset end -qs.statement_start_offset)/2) as statement FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle where cp.plan_handle=qs.plan_handle and cp.usecounts>4 ORDER BY [dbid],[Usecounts] DESC
返回做IO数目最多的50条语句以及它们的执行计划
select top 50 (total_logical_reads/execution_count) as avg_logical_reads, (total_logical_writes/execution_count) as avg_logical_writes, (total_physical_reads/execution_count) as avg_phys_reads, Execution_count, statement_start_offset as stmt_start_offset, statement_end_offset as stmt_end_offset, substring(sql_text.text, (statement_start_offset/2), case when (statement_end_offset -statement_start_offset)/2 <=0 then 64000 else (statement_end_offset -statement_start_offset)/2 end) as exec_statement, sql_text.text,plan_text.* from sys.dm_exec_query_stats cross apply sys.dm_exec_sql_text(sql_handle) as sql_text cross apply sys.dm_exec_query_plan(plan_handle) as plan_text order by (total_logical_reads + total_logical_writes) /Execution_count Desc
计算signal wait占整wait时间的百分比
指令等待 CPU 资源的时间占总时间的百分比。如果超过 25% ，说明 CPU 紧张
select convert(numeric(5,4),sum(signal_wait_time_ms)/sum(wait_time_ms)) from Sys.dm_os_wait_stats
计算'Cxpacket'占整wait时间的百分比
Cxpacket:Sql Server 在处理一句代价很大的语句，要不就是没有合适的索引或筛选条件没能筛选足够的记录，使得
语句要返回大量的结果，当 >5% 说明有问题
declare @Cxpacket bigint declare @Sumwaits bigint select @Cxpacket = wait_time_ms from Sys.dm_os_wait_stats where wait_type = 'Cxpacket' select @Sumwaits = sum(wait_time_ms) from Sys.dm_os_wait_stats select convert(numeric(5,4),@Cxpacket/@Sumwaits)
查询当前数据库上所有用户表格在Row lock上发生阻塞的频率
declare @dbid int select @dbid = db_id() Select dbid=database_id, objectname=object_name(s.object_id), indexname=i.name, i.index_id --, partition_number, row_lock_count, row_lock_wait_count, [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2)), row_lock_wait_in_ms, [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2)) from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s, sys.indexes i where objectproperty(s.object_id,'IsUserTable') = 1 and i.object_id = s.object_id and i.index_id = s.index_id order by row_lock_wait_count desc
返回当前数据库所有碎片率大于25%的索引
运行本语句会扫描很多数据页面
避免在系统负载比较高时运行
避免在系统负载比较高时运行
declare @dbid int select @dbid = db_id() SELECT o.name as tablename,s.* FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL) s, sys.objects o where avg_fragmentation_in_percent>25 and o.object_id =s.object_id order by avg_fragmentation_in_percent desc
当前数据库可能缺少的索引
select d.* , s.avg_total_user_cost , s.avg_user_impact , s.last_user_seek , s.unique_compiles from sys.dm_db_missing_index_group_stats s , sys.dm_db_missing_index_groups g , sys.dm_db_missing_index_details d where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle order by s.avg_user_impact desc
自动重建或重新组织索引
SET NOCOUNT ON;DECLARE @objectid int;DECLARE @indexid int;DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130); DECLARE @indexname nvarchar(130); DECLARE @partitionnum bigint; DECLARE @partitions bigint;DECLARE @frag float;DECLARE @command nvarchar(4000); -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function -- and convert object and index IDs to names. SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; -- Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; -- Open the cursor. OPEN partitions; -- Loop through the partitions. WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); EXEC (@command); PRINT N'Executed: ' + @command; END; -- Close and deallocate the cursor. CLOSE partitions; DEALLOCATE partitions; -- Drop the temporary table. DROP TABLE #work_to_do;
查看当前数据库索引的使用率
SELECT object_name(object_id) as table_name, (select name from sys.indexes where object_id = stats.object_id and index_id = stats.index_id) as index_name,* FROM sys.dm_db_index_usage_stats as stats WHERE database_id = DB_ID()order by table_name
指定表的索引使用情况
declare @table as nvarchar(100) set @table = 'Table_1'; SELECT( select name from sys.indexes where object_id = stats.object_id and index_id = stats.index_id) as index_name,* FROM sys.dm_db_index_usage_stats as stats where object_id = object_id(@table) order by user_seeks, user_scans, user_lookups asc
最经常做重编译的存储过程
select top 25 sql_text.text, sql_handle, plan_generation_num, execution_count, dbid, objectid from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(sql_handle) as sql_text where plan_generation_num>1 order by plan_generation_num desc
锁
sp_lock select OBJECT_NAME(objid) dbcc inputbuffer(spid) select @@spid kill 58 WITH STATUSONLY EXEC sp_who 'active'
查询系统中死锁的SQL语句
declare @spid int,@bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter int create table #tmp_lock_who ( id int identity(1,1), spid smallint, bl smallint) IF @@ERROR<>0 print @@ERROR insert into #tmp_lock_who(spid,bl) select 0 ,blocked from (select * from sysprocesses where blocked>0 ) a where not exists(select * from (select * from sysprocesses where blocked>0 ) b where a.blocked=spid) union select spid,blocked from sysprocesses where blocked>0 IF @@ERROR<>0 print @@ERROR -- 找到临时表的记录数 select @intCountProperties = Count(*),@intCounter = 1 from #tmp_lock_who IF @@ERROR<>0 print @@ERROR if @intCountProperties=0 select '现在没有阻塞和死锁信息' as message -- 循环开始 while @intCounter <= @intCountProperties begin -- 取第一条记录 select @spid = spid,@bl = bl from #tmp_lock_who where Id = @intCounter begin if @spid =0 select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' else select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl ) end -- 循环指针下移 set @intCounter = @intCounter + 1 end select * from #tmp_lock_who drop table #tmp_lock_who select @@lock_timeout
查看锁信息
select 进程id=req_spid ,数据库=db_name(rsc_dbid) ,类型=case rsc_type when 1 then 'NULL 资源（未使用）' when 2 then '数据库' when 3 then '文件' when 4 then '索引' when 5 then '表' when 6 then '页' when 7 then '键' when 8 then '扩展盘区' when 9 then 'RID（行 ID)' when 10 then '应用程序' end ,rsc_objid,rsc_indid from master..syslockinfo select * from sys.dm_tran_locks SET SHOWPLAN_ALL ON; SET STATISTICS IO on; SET STATISTICS TIME on
列出最初锁住资源，导致一连串其他进程被锁住的起始源头
IF EXISTS(SELECT * FROM master.sys.sysprocesses WHERE spid IN (SELECT blocked FROM master.sys.sysprocesses)) --确定有进程被其他的进程锁住 SELECT DISTINCT '进程ID' = STR(a.spid, 4) ,'进程ID状态' = CONVERT(CHAR(10), a.status) ,'登入帐号'=SUBSTRING(SUSER_SNAME(sid),1,30) ,'工作站名称' = CONVERT(CHAR(10), a.hostname) ,'执行命令的用户' = CONVERT(CHAR(10), SUSER_NAME(a.uid)) ,'是否被锁住'=CONVERT(char(3),blocked) ,'数据库名' = CONVERT(CHAR(10), DB_NAME(a.dbid)) ,'正在执行的命令' = CONVERT(CHAR(16), a.cmd) ,'登录名' = a.loginame ,'执行语句' = b.text ,'等待型态' = a.waittype FROM master..sysprocesses a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b --列出锁住别人(在别的进程中 blocked字段出现的值)，但自己未被锁住(blocked=0) WHERE spid IN (SELECT blocked FROM master.sys.sysprocesses) AND blocked=0 ELSE SELECT 'No Blocked Session(s)' --a.status = suspended,a.blocked(阻塞者id) --DBCC INPUTBUFFER (阻塞者id); --就可以看到语句了或者join
经常出现的是,在sysprocesses视图中 status是'sleeping',waittype字段是0x0000,打开事务数open_tran大于0,一般
都是交易已经激活但迟迟没有结束,就可能是程序没有管理好交易管理
select a.*,b.text from master.sys.sysprocesses a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b where a.status = 'sleeping' and a.waittype=0x0000 and a.open_tran > 0 select t1.resource_type as [资源锁定类型] ,db_name(resource_database_id) as [数据库名] ,t1.resource_associated_entity_id as [锁定的对象] ,t1.request_mode as [等待者需求的锁定类型] ,t1.request_session_id as [等待者sid] ,t2.wait_duration_ms as [等待时间] ,(select text from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle) where r.session_id = t1.request_session_id) as [等待者要执行的批次] ,(select substring(qt.text,r.statement_start_offset/2+1, (case when r.statement_end_offset = -1 then datalength(qt.text) else r.statement_end_offset end - r.statement_start_offset)/2+1) from sys.dm_exec_requests as r cross apply sys.dm_exec_sql_text(r.sql_handle) as qt where r.session_id = t1.request_session_id) as [等待者正要执行的语法] ,t2.blocking_session_id as [锁定者sid] ,(select text from sys.sysprocesses as p cross apply sys.dm_exec_sql_text(p.sql_handle) where p.spid = t2.blocking_session_id) as [锁定者的语法] from sys.dm_tran_locks as t1, sys.dm_os_waiting_tasks as t2 where t1.lock_owner_address = t2.resource_address
统计分析
DBCC SHOW_STATISTICS('表名','索引名')
