sqlserver cpu占用高排查_BS开发框架

在MSSql高级--阻塞和死锁文章中我们讲解了B/S开发框架相关项目数据发生阻塞和死锁怎么办,今天给大家讲解一个更高级的解决性能的话题-运行sql server cpu占用高问题排查方法。

B/S开发框架SQLServer

B/S开发框架项目在上线后,经常会碰到打开页面卡或页面一直出不来,这个情况下首选要注意后台代码是否有循环多的代码或者运行占内存的代码,还有可能是软件呢开发平台的项目业务数据库执行某句(段)sql代码引起内存不足或者CPU爆满,从而页面久久不能获取到数据。那么我们该怎么去识别SQL语句耗性能呢,请运行下面的sql语句。

 --select * from [xxxxDB].[dbo].[WebUser] where Id=84
 --1、profile排查高占用cpu(>2000) sql语句
 --创建索引
--CREATE NONCLUSTERED INDEX IX_t_AccessControl_F4 ON dbo.t_AccessControl
-- (
--     FObjectType
-- )include([FUserID], [FAccessType], [FAccessMask]) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
-- GO
--删除索引
-- drop index IX_t_AccessControl_F4 on t_AccessControl

--重建所有表索引
--declare @table varchar(200)
-- declare cur_dbreindex cursor for select name from sysobjects 
--              where xtype='u'
--                  order by a.name
-- open cur_dbreindex
-- fetch next from cur_dbreindex into @table 
-- while @@FETCH_STATUS=0
--   begin
--     exec('dbcc dbreindex ('+@table+')')
--     fetch next from cur_dbreindex into @table
--   end
-- close cur_dbreindex
-- deallocate cur_dbreindex 

--查询索引碎片

SELECT a.index_id, name, avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a 
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; 

--CREATE NONCLUSTERED INDEX IX_t_UserID ON dbo.SearchEnginePublish
--(
--     UserId
-- )WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

-- drop index IX_t_AccessControl_F4 on t_AccessControl

--use NewBusinessExpressDb
----查询表索引
--SELECT   索引名称=a.name  
--,表名=c.name  
--,索引字段名=d.name  
--,索引字段位置=d.colid  
--FROM   sysindexes   a  
--JOIN   sysindexkeys   b   ON   a.id=b.id   AND   a.indid=b.indid  
--JOIN   sysobjects   c   ON   b.id=c.id  
--JOIN   syscolumns   d   ON   b.id=d.id   AND   b.colid=d.colid  
--WHERE   a.indid   NOT IN(0,255)   
--AND   c.name='SearchEnginePublish' --查指定表  
--ORDER BY   c.name,a.name,d.name 

--获取表及字段信息
select * from sysobjects where xtype='u' 
select COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='_keywordInfo'

 SET STATISTICS PROFILE ON
 SET STATISTICS IO ON
 SET STATISTICS TIME ON
 GO
--你的SQL脚本开始
--SELECT [TestCase] FROM [TestCaseSelect]
--你的SQL脚本结束
GO
 SET STATISTICS PROFILE OFF
 SET STATISTICS IO OFF
 SET STATISTICS TIME OFF
 
 USE master
 GO
 --bs开发框架数据库用户连接数量
 --如果要指定数据库就把注释去掉
 SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'
 SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50
 
 GO
 --前10最耗CPU会话
  SELECT TOP 10 [session_id],[request_id],[start_time] AS '开始时间',[status] AS '状态', [command] AS '命令' ,dest.[text] AS 'sql语句', DB_NAME([database_id]) AS '数据库名',
  [blocking_session_id] AS '正在阻塞其他会话的会话ID',[wait_type] AS '等待资源类型',[wait_time] AS '等待时间',[wait_resource] AS '等待的资源',[reads] AS '物理读次数',
 [writes] AS '写次数',[logical_reads] AS '逻辑读次数',[row_count] AS '返回结果行数'
 FROM sys.[dm_exec_requests] AS der 
 CROSS APPLY 
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
 WHERE [session_id]>50 --AND DB_NAME(der.[database_id])='gposdb'  
 ORDER BY [cpu_time] DESC
 
 Go
 --查看CPU数和user scheduler数目
 SELECT cpu_count,scheduler_count FROM sys.dm_os_sys_info
 --查看最大工作线程数
 SELECT max_workers_count FROM sys.dm_os_sys_info
 
 GO
 --查询worker是否用完,当达到最大线程数的时候就要检查blocking了
 SELECT
 scheduler_address,
 scheduler_id,
 cpu_id,
 status,
 current_tasks_count,
 current_workers_count,active_workers_count
 FROM sys.dm_os_schedulers
 
 --对照下面这个表
