SQLserver 的性能排查笔记

SQLserver 的性能排查笔记
查看存储中用到的文本

SELECT DISTINCT
OBJECT_NAME(id),id from syscomments with(nolock)
where id in
( select object_id(name) from dbo.sysobjects with(nolock) where xtype='P'
)
and text like '%内容%' 

运行中的语句

--运行中的语句
SELECT
	individual_query=SUBSTRING(qt.text,(er.statement_start_offset/2)+1,((CASE  WHEN  er.statement_end_offset=-1 THEN  LEN(CONVERT(NVARCHAR(MAX),qt.text))* 2 ELSE   er.statement_end_offset  END-er.statement_start_offset)/2)+1),
    parent_query=qt.text,
    es.session_id,
    database_name=DB_NAME(er.database_id),
    er.cpu_time,
    er.reads,
    er.writes,
    er.logical_reads,
    login_name,
    er.status,
    blocking_session_id,
    wait_type,
    wait_resource,
    wait_time,
    program_name,
    host_name,
    nt_domain,
    start_time,
   -- DATEDIFF(MS,er.start_time,GETDATE())as duration,
    (SELECT  query_plan  FROM  sys.dm_exec_query_plan (er.plan_handle))AS  query_plan
FROM
    sys.dm_exec_requests er
    INNER  JOIN  sys.dm_exec_sessions  es  ON er.session_id=es.session_id
    CROSS  APPLY  sys.dm_exec_sql_text (er.sql_handle)AS  qt
WHERE
    es.session_id> 50         
    AND  es.session_Id  NOT  IN(@@SPID)
ORDER BY
    1, 2

当前CPU最大的语句

SELECT TOP 100 s.session_id,
           r.status,
           r.cpu_time,
           r.logical_reads,
           r.reads,
           r.writes,
           r.total_elapsed_time / (1000 * 60) 'Elaps M',
           SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
           ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.TEXT)
                ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) + 1) AS statement_text,
           COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))
           + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
           r.command,
           s.login_name,
           s.host_name,
           c.client_net_address,
           s.program_name,
           s.last_request_end_time,
           s.login_time,
           r.open_transaction_count
FROM sys.dm_exec_connections c
JOIN sys.dm_exec_sessions s ON c.session_id= s.session_id
JOIN sys.dm_exec_requests r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
-- WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

历史CPU最大的语句

SELECT TOP 100 st.text AS batch_text,
    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,
    qs.execution_count,
    qs.total_worker_time,
    qs.last_execution_time,
    (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
    (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
    (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC

索引创建建议

SELECT CONVERT(VARCHAR(30), GETDATE(), 126) AS runtime,
    mig.index_group_handle,
    mid.index_handle,
    CONVERT(DECIMAL(28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
    'CREATE INDEX missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns,
        '') + CASE WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(mid.inequality_columns,
        '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')',
        '') AS create_index_statement,
    migs.*,
    mid.database_id,
    mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (DECIMAL (28, 1),
               migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

索引

--查看索引
dbcc ShowContig('tableName')
--重建索引
dbcc dbreindex('tableName')

查看死锁

SELECT XEventData.XEvent.value('@timestamp', 'datetime2(3)'),  
cast(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) 
FROM (SELECT CAST (target_data AS XML) AS TargetData     
FROM sys.dm_xe_session_targets st     
JOIN sys.dm_xe_sessions s 
ON s.address = st.event_session_address     
WHERE [name] = 'system_health' AND st.target_name = N'ring_buffer') AS Data 
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)     
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

查看阻塞

select * from master..sysprocesses where blocked<>0

查看临时表占用大小

select cast(sum(a.total_pages)*8/1024 as varchar)+' MB' total     
from sys.partitions p 
join sys.allocation_units a 
on p.partition_id = a.container_id 
join sys.tables it 
on p.object_id = it.object_id
 where it.name like 'TMP%' 

查询系统中每个临时表占用的空间大小统计

select it.name, cast(sum(a.total_pages)*8 as varchar)+'KB' total     
from sys.partitions p 
join sys.allocation_units a 
on p.partition_id = a.container_id  
join sys.tables it 
on p.object_id = it.object_id 
where it.name like 'tm%' 
group by  it.name order by sum(a.total_pages)*8 desc

查看所有已经标记为需要删除的临时表

select name
from sys.tables u 
join T_BAS_TEMPORARYTABLENAME v 
on u.name=v.FTABLENAME 
and (v.FPROCESSTYPE=1 or v.FCREATEDATE<GETDATE()-1); 

删除所有已经标记为需要删除的临时表

declare @sql as varchar(max) 
set @sql='' 
select @sql=@sql+'drop table '+name+';' 
from sys.tables u 
join T_BAS_TEMPORARYTABLENAME v 
on u.name=v.FTABLENAME 
and (v.FPROCESSTYPE=1 or v.FCREATEDATE<GETDATE()-1); 
exec(@sql); 
delete u from T_BAS_TEMPORARYTABLENAME u where  
not exists(select 1 from sys.tables where u.ftablename=name ); 

查询字段长度

SELECT a.name,b.name,c.DATA_TYPE,b.max_length FROM sys.tables a join sys.columns b on b.object_id = a.object_id
join INFORMATION_SCHEMA.COLUMNS c on b.name=c.COLUMN_NAME and a.name=c.TABLE_NAME
where a.name='表名' AND B.NAME = '字段名'

PLE (Page Life Expectancy, 页面的预期寿命)

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Manager%'
AND counter_name = 'Page life expectancy'

一般在300以上

执行时间

--SELECT TOP 10 * FROM T_BD_MATERIAL

SELECT top 100
(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'执行次数'
,creation_time N'语句编译时间'
,last_execution_time 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'执行语句'
,qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
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 last_execution_time DESC,total_elapsed_time / execution_count DESC;

卡慢排查

--打开IO和TIME统计开关
SET STATISTICS TIME ON
SET  STATISTICS IO ON

--清空缓存
checkpoint
DBCC DROPCLEANBUFFERS 
DBCC FREEPROCCACHE     

--数据库连接情况
select * from sys.dm_exec_connections
select client_net_address,count(*) from sys.dm_exec_connections group by client_net_address

--获取SQL Server允许同时用户连接的最大数
SELECT @@MAX_CONNECTIONS 

--获取当前SQL服务器所有的连接详细信息
SELECT * FROM sysprocesses 

查看被锁的表

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT';

# 这时会出现两列数据
第一列 --spid 锁表进程
第二列 --tableName 被锁表名

对锁表进行解锁

declare @spid int
Set @spid = 57 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)

查看所有的spid

select 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)