トレースフラグを付けてデッドロックを記録する

SQL Serverにはトレースフラグを言うものを付けて、特定のエラーを記録する方法がある。
デットロックを後追いするにはいいかもしれない。

[code]
— 現在のトレースフラグ確認
DBCC TRACESTATUS (1204, 1222)
GO

— 結果
TraceFlag Status Global Session
1204 0 0 0 1222 0 0 0

— トレースフラグ設定
DBCC TRACEON (1204, 1222,-1)
GO

— 結果
TraceFlag Status Global Session
1204 1 1 0 1222 1 1 0

— トレースフラグ設定を消す場合
DBCC TRACEOFF (1204, 1222,-1)
GO
[/code]
カッコ内の番号は、MSサイトを参照

上記はオンラインで変更できるパラメータだが、起動時から有効にするには
SQL Server Configuration Managerで、

[code]
-T1204 -T1222
[/code]

を追加する。

SQLConfigurationManager.png

ジョブを設定を一括で変更する

とりあえず、沢山あるログシッピングのLSCopyとLSRestoreジョブの失敗時のアラートを飛ばしたい。
DBメールの設定と、オペレーターの設定は先に済ませておく。

[code]
USE msdb
GO
declare @job_copy nvarchar(50)
declare @job_restore nvarchar(50)
DECLARE C1_LSCOPY CURSOR FOR
SELECT name FROM dbo.sysjobs where name LIKE ‘LSCopy%’
DECLARE C1_LSRESTORE CURSOR FOR
SELECT name FROM dbo.sysjobs where name LIKE ‘LSRestore%’
OPEN C1_LSCOPY
FETCH NEXT FROM C1_LSCOPY INTO @job_copy
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC dbo.sp_update_job @job_name = @job_copy,
@notify_level_email=2,
@notify_email_operator_name=N’Admin’;
FETCH NEXT FROM C1_LSCOPY INTO @job_copy
END
OPEN C1_LSRESTORE
FETCH NEXT FROM C1_LSRESTORE INTO @job_restore
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC dbo.sp_update_job @job_name = @job_restore,
@notify_level_email=2,
@notify_email_operator_name=N’Admin’;
FETCH NEXT FROM C1_LSRESTORE INTO @job_restore
END
CLOSE C1_LSCOPY
CLOSE C1_LSRESTORE
DEALLOCATE C1_LSCOPY
DEALLOCATE C1_LSRESTORE
GO
[/code]

SQL Serverのslowqueryを監視する

前回覚えたSQL Serverの条件分岐を使って、nagios/icinga用のSlowQuery監視スクリプトを書いてみた。

前提として、こちらを参考に、SlowQuery取得の設定が必要。

NRPE経由で監視する場合は、MAX_PACKETBUFFER_LENGTH等の値の変更をしたら幸せになれます。