--Web开发框架各种CPU和SQLSERVER版本组合自动配置的最大工作线程数
--CPU数                    32位计算机                        64位计算机
--<=4                       256                                   512
--  8                       288                                   576
-- 16                       352                                   704
-- 32                       480                                   960
GO
--结合[sys].[dm_os_wait_stats]视图,如果当前SQLSERVER里面没有任何等待资源,那么下面的SQL语句不会显示任何结果
 SELECT TOP 10
  [session_id],
  [request_id],
  [start_time] AS '开始时间',
  [status] AS '状态',
  [command] AS '命令',
  dest.[text] AS 'sql语句', 
  DB_NAME([database_id]) AS '数据库名',
  [blocking_session_id] AS '正在阻塞其他会话的会话ID',
  der.[wait_type] AS '等待资源类型',
  [wait_time] AS '等待时间',
  [wait_resource] AS '等待的资源',
  [dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
  [reads] AS '物理读次数',
  [writes] AS '写次数',
  [logical_reads] AS '逻辑读次数',
  [row_count] AS '返回结果行数'
  FROM sys.[dm_exec_requests] AS der 
  INNER JOIN [sys].[dm_os_wait_stats] AS dows 
  ON der.[wait_type]=[dows].[wait_type]
  CROSS APPLY 
  sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
  WHERE [session_id]>50  
  ORDER BY [cpu_time] DESC
  
  GO
  --查询CPU占用高的语句
  SELECT TOP 10
     total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
     execution_count,
     (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
           THEN LEN(CONVERT(nvarchar(max), text)) * 2
           ELSE statement_end_offset
        END - statement_start_offset)/2)
     FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
 FROM sys.dm_exec_query_stats
 ORDER BY [avg_cpu_cost] DESC
 
 --查询缺失索引
 SELECT 
     DatabaseName = DB_NAME(database_id)
     ,[Number Indexes Missing] = count(*) 
 FROM sys.dm_db_missing_index_details
 GROUP BY DB_NAME(database_id)
 ORDER BY 2 DESC;

 SELECT  TOP 10 
          [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
          , avg_user_impact
          , TableName = statement
          , [EqualityUsage] = equality_columns 
          , [InequalityUsage] = inequality_columns
          , [Include Cloumns] = included_columns
  FROM        sys.dm_db_missing_index_groups g 
  INNER JOIN    sys.dm_db_missing_index_group_stats s 
        ON s.group_handle = g.index_group_handle 
 INNER JOIN    sys.dm_db_missing_index_details d 
        ON d.index_handle = g.index_handle
 ORDER BY [Total Cost] DESC;
 
--每秒死锁数量

SELECT  * FROM   sys.dm_os_performance_counters
WHERE  counter_name LIKE 'Number of Deadlocksc%';

--查询当前阻塞
WITH    CTE_SID ( BSID, SID, sql_handle )
          AS ( SELECT   blocking_session_id ,
                        session_id ,
                        sql_handle
               FROM     sys.dm_exec_requests
               WHERE    blocking_session_id <> 0
               UNION ALL
               SELECT   A.blocking_session_id ,
                        A.session_id ,
                        A.sql_handle
               FROM     sys.dm_exec_requests A
                        JOIN CTE_SID B ON A.SESSION_ID = B.BSID
             )
    SELECT  C.BSID ,
            C.SID ,
            S.login_name ,
            S.host_name ,
            S.status ,
            S.cpu_time ,
            S.memory_usage ,
            S.last_request_start_time ,
            S.last_request_end_time ,
            S.logical_reads ,
            S.row_count ,
            q.text
    FROM    CTE_SID C 
            JOIN sys.dm_exec_sessions S ON C.sid = s.session_id
            CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
    ORDER BY sid
    
----显示所有活动进程:
--use master 
--go 
--exec sp_who 'active'
--go

----显示会话ID标识的特定进程

--use master 
--go 
--exec sp_who '10'
--go

----Proc_UpdateWebsiteAccountState 

----删除阻塞的进程
--use master 
--go 
--kill 00 
--go
在MSSql高级--阻塞和死锁文章中我们讲解了B/S开发框架相关项目数据发生阻塞和死锁怎么办,这个更高级的解决性能的话题-运行sql server cpu占用高问题排查方法,大家都明白了么。
标签: Web开发框架SqlServer专辑标签

网站&系统开发技术学习交流群:463167176

本站文章除注明转载外,均为本站原创或翻译,欢迎任何形式的转载,但请务必注明出处,尊重他人劳动,共创和谐网络环境。
转载请注明:文章转载自:软件开发框架 » sqlserver cpu占用高排查_BS开发框架
本文标题:sqlserver cpu占用高排查_BS开发框架
本文地址:http://www.hocode.com/OrgTec/DB/0012.html

相关文章: MSSql高级--阻塞和死锁

电话
电话 18718672256

扫一扫
二维码