<th id="v9g6b"><track id="v9g6b"></track></th>

  • <em id="v9g6b"><acronym id="v9g6b"></acronym></em>
    <progress id="v9g6b"><pre id="v9g6b"></pre></progress>
    <tbody id="v9g6b"></tbody>
    <button id="v9g6b"><acronym id="v9g6b"></acronym></button>
    <rp id="v9g6b"><ruby id="v9g6b"></ruby></rp>

        <dd id="v9g6b"></dd>

        <em id="v9g6b"></em>

          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.sajuice.com/OrgTec/DB/0012.html

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

          电话
          电话 18718672256

          扫一扫
          二维码
          本港台开奖 <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <蜘蛛词>| <文本链> <文本链> <文本链> <文本链> <文本链> <文本链>