[bash]
#!/bin/bash
CMDNAME=`basename $0`
while getopts h:u:p:c:w: OPT
do
case $OPT in
"h" ) FLG_H="TRUE" ; VALUE_H="$OPTARG" ;;
"u" ) FLG_U="TRUE" ; VALUE_U="$OPTARG" ;;
"p" ) FLG_P="TRUE" ; VALUE_P="$OPTARG" ;;
"w" ) FLG_W="TRUE" ; VALUE_W="$OPTARG" ;;
"c" ) FLG_C="TRUE" ; VALUE_C="$OPTARG" ;;
* ) echo "Usage: $CMDNAME [-h HOST_NAME] [-u User] [-p PASS] [-c warnning] [-c critical]" 1>&2
exit 1 ;;
esac
done
_HOST=$VALUE_H
_USER=$VALUE_U
_PASS=$VALUE_P
_WARN=$VALUE_W
_CRIT=$VALUE_C
# 何分前の以降に出たSLOW LOGを対象とする
_TIMESTAMP=’00:05′
#echo "$_HOST, $_USER, $_PASS, $_WARN, $_CRIT"
export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
export FREETDSCONF=/etc/freetds/freetds.conf
# ここからT-SQL処理
TDSVER=8.0 /usr/local/freetds/bin/tsql -H $_HOST -p 1433 -U $_USER -P $_PASS << _EOF > /tmp/${_HOST}_slowlog.tmp
DECLARE @VERSION_FULL nvarchar(max);
DECLARE @VERSION int;
SET @VERSION_FULL = (SELECT convert (nvarchar(max),SERVERPROPERTY(‘productversion’)));
SET @VERSION = substring(@VERSION_FULL, 1, 2)
DECLARE @DATE VARCHAR(16);
SET @DATE = (select CONVERT(VARCHAR,GETDATE()-‘$_TIMESTAMP’,120));
IF @VERSION = 10
BEGIN
SELECT REPLACE((
SELECT
DATEADD(hour,9 , CAST(event_data as XML).value(‘(/event/@timestamp)[1]’, ‘datetime’)) AS timestamp,
DB_NAME(CAST(event_data as XML).value(‘(/event/action[@name="database_id"]/value)[1]’, ‘sysname’)) AS database_name,
CAST(event_data as XML).value(‘(/event/action[@name="sql_text"]/value)[1]’, ‘nvarchar(max)’) AS sql_text,
CAST(event_data as XML).value(‘(/event/data[@name="duration"]/value)[1]’, ‘bigint’) AS duration,
CAST(event_data as XML).value(‘(/event/data[@name="cpu"]/value)[1]’, ‘bigint’) AS cpu_time
FOR XML PATH (”)), ‘ ‘, ‘ ‘) AS RESULT
from sys.fn_xe_file_target_read_file(
‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\Highload_Query*.xel’,
‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\Highload_Query*.xem’ ,
NULL,
NULL)
where DATEADD(hour,9 , CAST(event_data as XML).value(‘(/event/@timestamp)[1]’, ‘datetime’)) >= @DATE
END
ELSE
BEGIN
IF @VERSION = 11
BEGIN
SELECT
DATEADD(hour,9 , CAST(event_data as XML).value(‘(/event/@timestamp)[1]’, ‘datetime’)) AS timestamp,
DB_NAME(CAST(event_data as XML).value(‘(/event/action[@name="database_id"]/value)[1]’, ‘sysname’)) AS database_name,
CAST(event_data as XML).value(‘(/event/data[@name="batch_text"]/value)[1]’, ‘nvarchar(max)’) AS batch_textt,
CAST(event_data as XML).value(‘(/event/data[@name="duration"]/value)[1]’, ‘bigint’) AS duration,
CAST(event_data as XML).value(‘(/event/data[@name="cpu_time"]/value)[1]’, ‘bigint’) AS cpu_time
from sys.fn_xe_file_target_read_file(
‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\Highload_Query*.xel’,
NULL,
NULL,
NULL)
where DATEADD(hour,9 , CAST(event_data as XML).value(‘(/event/@timestamp)[1]’, ‘datetime’)) >= @DATE;
END;
GO
quit
_EOF
IFS_BACKUP=$IFS
IFS=$’\n’
_TIME=(`cat /tmp/${_HOST}_slowlog.tmp | sed -n -e ‘s/.*\(.*\)<\/timestamp>.*/"\1"/p’`)
_DBNAME=(`cat /tmp/${_HOST}_slowlog.tmp | sed -n -e ‘s/.*\(.*\)<\/database_name>.*/"\1"/p’`)
_CPUTIME=(`cat /tmp/${_HOST}_slowlog.tmp |sed -n -e ‘s/.*\(.*\)<\/cpu_time>.*/"\1"/p’`)
_DURATION=(`cat /tmp/${_HOST}_slowlog.tmp | sed -n -e ‘s/.*\(.*\)<\/duration>.*/"\1"/p’`)
_QUERY=(`cat /tmp/${_HOST}_slowlog.tmp | tr ‘\n’ ‘ ‘| sed -e ‘s/<\/cpu_time>/<\/cpu_time>\n/g’| sed -n -e ‘s/.*\(.*\)<\/sql_text>.*/"\1"\n/p’`)
func_message (){
for (( i=0; $i<`echo ${#_TIME[@]}`; i++ ));
do
echo "———-"
echo "TIME: ${_TIME[$i]}"
echo "DBNAME: ${_DBNAME[$i]}"
echo "CPU_TIME: ${_CPUTIME[$i]} ms"
echo "DURATION: ${_DURATION[$i]} us"
echo "${_QUERY[$i]}"
done
IFS=$IFS_BACKUP
}
# check
if [ $_CRIT -le `echo ${#_TIME[@]}` ]
then
echo "CRIT: SlowQuery is `echo ${#_TIME[@]}` | SlowQuerys=`echo ${#_TIME[@]}`;$_WARN;$_CRIT"
func_message
exit 2
elif [ $_WARN -le `echo ${#_TIME[@]}` ]
then
echo "WARN: SlowQuery is `echo ${#_TIME[@]}` | SlowQuerys=`echo ${#_TIME[@]}`;$_WARN;$_CRIT"
func_message
exit 1
elif [ $_WARN -gt `echo ${#_TIME[@]}` ]
then
echo "OK: SlowQuery is `echo ${#_TIME[@]}` | SlowQuerys=`echo ${#_TIME[@]}`;$_WARN;$_CRIT"
exit 0
else
echo "status unknown"
exit 3
fi
[/bash]

使い方
./check_sqlserver_slowquery -h ホスト名orIPアドレス -u ユーザー名 -p パスワード -w ワーニング閾値 -c クリティカル閾値

閾値以下の場合は
OK: SlowQuery is 0 | SlowQuerys=0;3;4
とか表示される。

閾値以上の場合は
CRIT: SlowQuery is 6 | SlowQuerys=6;3;4
———-
TIME: “2016-01-08T10:42:00.427”
DBNAME: “hoge”
CPU_TIME: “3932” ms
DURATION: “4011259” us
“~~遅いクエリ~~”
———-
TIME: “2016-01-08T10:42:07.283”
DBNAME: “hoge”
CPU_TIME: “4431” ms
DURATION: “4481414” us
“~~遅いクエリ~~”

等が表示されます。

SQL Serverのversionによって処理を分けたい場合

SQL Serverのversionによって処理を分けたい場合
もうちょっと細かいversionで分けられると思うけど、
今はSQL Server 2008 か 2012 か 2014 かぐらいで分けれれば問題ない。

[code]declare @VERSION_FULL nvarchar(max);
declare @VERSION int;
SET @VERSION_FULL = (SELECT convert (nvarchar(max),SERVERPROPERTY(‘productversion’)));
SET @VERSION = substring(@VERSION_FULL, 1, 2)
IF @VERSION = 10
— SQL SERVER 2008での処理
BEGIN
PRINT ‘2008’;
END
ELSE
BEGIN
IF @VERSION = 11
— SQL SERVER 2012での処理
PRINT ‘2012’;
ELSE
— SQL SERVER 2014での処理
PRINT ‘2014’;
END ;
GO
[/code]