概述
上一篇主要介绍了sqlserver巡检时所作的数据库、数据文件、日志文件及I/O统计信息的巡检，今天主要介绍一下对象的一些检查以及性能方面的一些巡检，仅供参考。
一、对象检查
对象检查包括：表、索引、索引视图等
1、查看某个表的行数,保留大小，数据大小，索引大小，未使用大小
--不一定准确:无性能影响exec sp_spaceused @objname ='temp_lock' --准确:但有性能影响exec sp_spaceused @objname ='temp_lock', @updateusage ='true' 2、一次计算多个对象的空间使用情况 (按页统计，没有性能影响，有时不准确)
sys.dm_db_partition_stats返回当前数据库中每个分区(表和索引)的页和行计数信息
select o.name, sum(p.reserved_page_count) as reserved_page_count, --保留页，包含表和索引 sum(p.used_page_count) as used_page_count, --已使用页，包含表和索引 sum(case when p.index_id <2 then p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count else p.lob_used_page_count + p.row_overflow_used_page_count end) as data_pages, --数据页,包含表中数据、索引中的lob数据、索引中的行溢出数据 sum(case when p.index_id < 2 then p.row_count else 0 end) as row_counts --数据行数，包含表中的数据行数，不包含索引中的数据条目数 from sys.dm_db_partition_stats p inner join sys.objects o on p.object_id = o.object_id --where p.object_id= object_id('表名') group by o.name 
3、显示当前数据库中所有的表或视图的数据和索引的空间信息(按页或区统计，有性能影响，准确)
这里包含：逻辑碎片、区碎片(碎片率)、平均页密度
insert extentinfo exec('dbcc extentinfo(''test'') ') go ps:
4、查找数据库需重建的对象
4.1、建表
use dbname go if OBJECT_ID('extentinfo') is not null drop table extentinfo go create table extentinfo ( [file_id] smallint, page_id int, pg_alloc int, ext_size int, obj_id int, index_id int, partition_number int, partition_id bigint, iam_chain_type varchar(50), pfs_bytes varbinary(10) ) go 4.2、查询数据文件盘区信息
查询到的盘区信息是数据库的数据文件的盘区信息，日志文件不以盘区为单位
命令格式: DBCC EXTENTINFO(dbname,tablename,indexid)
DBCC EXTENTINFO('[test]','extentinfo',0)
insert extentinfo exec('dbcc extentinfo(''test'') ') go 4.3、每一个区有一条数据
select file_id, obj_id, --对象ID index_id, --索引id page_id, --这个区是从哪个页开始的,也就是这个区中的第一个页面的页面号 pg_alloc, --这个盘区分配的页面数量 ext_size, --这个盘区包含了多少页 partition_number, partition_id, iam_chain_type, --IAM链类型:行内数据,行溢出数据,大对象数据 pfs_bytes from extentinfo order by file_id, OBJ_ID, index_id, partition_id, ext_size 4.4、重建对象
数据库的数据文件的盘区信息,通过计算每个对象理论上区的数量和实际数量,如果两者相差很大,
那就应该重建对象.
1)每一条记录就是一个区
2)如果pg_alloc比ext_size小，也就是实际每个区分配的页数小于理论上这个区的页数,那么就会多一条记录，把本应该属于这个区的页放到多出来的这条记录
对应的区中,那么原来只有一条记录(也就是一个区)，现在就有2条记录(也就是2个区),导致实际的区数量2大于理论上的区数量1.
select file_id, obj_id, index_id, partition_id, ext_size, count(*) as '实际区的个数', sum(pg_alloc) as '实际包含的页数', ceiling(sum(pg_alloc) * 1.0 / ext_size) as '理论上的区的个数', ceiling(sum(pg_alloc) * 1.0 / ext_size) / count(*) * 100.00 as '理论上的区个数 / 实际区的个数' from extentinfo group by file_id, obj_id, index_id, partition_id, ext_size having ceiling(sum(pg_alloc)*1.0/ext_size) < count(*) --过滤: 理论上区的个数 < 实际区的个数,也就是百分比小于100%的 order by partition_id, obj_id, index_id, [file_id] 六、tempdb数据库检查
监控tempdb的空间使用方法:
(1)可以通过SQL Trace来跟踪,但是由于不能预期造成大量使用tempdb语句在什么时候运行, 而且SQL Trance操作比较昂贵,如果一直开着会产生大量的跟踪文件,对硬盘的负担也比较重,一般不用.
(2)轻量级的监控是通过一定时间间隔运行能够监控系统运行的dbcc命令、动态性能视图-函数， 把结果记录在文件中，这对于很繁忙的系统是不错的选择。
1、tempdb概览
SELECT t1.session_id, t1.internal_objects_alloc_page_count, t1.user_objects_alloc_page_count, t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count, t.text from sys.dm_db_session_space_usage t1 --反映每个session的累计空间申请 inner join sys.dm_exec_sessions as t2 on t1.session_id = t2.session_id inner join sys.dm_exec_requests t3 on t2.session_id = t3.session_id cross apply sys.dm_exec_sql_text(t3.sql_handle) t where t1.internal_objects_alloc_page_count>0 or t1.user_objects_alloc_page_count >0 or t1.internal_objects_dealloc_page_count>0 or t1.user_objects_dealloc_page_count>0 
6.2、查看tempdb空间的总体分配,申请空间的会话正在运行的语句
SELECT t1.session_id, t1.internal_objects_alloc_page_count, t1.user_objects_alloc_page_count, t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count, t.text from sys.dm_db_session_space_usage t1 --反映每个session的累计空间申请 inner join sys.dm_exec_sessions as t2 on t1.session_id = t2.session_id inner join sys.dm_exec_requests t3 on t2.session_id = t3.session_id cross apply sys.dm_exec_sql_text(t3.sql_handle) t where t1.internal_objects_alloc_page_count>0 or t1.user_objects_alloc_page_count >0 or t1.internal_objects_dealloc_page_count>0 or t1.user_objects_dealloc_page_count>0 
6.3、返回tempdb中页分配和释放活动，
只有当任务正在运行时，sys.dm_db_task_space_usage才会返回值。在请求完成时，这些值将按session聚合体现在SYS.dm_db_session_space_usage
select t.session_id, t.request_id, t.database_id, t.user_objects_alloc_page_count, t.internal_objects_dealloc_page_count, t.internal_objects_alloc_page_count, t.internal_objects_dealloc_page_count from sys.dm_db_task_space_usage t inner join sys.dm_exec_sessions e on t.session_id = e.session_id inner join sys.dm_exec_requests r on t.session_id = r.session_id and t.request_id = r.request_id 
七、性能相关
1、查询SQL Server执行过的SQL语句(执行次数)
有的时候，需要知道近段时间SQLSERVER执行了什么语句，可以用下面的方法：
SELECT ss.SUM_execution_count , t.text , ss.SUM_total_elapsed_time , ss.sum_total_worker_time , ss.sum_total_logical_reads , ss.sum_total_logical_writes FROM ( SELECT S.plan_handle , SUM(s.execution_count) SUM_Execution_count , SUM(s.total_elapsed_time) SUM_total_elapsed_time , SUM(s.total_worker_time) SUM_total_worker_time , SUM(s.total_logical_reads) SUM_total_logical_reads , SUM(s.total_logical_writes) SUM_total_logical_writes FROM sys.dm_exec_query_stats s GROUP BY S.plan_handle ) AS ss CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t ORDER BY sum_total_logical_reads DESC
2、开销较大的查询
SELECT ss.SUM_execution_count , t.text , ss.SUM_total_elapsed_time , ss.sum_total_worker_time , ss.sum_total_logical_reads , ss.sum_total_logical_writes FROM ( SELECT S.plan_handle , SUM(s.execution_count) SUM_Execution_count , SUM(s.total_elapsed_time) SUM_total_elapsed_time , SUM(s.total_worker_time) SUM_total_worker_time , SUM(s.total_logical_reads) SUM_total_logical_reads , SUM(s.total_logical_writes) SUM_total_logical_writes FROM sys.dm_exec_query_stats s GROUP BY S.plan_handle ) AS ss CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t ORDER BY sum_total_logical_reads DESC
3、找出执行时间最长的10条SQL(适用于SQL SERVER 2005及其以上版本)
如果想对SQL作筛选，可将not like '%fetch%' 换成 like '%user%'就可以找出SQL语句中含有user关键字的SQL
SELECT (total_elapsed_time / execution_count)/1000 N'平均时间ms' ,total_elapsed_time/1000 N'总花费时间ms' ,total_worker_time/1000 N'所用的CPU总时间ms' ,total_physical_reads N'物理读取总次数' ,total_logical_reads/execution_count N'每次逻辑读次数' ,total_logical_reads N'逻辑读取总次数' ,total_logical_writes N'逻辑写入总次数' ,execution_count N'执行次数' ,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'执行语句' ,creation_time N'语句编译时间' ,last_execution_time 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;
4、找出执行最慢的SQL语句(适用于SQL SERVER 2005及其以上版本)
--给N赋初值为30 declare @n int set @n=30;with maco as ( select top (@n) plan_handle, sum(total_worker_time) as total_worker_time , sum(execution_count) as execution_count , count(1) as sql_count from sys.dm_exec_query_stats group by plan_handle order by sum(total_worker_time) desc ) select t.text , a.total_worker_time , a.execution_count , a.sql_count from maco a cross apply sys.dm_exec_sql_text(plan_handle) t 
5、找出最耗时的前N条T-SQL语句 (适用于SQL SERVER 2005及其以上版本)
SELECT TOP 20 total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数], qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)], last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)], max_worker_time /1000 AS [最大执行时间(ms)], SUBSTRING(qt.text,qs.statement_start_offset/2+1, (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) AS [使用CPU的语法], qt.text [完整语法], qt.dbid, dbname=db_name(qt.dbid), qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName FROM sys.dm_exec_query_stats qs WITH(nolock) CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE execution_count>1 ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC6、平均耗CPU最多的前20个SQL (SQL SERVER 2008或以上版本)
SELECT TOP 20 total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数], qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)], last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)], SUBSTRING(qt.text,qs.statement_start_offset/2+1, (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) AS [使用CPU的语法], qt.text [完整语法], qt.dbid, dbname=db_name(qt.dbid), qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName FROM sys.dm_exec_query_stats qs WITH(nolock) CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE execution_count>1 ORDER BY total_worker_time DESC 7、总耗CPU最多的前20个SQL (SQL SERVER 2008或以上版本)
SELECT TOP 20 total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数], qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)], last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)], SUBSTRING(qt.text,qs.statement_start_offset/2+1, (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) AS [使用CPU的语法], qt.text [完整语法], qt.dbid, dbname=db_name(qt.dbid), qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName FROM sys.dm_exec_query_stats qs WITH(nolock) CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE execution_count>1 ORDER BY total_worker_time DESC 8、逻辑读最高的前25条语句
select top 25p.name as [SP Name],deps.total_logical_reads as [TotalLogicalReads],deps.total_logical_reads / deps.execution_count as [AvgLogicalReads],deps.execution_count,ISNULL(deps.execution_count/DATEDIFF(Second,deps.cached_time,Getdate()),0) as [Calls/Second],deps.total_elapsed_time,deps.total_elapsed_time / deps.execution_count as [avg_elapsed_time] ,deps.cached_timefrom sys.procedures as pinner join sys.dm_exec_procedure_stats as deps on p.[object_id] = deps.[object_id]where deps.database_id = DB_ID()order by deps.total_logical_reads desc9、最耗时的前100个存储过程
SELECT TOP100 a.object_id,--对象名称a.database_id,--数据库名称OBJECT_NAME( object_id, database_id ) AS 'proc name',--存储过程名称a.cached_time,--存储过程添加到缓存的时间a.last_execution_time,--上次执行时间a.total_elapsed_time,--总耗时a.total_elapsed_time/ a.execution_count AS [avg_elapsed_time],--平均耗时a.execution_count,--运行次数a.total_physical_reads/ a.execution_count avg_physical_reads,--平均物理读取数a.total_logical_writes,--总逻辑写入次数a.total_logical_writes/ a.execution_count avg_logical_reads,--平均逻辑写入次数a.last_elapsed_time,--上次运行耗时a.[total_worker_time] --此存储过程自编译以来执行所用的 CPU 时间总量(微秒)FROMsys.dm_exec_procedure_stats a ORDER BY[avg_elapsed_time] DESC;
八、阻塞锁及长任务检查
1、死锁等待
SELECTwt.session_id AS waiting_session_id,DB_NAME( tl.resource_database_id ) AS DatabaseName,wt.wait_duration_ms,wt.waiting_task_address,tl.request_mode,(SELECT SUBSTRING(st.text,( er.statement_start_offset/ 2 ) + 1,( CASE er.statement_end_offset WHEN - 1 THEN datalength( st.text ) ELSE er.statement_end_offset END - er.statement_start_offset/ 2 ) + 1 ) FROMsys.dm_exec_requests AS er CROSS apply sys.dm_exec_sql_text ( er.sql_handle ) AS st WHEREer.session_id= tl.request_session_id ) AS waiting_query_text,tl.resource_type,tl.resource_associated_entity_id,wt.wait_type,wt.blocking_session_id,wt.resource_description AS blocking_resource_description,CASEWHEN wt.blocking_session_id> 0 THEN(SELECTst2.text FROMsys.sysprocesses AS sp CROSS apply sys.dm_exec_sql_text ( sp.sql_handle ) AS st2 WHEREsp.spid= wt.blocking_session_id ) ELSE NULL END AS blocking_query_text FROMsys.dm_os_waiting_tasks AS wtJOIN sys.dm_tran_locks AS tl ON wt.resource_address = tl.lock_owner_address WHEREwt.wait_duration_ms > 5000;
2、表锁跟踪
select request_session_id,resource_type, DB_NAME(resource_database_id) as DatabaseName,OBJECT_NAME(resource_associated_entity_id) as TableName, request_mode, request_type, request_statusfrom sys.dm_tran_locks as tl join sys.all_objects as ao on tl.resource_associated_entity_id=ao.object_idwhere request_type='LOCK' and request_status='GRANT' and request_mode in('X','S')and ao.type='U' and resource_type='OBJECT' and tl.resource_database_id=db_id()3、长时间等待的任务
select top 20 wt.session_id, wt.waiting_task_address, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description--, wt.waiting_tasks_count, wt.max_wait_time_msfromsys.dm_os_waiting_tasks as wt-- where wt.wait_duration_ms > 5000order by wait_duration_ms desc
4、Top 20 耗时任务
SELECT TOP 20 creation_time, last_execution_time, total_elapsed_time,total_elapsed_time/execution_count AS [Avg Elapsed Time us], last_elapsed_time,execution_count, ( 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))FROM sys.dm_exec_sql_text(sql_handle)) AS query_textFROM sys.dm_exec_query_stats ORDER BY [Avg Elapsed Time us] DESC;
觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~
3310
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
