【SQL Server DBA】日常巡检语句3:特定监控(阻塞、top语句、索引、作业)

原文:【SQL Server DBA】日常巡检语句3:特定监控(阻塞、top语句、索引、作业)


1、查询阻塞信息、锁定了哪些资源


  1. --1.查看阻塞信息
  2. select spid,loginame,waitresource from master..sysprocesses
  3. where blocked <> 0
  4. /*
  5. spid loginame waitresource
  6. 53 ggg-PC\Administrator RID:1:1:1385:0
  7. */
  8. --2.查看语句
  9. dbcc inputbuffer(53)
  10. /*
  11. eventInfo
  12. select * from xx
  13. */
  14. --3.查看锁的信息
  15. exec sp_lock @spid1 = 53
  16. /*
  17. spid dbid ObjId IndId Type Resource Mode Status
  18. 53 1 1335727861 0 PAG 1:1385 IS GRANT
  19. 53 1 1335727861 0 RID 1:1385:0 S WAIT
  20. 53 1 1335727861 0 TAB IS GRANT
  21. */
  22. --select OBJECT_ID(1335727861) as table_name
  23. --4.打开数据库
  24. select *
  25. from sysdatabases
  26. where dbid = 1
  27. /*
  28. name dbid sid mode status status2 crdate reserved category cmptlevel filename version
  29. 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
  30. */
  31. --5.根据锁信息中的ObjId列:1335727861,找到了这个xx表
  32. select *
  33. from sysobjects
  34. where id = 1335727861
  35. /*
  36. 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
  37. 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
  38. */

查找死锁


  1. exec sp_who_lock
  2. /*
  3. create procedure sp_who_lock
  4. as
  5. begin
  6. declare @spid int,@bl int,
  7. @intTransactionCountOnEntry int,
  8. @intRowcount int,
  9. @intCountProperties int,
  10. @intCounter int
  11. create table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)
  12. IF @@ERROR<>0 RETURN @@ERROR
  13. insert into #tmp_lock_who(spid,bl) select 0 ,blocked
  14. from (select * from sys.sysprocesses where blocked>0 ) a
  15. where not exists(select * from (select * from sys.sysprocesses where blocked>0 ) b
  16. where a.blocked=spid)
  17. union select spid,blocked from sys.sysprocesses where blocked>0
  18. IF @@ERROR<>0 RETURN @@ERROR
  19. -- 找到临时表的记录数
  20. select @intCountProperties = Count(*),@intCounter = 1
  21. from #tmp_lock_who
  22. IF @@ERROR<>0 RETURN @@ERROR
  23. if @intCountProperties=0
  24. select '现在没有阻塞和死锁信息' as message
  25. -- 循环开始
  26. while @intCounter <= @intCountProperties
  27. begin
  28. -- 取第一条记录
  29. select @spid = spid,@bl = bl
  30. from #tmp_lock_who where id = @intCounter
  31. begin
  32. if @spid =0
  33. select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
  34. else
  35. select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
  36. DBCC INPUTBUFFER (@bl )
  37. end
  38. -- 循环指针下移
  39. set @intCounter = @intCounter + 1
  40. end
  41. drop table #tmp_lock_who
  42. return 0
  43. end
  44. */

查看进程中正在执行的SQL


  1. dbcc inputbuffer(spid)
  2. exec sp_who3
  3. /*
  4. CREATE PROCEDURE sp_who3 ( @SessionID INT = NULL )
  5. AS
  6. BEGIN
  7. SELECT SPID = er.session_id ,
  8. Status = ses.status ,
  9. [Login] = ses.login_name ,
  10. Host = ses.host_name ,
  11. BlkBy = er.blocking_session_id ,
  12. DBName = DB_NAME(er.database_id) ,
  13. CommandType = er.command ,
  14. SQLStatement = st.text ,
  15. ObjectName = OBJECT_NAME(st.objectid) ,
  16. ElapsedMS = er.total_elapsed_time ,
  17. CPUTime = er.cpu_time ,
  18. IOReads = er.logical_reads + er.reads ,
  19. IOWrites = er.writes ,
  20. LastWaitType = er.last_wait_type ,
  21. StartTime = er.start_time ,
  22. Protocol = con.net_transport ,
  23. ConnectionWrites = con.num_writes ,
  24. ConnectionReads = con.num_reads ,
  25. ClientAddress = con.client_net_address ,
  26. Authentication = con.auth_scheme
  27. FROM sys.dm_exec_requests er
  28. OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
  29. LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
  30. LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
  31. WHERE er.session_id > 50
  32. AND @SessionID IS NULL
  33. OR er.session_id = @SessionID
  34. ORDER BY er.blocking_session_id DESC ,
  35. er.session_id
  36. END
  37. */

