拡張イベントを使ってスロークエリを探す で出力されたファイルをkibanaに取り込んでスロークエリの確認をします。
kibanaのインストールはSQLServer+probespawner+Elasticsearch+kibanaの手順で行いました。
probespawner のjsonは添付のように設定しました。
{
"input": ["DatabaseInput"],
"DatabaseInput": {
"probemodule": { "module": "cooldbprobe", "name" : "DatabaseProbe" },
"output": ["ElasticsearchOutput"],
"interval": 60,
"url": "jdbc:sqlserver://[対象のDBサーバ]:1433;integratedSecurity=true;databaseName=Master",
"driverClassName": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
"username": "",
"password": "",
"minIdle": "2",
"maxIdle": "2",
"maxAge": "86400",
"maxCycles": 1,
"sql": [
{
"statement": "select CONVERT(varchar(30), xml_data.value('(/event/@timestamp)[1]','datetime') , 127) as '@timestamp' , @@SERVERNAME as servername , @@SERVICENAME as servicename , xml_data.value('(event/@name)[1]', 'varchar(50)') AS class , case when CHARINDEX('Server 2012',@@VERSION,1) > 1 OR xml_data.value('(event/@name)[1]', 'varchar(50)') <>'rpc_completed' then xml_data.value('(/event/data[@name=''duration'']/value)[1]','bigint')/1000000.0 else xml_data.value('(/event/data[@name=''duration'']/value)[1]','bigint')/1000.0 end duration_sec , xml_data.value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)') sql_text , xml_data.value('(/event/data[@name=''statement'']/value)[1]','varchar(max)') statement , xml_data.value('(/event/action[@name=''client_hostname'']/value)[1]','varchar(max)') hostname , xml_data.value('(/event/action[@name=''username'']/value)[1]','varchar(max)') loginname from (select object_name as event, CONVERT(xml, event_data) as xml_data from sys.fn_xe_file_target_read_file ('G:\\work\\ExtendedEvents_log\\slowquery*.xel', 'G:\\work\\ExtendedEvents_log\\slowquery*.xem', null, null) ) v where DATEADD(hh, 9, xml_data.value('(/event/@timestamp)[1]','datetime')) >= convert(varchar(15),dateadd(MI,-20,getdate()),120)+'0:00' and DATEADD(hh, 9, xml_data.value('(/event/@timestamp)[1]','datetime')) < convert(varchar(15),dateadd(MI,-10,getdate()),120)+'0:00' order by '@timestamp' desc"
}
]
},
"ElasticsearchOutput": {
"class": "elasticsearch",
"outputmodule": { "module": "jelh2", "name" : "Elasticsearch" },
"cluster" : "[設定したクラスタ名]",
"host" : "[ElasticsearchのIPアドレス]",
"port" : 9300,
"bulkActions": 1000,
"bulkSize": 107374182400,
"indexPrefix": "sqlserver",
"indexSuffix": "@timestamp",
"type": "jdbc",
"index_settings": {
"index" : {
"analysis" : {
"analyzer" : {
"ana_analyzer" : {
"type" : "standard",
"tokenizer": "standard",
"filter": ["standard", "asciifolding", "lowercase"]
},
"sortable": {
"tokenizer": "standard",
"filter": ["standard", "asciifolding", "lowercase"]
}
}
}
}
},
"type_mapping": {
"jdbc": {
"properties" : {
"@timestamp" : { "type" : "date" },
"servername" : { "type" : "string", "index" : "not_analyzed", "omit_norms": "true" },
"servicename" : { "type" : "string", "index" : "not_analyzed", "omit_norms": "true" },
"class" : { "type" : "string", "index" : "not_analyzed", "omit_norms": "true" },
"sql_text" : {
"type" : "multi_field",
"fields" : {
"sql_text" : {"type" : "string", "index" : "analyzed", "analyzer": "ana_analyzer"},
"disp" : {"type" : "string", "index" : "not_analyzed"}
}
},
"statement" : { "type" : "string", "index" : "not_analyzed", "omit_norms": "true" },
"hostname" : { "type" : "string", "index" : "not_analyzed", "omit_norms": "true" },
"loginname" : { "type" : "string", "index" : "not_analyzed", "omit_norms": "true" },
"value" : {"type" : "double" }
}
}
}
}
}
下記の場所を環境に合わせて修正してください。
8行目
“url”: “jdbc:sqlserver://[対象のDBサーバ]:1433;integratedSecurity=true;databaseName=Master”,
※Windows認証にしましたので、バッチはDBに接続できるユーザで実行します。
25,26行目
“cluster” : “[設定したクラスタ名]”,
“host” : “[ElasticsearchのIPアドレス]”,
その他
18行目のstatement句のSQL文内にスロークエリを取得する時間を指定してあります。
where句で抽出対象時間を20分前から10分前までにしています。
あまり直近時間にすると、拡張イベントのファイル出力が非同期のため、データが落ちてない場合があります。
取得バッチをOSのタスクに登録して、有効/無効化を切り替えたかったので
15行目の maxCyclesを1として、処理を1回で終了するようにしています。
取得バッチ
G:\App\probespawner-master\test.bat
cd /d "%~dp0" probespawner.bat probespawner_sql_sample.json
バッチをタスクに登録して、定期的に実行するとよいと思います。
取得することができました。
sql_text をanalyzedに設定したので、ある程度の文字列検索もできるようです。
表示に利用する時は、sql_text.dispを使います。
まだまだ勉強中なので間違っていたり、無駄な部分があるかもしれません。。。
