原文:【SQL Server DBA】日常巡检语句3：特定监控(阻塞、top语句、索引、作业)
1、查询阻塞信息、锁定了哪些资源
-
--1.查看阻塞信息
-
select spid,loginame,waitresource from master..sysprocesses
-
where blocked <> 0
-
/*
-
spid loginame waitresource
-
53 ggg-PC\Administrator RID:1:1:1385:0
-
*/
-
-
-
-
--2.查看语句
-
dbcc inputbuffer(53)
-
/*
-
eventInfo
-
select * from xx
-
*/
-
-
-
-
--3.查看锁的信息
-
exec sp_lock @spid1 = 53
-
/*
-
spid dbid ObjId IndId Type Resource Mode Status
-
53 1 1335727861 0 PAG 1:1385 IS GRANT
-
53 1 1335727861 0 RID 1:1385:0 S WAIT
-
53 1 1335727861 0 TAB IS GRANT
-
*/
-
-
--select OBJECT_ID(1335727861) as table_name
-
-
-
-
--4.打开数据库
-
select *
-
from sysdatabases
-
where dbid = 1
-
/*
-
name dbid sid mode status status2 crdate reserved category cmptlevel filename version
-
master 1 0x01 0 65544 1090520064 2003-04-08 09:13:36.390 1900-01-01 00:00:00.000 0 100 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf 661
-
*/
-
-
-
-
--5.根据锁信息中的ObjId列：1335727861，找到了这个xx表
-
select *
-
from sysobjects
-
where id = 1335727861
-
/*
-
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
-
xx 1335727861 U 1 0 0 0 0 0 2013-12-25 08:55:07.523 0 0 0 U 1 3 0 2013-12-25 08:55:07.523 0 0 0 0 0 0 0
-
*/
查找死锁
-
exec sp_who_lock
-
/*
-
create procedure sp_who_lock
-
as
-
begin
-
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 RETURN @@ERROR
-
insert into #tmp_lock_who(spid,bl) select 0 ,blocked
-
from (select * from sys.sysprocesses where blocked>0 ) a
-
where not exists(select * from (select * from sys.sysprocesses where blocked>0 ) b
-
where a.blocked=spid)
-
union select spid,blocked from sys.sysprocesses where blocked>0
-
IF @@ERROR<>0 RETURN @@ERROR
-
-- 找到临时表的记录数
-
select @intCountProperties = Count(*),@intCounter = 1
-
from #tmp_lock_who
-
IF @@ERROR<>0 RETURN @@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
-
drop table #tmp_lock_who
-
return 0
-
end
-
*/
查看进程中正在执行的SQL
-
dbcc inputbuffer(spid)
-
-
exec sp_who3
-
/*
-
CREATE PROCEDURE sp_who3 ( @SessionID INT = NULL )
-
AS
-
BEGIN
-
-
-
SELECT SPID = er.session_id ,
-
Status = ses.status ,
-
[Login] = ses.login_name ,
-
Host = ses.host_name ,
-
BlkBy = er.blocking_session_id ,
-
DBName = DB_NAME(er.database_id) ,
-
CommandType = er.command ,
-
SQLStatement = st.text ,
-
ObjectName = OBJECT_NAME(st.objectid) ,
-
ElapsedMS = er.total_elapsed_time ,
-
CPUTime = er.cpu_time ,
-
IOReads = er.logical_reads + er.reads ,
-
IOWrites = er.writes ,
-
LastWaitType = er.last_wait_type ,
-
StartTime = er.start_time ,
-
Protocol = con.net_transport ,
-
ConnectionWrites = con.num_writes ,
-
ConnectionReads = con.num_reads ,
-
ClientAddress = con.client_net_address ,
-
Authentication = con.auth_scheme
-
FROM sys.dm_exec_requests er
-
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
-
LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
-
LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
-
WHERE er.session_id > 50
-
AND @SessionID IS NULL
-
OR er.session_id = @SessionID
-
ORDER BY er.blocking_session_id DESC ,
-
er.session_id
-
-
-
END
-
*/
2、top 语句
-
--SQL Server启动以来累计使用CPU资源最多的语句。
-
select
-
highest_cpu_queries.*,
-
-
q.dbid,
-
q.objectid,
-
q.number,
-
q.encrypted,
-
q.[text]
-
from
-
(
-
select top 10 qs.*
-
from sys.dm_exec_query_stats qs
-
order by qs.total_worker_time desc
-
) as highest_cpu_queries
-
-
cross apply sys.dm_exec_sql_text(plan_handle) as q
-
--where text like '%%'
-
order by highest_cpu_queries.total_worker_time desc
-
-
-
-
--我们也可以找到最经常做编重新译的存储过程,也就是recompile过。
-
select top 10
-
a.sql_handle,
-
a.plan_generation_num,
-
a.execution_count,
-
-
s.dbid,
-
s.objectid,
-
s.text
-
from sys.dm_exec_query_stats a
-
cross apply sys.dm_exec_sql_text(sql_handle) as s
-
-
where plan_generation_num >1
-
order by plan_generation_num desc
-
-
-
-
--返回做IO数目最多的10条语句以及它们的执行计划
-
select top 10
-
-
(qs.total_logical_reads / qs.execution_count) as avg_logical_reads,
-
(qs.total_logical_writes / qs.execution_count) as avg_logical_writes,
-
(qs.total_physical_reads / qs.execution_count) as avg_phys_reads,
-
-
qs.execution_count,
-
-
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 + 1
-
) as statement
-
from sys.dm_exec_query_stats qs
-
cross apply sys.dm_exec_sql_text(sql_handle) as qt
-
cross apply sys.dm_exec_query_plan(plan_handle) as q
-
order by
-
(total_logical_reads + total_logical_writes) / Execution_count Desc
-
-
-
-
--返回最经常运行的10条语句
-
SELECT TOP 10
-
cp.cacheobjtype,
-
-
cp.usecounts, --使用这个缓存的执行计划的次数
-
cp.size_in_bytes, --缓存的执行计划使用的字节数
-
-
qs.execution_count, --执行次数,与usecounts相等.
-
qs.plan_generation_num, --用来区分:重新编译语句和存储过程
-
-
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 + 1
-
) 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
-
and cp.plan_handle=qs.plan_handle
-
-
where cp.usecounts>4
-
ORDER BY [dbid],[Usecounts] DESC
3、索引
-
-- 当前数据库可能缺少的索引
-
select
-
s.group_handle, --标识缺失索引组,在服务器中是唯一的,一个索引组仅包含一个索引
-
-
s.unique_compiles, --从索缺失索引组受益的不同查询的编译和重新编译数
-
-
s.user_seeks, --如果用户查询使用了组中建议索引，所导致的查找次数
-
s.user_scans, --如果用户查询使用了组中建议索引，所导致的扫描次数
-
s.last_user_seek, --如果用户查询使用了组中建议索引，所导致上次查找的日期和时间
-
s.last_user_scan, --如果用户查询使用了组中建议索引，所导致上次扫描的日期和时间
-
s.avg_total_user_cost, --如果用户查询使用了组中建议索引，所能减少的平均成本
-
s.avg_user_impact, --如果实现了建议索引，那么用户查询的成本将按此百分比平均下降
-
-
s.system_seeks, --如果系统查询（如自动统计信息查询）使用了组中建议索引，所导致的查找次数
-
s.system_scans, --如果系统查询使用了组中建议索引，所导致的扫描次数
-
s.last_system_seek, --如果系统查询使用了组中建议索引，所导致上次查找的日期和时间
-
s.last_system_scan, --如果系统查询使用了组中建议索引，所导致上次扫描的日期和时间
-
s.avg_total_system_cost, --如果系统查询使用了组中建议索引，所能减少的平均成本
-
s.avg_system_impact, --如果实现了建议索引，那么系统查询的成本将按此百分比平均下降
-
-
-
g.index_group_handle, --标识缺失索引组
-
g.index_handle, --标识由index_group_handle组指定的缺失索引。一个索引组包含一个索引
-
-
-
d.index_handle,
-
d.database_id, --标识索引缺失的表所在的数据库id
-
d.object_id, --标识索引缺失的表
-
d.statement,
-
-
d.equality_columns, --构成相等谓词的列的逗号分隔列表，如下：table.column = constant_value
-
d.inequality_columns,--构成不等谓词的列的逗号分隔列表，=之外的任何比较运算符都表示不等.
-
--以下形式的谓词:table.column > constant_value
-
d.included_columns, --用于查询的涵盖列的逗号分隔列表。有关涵盖列或包含列的详细信息
-
-
c.column_id,
-
c.column_name,
-
c.column_usage /*
-
EQUALITY:列提供一个表示相等的谓词，形式为：table.column = constant_value
-
INEQUALITY:列包含表示不等的谓词，形式为：table.column > constant_value
-
INCLUDE:列不用于谓词赋值，但用于其他原因，例如包含一个查询。
-
=之外的任何比较运算符都表示不等。
-
*/
-
from sys.dm_db_missing_index_group_stats s --缺失索引组的摘要信息，不包括空间索引
-
inner join sys.dm_db_missing_index_groups g --特定缺失索引组中包含的缺失索引（不含空间索引）信息
-
on s.group_handle = g.index_group_handle
-
inner join sys.dm_db_missing_index_details d --返回有关缺失索引的详细信息，不包括空间索引
-
on d.index_handle = g.index_handle
-
cross apply sys.dm_db_missing_index_columns(d.index_handle) c --缺少索引的表的列的信息
-
order by s.avg_user_impact desc
-
-
-
-
--索引的使用情况
-
select DB_NAME(t.database_id) dbname,
-
OBJECT_NAME(t.object_id) tablename,
-
-
i.name indexname,
-
t.user_seeks,
-
t.user_scans,
-
t.user_lookups,
-
t.user_updates
-
from sys.dm_db_index_usage_stats t
-
inner join sys.indexes i
-
on t.object_id = i.object_id and
-
t.index_id = i.index_id
-
-
-
-
--修改次数最多的索引，通过Database_id,object_id,index_id和partition_number找是哪个数据库的哪个索引
-
SELECT top 100 *
-
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)
-
order by leaf_insert_count +
-
leaf_delete_count +
-
leaf_update_count desc
-
-
-
-
--返回当前数据库所有碎片率大于25%的索引
-
declare @dbid int
-
select @dbid = db_id()
-
-
SELECT *
-
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL)
-
where avg_fragmentation_in_percent > 25
-
-
order by avg_fragmentation_in_percent desc
-
-
-
-
--页的分裂次数,可以适当加大fillfactor的值
-
use master
-
go
-
-
select leaf_allocation_count, --由于页拆分所引起的页分配的累积计数
-
nonleaf_allocation_count, --叶级以上由页拆分引起的页分配的累积计数
-
-
leaf_page_merge_count, --叶级页合并的累积计数
-
nonleaf_page_merge_count --叶级以上页合并的累积计数
-
from sys.dm_db_index_operational_stats
-
(
-
db_id('数据库名'),object_id('数据库名.dbo.temp_lock'),1,null
-
)
4、如何用脚本获得sql server作业的执行情况
-
select j.name as job_name,
-
-
js.step_name,
-
-
h.run_date,
-
h.run_time,
-
h.run_duration,
-
h.server,
-
-
case run_status
-
when 0 then '失败'
-
when 1 then '成功'
-
when 2 then '重试'
-
when 3 then '取消'
-
when 4 then '正在进行'
-
end as run_status
-
-
from msdb.dbo.sysjobhistory h
-
-
inner join msdb.dbo.sysjobs j
-
on h.job_id = j.job_id
-
-
inner join msdb.dbo.sysjobsteps js
-
on js.job_id = h.job_id
-
and js.step_id = h.step_id