2、top 语句


  1. --SQL Server启动以来累计使用CPU资源最多的语句。
  2. select
  3. highest_cpu_queries.*,
  4. q.dbid,
  5. q.objectid,
  6. q.number,
  7. q.encrypted,
  8. q.[text]
  9. from
  10. (
  11. select top 10 qs.*
  12. from sys.dm_exec_query_stats qs
  13. order by qs.total_worker_time desc
  14. ) as highest_cpu_queries
  15. cross apply sys.dm_exec_sql_text(plan_handle) as q
  16. --where text like '%%'
  17. order by highest_cpu_queries.total_worker_time desc
  18. --我们也可以找到最经常做编重新译的存储过程,也就是recompile过。
  19. select top 10
  20. a.sql_handle,
  21. a.plan_generation_num,
  22. a.execution_count,
  23. s.dbid,
  24. s.objectid,
  25. s.text
  26. from sys.dm_exec_query_stats a
  27. cross apply sys.dm_exec_sql_text(sql_handle) as s
  28. where plan_generation_num >1
  29. order by plan_generation_num desc
  30. --返回做IO数目最多的10条语句以及它们的执行计划
  31. select top 10
  32. (qs.total_logical_reads / qs.execution_count) as avg_logical_reads,
  33. (qs.total_logical_writes / qs.execution_count) as avg_logical_writes,
  34. (qs.total_physical_reads / qs.execution_count) as avg_phys_reads,
  35. qs.execution_count,
  36. qs.statement_start_offset,
  37. qs.statement_end_offset,
  38. qt.dbid,
  39. qt.objectid,
  40. SUBSTRING(qt.text,
  41. qs.statement_start_offset/2,
  42. (case when qs.statement_end_offset = -1
  43. then len(convert(nvarchar(max), qt.text)) * 2
  44. else qs.statement_end_offset
  45. end - qs.statement_start_offset
  46. ) / 2 + 1
  47. ) as statement
  48. from sys.dm_exec_query_stats qs
  49. cross apply sys.dm_exec_sql_text(sql_handle) as qt
  50. cross apply sys.dm_exec_query_plan(plan_handle) as q
  51. order by
  52. (total_logical_reads + total_logical_writes) / Execution_count Desc
  53. --返回最经常运行的10条语句
  54. SELECT TOP 10
  55. cp.cacheobjtype,
  56. cp.usecounts, --使用这个缓存的执行计划的次数
  57. cp.size_in_bytes, --缓存的执行计划使用的字节数
  58. qs.execution_count, --执行次数,与usecounts相等.
  59. qs.plan_generation_num, --用来区分:重新编译语句和存储过程
  60. qs.statement_start_offset,
  61. qs.statement_end_offset,
  62. qt.dbid,
  63. qt.objectid,
  64. SUBSTRING(qt.text,
  65. qs.statement_start_offset/2,
  66. (case when qs.statement_end_offset = -1
  67. then len(convert(nvarchar(max), qt.text)) * 2
  68. else qs.statement_end_offset
  69. end - qs.statement_start_offset
  70. ) / 2 + 1
  71. ) as statement
  72. FROM sys.dm_exec_query_stats qs
  73. cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
  74. inner join sys.dm_exec_cached_plans as cp
  75. on qs.plan_handle=cp.plan_handle
  76. and cp.plan_handle=qs.plan_handle
  77. where cp.usecounts>4
  78. ORDER BY [dbid],[Usecounts] DESC

