SQLserver数据库老是卡死（CPU%99），有没有办法让数据库卡住的时候，立马停止执行。

[dxlong8]

为什么SQLserver数据库只要卡死了，cpu立马飙到99%，导致其他用户都无法访问。
我一直在想一个问题，你卡死了就立马停止执行啊，还一直占着资源干什么，别的正常的访问都无法进行，是不是太垃圾了。
有没有办法，让数据库卡住的时候，立马停止执行。释放资源。

[dxlong8]

大家看看这个图，一个小时以内，居然卡住了27分钟，网站一直处于打不开的状态

[dxlong8]

[吉普赛的歌]

在服务器上执行： 1. 最近执行的10 条慢SQL:

SELECT TOP 10 OBJECT_NAME(qt.objectid, qt.dbId)  AS procName,
       DB_NAME(qt.dbId)                   AS [db_name],
       qt.text                            AS SQL_Full,
       SUBSTRING(
           qt.text,
           (qs.statement_start_offset / 2) + 1,
           (
               (
                   CASE statement_end_offset
                        WHEN -1 THEN DATALENGTH(qt.text)
                        ELSE qs.statement_end_offset
                   END 
                   - qs.statement_start_offset
               ) / 2
           ) + 1
       )                                  AS SQL_Part --统计对应的部分语句
       ,
       qs.creation_time,
       qs.last_execution_time,
       qs.execution_count,
       qs.last_elapsed_time / 1000000     AS lastElapsedSeconds,
       qs.last_worker_time / 1000000      AS lastCpuSeconds,
       CAST(
           qs.total_elapsed_time / 1000000.0 / (
               CASE 
                    WHEN qs.execution_count = 0 THEN -1
                    ELSE qs.execution_count
               END
           ) AS DECIMAL(28, 2)
       )                                  AS avgDurationSeconds,
       CAST(qs.last_logical_reads AS BIGINT) * 1.0 / (1024 * 1024) * 8060 AS 
       lastLogicReadsMB,
       qs.last_logical_reads,
       qs.plan_handle
FROM   sys.dm_exec_query_stats qs
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
WHERE  qs.last_execution_time >= CONVERT(CHAR(10),GETDATE(),120)+' 08:00'	--今天8点之后的慢SQL
       AND qs.last_elapsed_time >= 3 * 1000 * 1000							--只取执行时间大于 3 秒的记录
       AND qt.[text] NOT LIKE '%Proc_DBA%'
ORDER BY
       qs.last_worker_time DESC

2. 阻塞查询：在数据库服务器cpu高的时候，立即执行这个存储过程， 看下有没有记录。

-- =============================================
-- Author:		yenange
-- Create date: 2014-11-18
-- Description:	阻塞预警
-- =============================================
CREATE PROCEDURE [dbo].[Proc_DBA_BlockingWarning] 
	@BlockingWarning INT = 100	--被阻塞的会话数大于@BlockingWarning就预警
AS
BEGIN
	SET NOCOUNT ON
	--1. 定义表变量，并将阻塞和被阻塞的数据放入表变量
	DECLARE @t TABLE (
		SPID SMALLINT,
		DBName NVARCHAR(128),
		Remark NCHAR(3),
		[ProgramName] nchar(128),
		[LoginName] nchar(128),
		HostName nchar(128),
		[Status] nchar(30),
		BlockedBy SMALLINT,
		LoginTime DATETIME,
		QUERY nvarchar(max)
	)
	INSERT INTO @t (
		SPID,
		DBName,
		Remark,
		[ProgramName],
		[LoginName],
		HostName,
		[Status],
		BlockedBy,
		LoginTime,
		QUERY
	)
	SELECT  
	   SPID=p.spid,
	   DBName = convert(VARCHAR(20),d.name),
	   Remark = CASE WHEN p.blocked>0 THEN '被阻塞' else '阻塞源' end,
	   ProgramName = program_name,
	   LoginName = convert(CHAR(20),l.name),
	   HostName = convert(CHAR(20),hostname),
	   Status = p.status,
	   BlockedBy = p.blocked,
	   LoginTime = login_time,
	   QUERY = TEXT
	FROM   MASTER.dbo.sysprocesses p
		   LEFT JOIN MASTER.dbo.sysdatabases d
			 ON p.dbid = d.dbid
		   LEFT JOIN MASTER.dbo.syslogins l
			 ON p.sid = l.sid
		   CROSS APPLY sys.dm_exec_sql_text(sql_handle)
	WHERE  (p.blocked = 0
		   AND EXISTS (SELECT 1
					   FROM   MASTER.dbo.sysprocesses p1
					   WHERE  p1.blocked = p.spid))
		   OR (p.blocked>0)

	--2. 如果被阻塞的数量大于设置参数，则
	DECLARE @Warning INT
	IF (SELECT COUNT(1) FROM @t WHERE BlockedBy>0)>=@BlockingWarning
		BEGIN
			SET @Warning = 1
		END
	ELSE
		BEGIN
			SET @Warning = 0		
		END

	SELECT @Warning AS Warning,
		SPID,
		DBName,
		Remark,
		[ProgramName],
		[LoginName],
		HostName,
		[Status],
		BlockedBy,
		LoginTime,
		QUERY 
	FROM @t
	SET NOCOUNT OFF
END

第1个可以过后再查， 第2个必须在卡的时候立即查。 知道了什么慢， 才好对症下药。

[Seagull16]

DBAs are able to kill it based on the SPID. It must be dead lock or block there.

[dxlong8]

忙了两天，总算解决了，把每个表的索引重新生成一便，然后补充了几个索引，就解决了

[dxlong8]

附上 查询却索引的脚本，大家需要的时候可以试试 SELECT [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

[车找人找车平台]

你的拼车网站还在弄吗？