CPU高負荷時に統計情報更新

順調に運用しているSQLServerのCPU利用率が突然高くなり、クエリの応答速度が悪くなる時があります。

とりあえずの対策として、インデックスデフラグや統計情報を更新すると改善する場合があります。CPU負荷を確認してsp_updatestatsを実行するクエリをジョブに設定し、定期的に実行しておくと安心かもしれません。

大きなテーブルを利用しているクエリで発生しているのであれば、トレースフラグ2371を設定するのも有効かもしれません。

参考:http://blogs.msdn.com/b/jpsql/archive/2013/06/17/sql-server-2012-2008-r2-colmodctr.aspx

CPU利用率の10分間の平均が70%以上で、全ユーザDBの統計情報を更新します。

-------------------------------------------------------------------------------------
--CPU使用率～統計情報更新
-------------------------------------------------------------------------------------

SET NOCOUNT  ON
SET QUOTED_IDENTIFIER ON

	DECLARE @ts_now BIGINT
	DECLARE @cpu_usage INT
	DECLARE @dbename VARCHAR (255)

	SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, (cpu_ticks / ms_ticks))
	FROM sys.dm_os_sys_info
	
	SELECT 
		@cpu_usage = avg(SQLProcessUtilization)
	FROM 
		(SELECT 
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
			TIMESTAMP
		FROM (
			SELECT TIMESTAMP, CONVERT(XML, record) AS record 
			FROM sys.dm_os_ring_buffers 
			WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
			AND record LIKE '%<SystemHealth>%') AS x
		) AS y 
	WHERE DATEADD(ms, -1 * (@ts_now - [TIMESTAMP]), GETDATE()) >=DATEADD(MINUTE,-10,GETDATE())

	--70%未満であれば、終了
	IF @cpu_usage < 70
	BEGIN
		--問題なければ
		GOTO ENDLABEL

	END

	RAISERROR ('%s', 10, 1 ,'CPU使用率が高いため、統計情報を更新します' ) WITH LOG


--統計情報更新
-- Declare cursor for list
DECLARE dbname CURSOR FOR
	SELECT [name] FROM sys.databases
	WHERE  is_read_only = 0 AND database_id > 4

-- Open the cursor
OPEN dbname

-- loop
FETCH NEXT
   FROM dbname
   INTO @dbename

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT 'DB = ' + @dbename

	EXEC ('use [' + @dbename + '] EXEC sp_updatestats ;' );

	-- loop
	FETCH NEXT
	   FROM dbname
	   INTO @dbename

END

CLOSE dbname
DEALLOCATE dbname

--終了
ENDLABEL:

Tweet