3、索引


  1. -- 当前数据库可能缺少的索引
  2. select
  3. s.group_handle, --标识缺失索引组,在服务器中是唯一的,一个索引组仅包含一个索引
  4. s.unique_compiles, --从索缺失索引组受益的不同查询的编译和重新编译数
  5. s.user_seeks, --如果用户查询使用了组中建议索引,所导致的查找次数
  6. s.user_scans, --如果用户查询使用了组中建议索引,所导致的扫描次数
  7. s.last_user_seek, --如果用户查询使用了组中建议索引,所导致上次查找的日期和时间
  8. s.last_user_scan, --如果用户查询使用了组中建议索引,所导致上次扫描的日期和时间
  9. s.avg_total_user_cost, --如果用户查询使用了组中建议索引,所能减少的平均成本
  10. s.avg_user_impact, --如果实现了建议索引,那么用户查询的成本将按此百分比平均下降
  11. s.system_seeks, --如果系统查询(如自动统计信息查询)使用了组中建议索引,所导致的查找次数
  12. s.system_scans, --如果系统查询使用了组中建议索引,所导致的扫描次数
  13. s.last_system_seek, --如果系统查询使用了组中建议索引,所导致上次查找的日期和时间
  14. s.last_system_scan, --如果系统查询使用了组中建议索引,所导致上次扫描的日期和时间
  15. s.avg_total_system_cost, --如果系统查询使用了组中建议索引,所能减少的平均成本
  16. s.avg_system_impact, --如果实现了建议索引,那么系统查询的成本将按此百分比平均下降
  17. g.index_group_handle, --标识缺失索引组
  18. g.index_handle, --标识由index_group_handle组指定的缺失索引。一个索引组包含一个索引
  19. d.index_handle,
  20. d.database_id, --标识索引缺失的表所在的数据库id
  21. d.object_id, --标识索引缺失的表
  22. d.statement,
  23. d.equality_columns, --构成相等谓词的列的逗号分隔列表,如下:table.column = constant_value
  24. d.inequality_columns,--构成不等谓词的列的逗号分隔列表,=之外的任何比较运算符都表示不等.
  25. --以下形式的谓词:table.column > constant_value
  26. d.included_columns, --用于查询的涵盖列的逗号分隔列表。有关涵盖列或包含列的详细信息
  27. c.column_id,
  28. c.column_name,
  29. c.column_usage /*
  30. EQUALITY:列提供一个表示相等的谓词,形式为:table.column = constant_value
  31. INEQUALITY:列包含表示不等的谓词,形式为:table.column > constant_value
  32. INCLUDE:列不用于谓词赋值,但用于其他原因,例如包含一个查询。
  33. =之外的任何比较运算符都表示不等。
  34. */
  35. from sys.dm_db_missing_index_group_stats s --缺失索引组的摘要信息,不包括空间索引
  36. inner join sys.dm_db_missing_index_groups g --特定缺失索引组中包含的缺失索引(不含空间索引)信息
  37. on s.group_handle = g.index_group_handle
  38. inner join sys.dm_db_missing_index_details d --返回有关缺失索引的详细信息,不包括空间索引
  39. on d.index_handle = g.index_handle
  40. cross apply sys.dm_db_missing_index_columns(d.index_handle) c --缺少索引的表的列的信息
  41. order by s.avg_user_impact desc
  42. --索引的使用情况
  43. select DB_NAME(t.database_id) dbname,
  44. OBJECT_NAME(t.object_id) tablename,
  45. i.name indexname,
  46. t.user_seeks,
  47. t.user_scans,
  48. t.user_lookups,
  49. t.user_updates
  50. from sys.dm_db_index_usage_stats t
  51. inner join sys.indexes i
  52. on t.object_id = i.object_id and
  53. t.index_id = i.index_id
  54. --修改次数最多的索引,通过Database_id,object_id,index_id和partition_number找是哪个数据库的哪个索引
  55. SELECT top 100 *
  56. FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)
  57. order by leaf_insert_count +
  58. leaf_delete_count +
  59. leaf_update_count desc
  60. --返回当前数据库所有碎片率大于25%的索引
  61. declare @dbid int
  62. select @dbid = db_id()
  63. SELECT *
  64. FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL)
  65. where avg_fragmentation_in_percent > 25
  66. order by avg_fragmentation_in_percent desc
  67. --页的分裂次数,可以适当加大fillfactor的值
  68. use master
  69. go
  70. select leaf_allocation_count, --由于页拆分所引起的页分配的累积计数
  71. nonleaf_allocation_count, --叶级以上由页拆分引起的页分配的累积计数
  72. leaf_page_merge_count, --叶级页合并的累积计数
  73. nonleaf_page_merge_count --叶级以上页合并的累积计数
  74. from sys.dm_db_index_operational_stats
  75. (
  76. db_id('数据库名'),object_id('数据库名.dbo.temp_lock'),1,null
  77. )

4、如何用脚本获得sql server作业的执行情况


  1. select j.name as job_name,
  2. js.step_name,
  3. h.run_date,
  4. h.run_time,
  5. h.run_duration,
  6. h.server,
  7. case run_status
  8. when 0 then '失败'
  9. when 1 then '成功'
  10. when 2 then '重试'
  11. when 3 then '取消'
  12. when 4 then '正在进行'
  13. end as run_status
  14. from msdb.dbo.sysjobhistory h
  15. inner join msdb.dbo.sysjobs j
  16. on h.job_id = j.job_id
  17. inner join msdb.dbo.sysjobsteps js
  18. on js.job_id = h.job_id
  19. and js.step_id = h.step_id
【SQL Server DBA】日常巡检语句3:特定监控(阻塞、top语句、索引、作业)
【SQL Server DBA】日常巡检语句3:特定监控(阻塞、top语句、索引、作业)
发布了416 篇原创文章 · 获赞 135 · 访问量 94万+
上一篇:用仿ActionScript的语法来编写html5——终篇,LegendForHtml5Programming1.0开源库件


下一篇:AI作曲的诺亚方舟将去往何处