Skip to content

Instantly share code, notes, and snippets.

@MasayukiOzawa

MasayukiOzawa/00-query.conf

Last active August 1, 2018 14:04
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save MasayukiOzawa/ec54e0b6859592c28992e0c6b97b674c to your computer and use it in GitHub Desktop.
Save MasayukiOzawa/ec54e0b6859592c28992e0c6b97b674c to your computer and use it in GitHub Desktop.
Download ZIP
Elastick Stack + SQL Server
Raw
00-query.conf
input {
jdbc {
jdbc_driver_library => "/usr/lib/jvm/java-8-openjdk-amd64/jre/bin/mssql-jdbc-6.4.0.jre8.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://192.168.0.7;databaseName=master"
jdbc_user => "<User Id>"
jdbc_password => "<Password>"
schedule => "15 00 * * *"
statement => "exec usp_Get_Cache"
# columns_charset => {"query_hash" => "UTF-8"}
# columns_charset => {"query_plan_hash" => "UTF-8"}
tags => "00-query"
}
}
filter{
if "00-query" in [tags] {
mutate {
convert => ["collect_date", "string"]
}
date {
match => ["collect_date", "yyyy-MM-dd HH:mm:ss.SSS", "ISO8601"]
}
}
}
output{
if "00-query" in [tags] {
elasticsearch {
hosts => "localhost"
index => "00-query-%{+YYYYMMdd}"
manage_template => false
# template_name => "00-query"
}
}
# stdout{}
}
Raw
00-wait.conf
input {
jdbc {
jdbc_driver_library => "/usr/lib/jvm/java-8-openjdk-amd64/jre/bin/mssql-jdbc-6.4.0.jre8.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://192.168.0.7;databaseName=master"
jdbc_user => "<User Id>"
jdbc_password => "<Password>"
schedule => "*/5 * * * *"
statement => "SELECT * FROM sys.dm_os_wait_stats"
tags => "00-wait"
}
}
output{
if "00-wait" in [tags] {
elasticsearch {
hosts => "localhost"
index => "00-wait-%{+YYYYMMdd}"
manage_template => false
}
}
# stdout{}
}
Raw
00-wait.json
POST _template/00-wait
{
"settings": {
"number_of_shards": 1,
"number_of_replicas": 0
},
"index_patterns": "00-wait-*",
"mappings": {
"doc": {
"properties": {
"@timestamp": {
"type": "date"
},
"@version": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"max_wait_time_ms": {
"type": "long"
},
"signal_wait_time_ms": {
"type": "long"
},
"tags": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"wait_time_ms": {
"type": "long"
},
"wait_type": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 100
}
}
},
"waiting_tasks_count": {
"type": "long"
}
}
}
}
}
Raw
Logstash:SQL Query Template.json
POST _template/00-query
{
"settings": {
"number_of_shards": 1,
"number_of_replicas": 0
},
"index_patterns": "00-query-*",
"mappings": {
"doc": {
"properties": {
"@timestamp": {
"type": "date"
},
"@version": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"average elapsed time (ms)": {
"type": "float"
},
"average logical read count": {
"type": "long"
},
"average logical write": {
"type": "long"
},
"average physical read count": {
"type": "long"
},
"average worker time (ms)": {
"type": "float"
},
"collect_date": {
"type": "date"
},
"creation_time": {
"type": "date"
},
"db_name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"execution_count": {
"type": "long"
},
"last_dop": {
"type": "long"
},
"last_execution_time": {
"type": "date"
},
"last_grant_kb": {
"type": "long"
},
"last_ideal_grant_kb": {
"type": "long"
},
"last_reserved_threads": {
"type": "long"
},
"last_rows": {
"type": "long"
},
"last_used_grant_kb": {
"type": "long"
},
"max_dop": {
"type": "long"
},
"max_grant_kb": {
"type": "long"
},
"max_ideal_grant_kb": {
"type": "long"
},
"max_reserved_threads": {
"type": "long"
},
"max_rows": {
"type": "long"
},
"max_used_grant_kb": {
"type": "long"
},
"max_used_threads": {
"type": "long"
},
"min_dop": {
"type": "long"
},
"min_grant_kb": {
"type": "long"
},
"min_ideal_grant_kb": {
"type": "long"
},
"min_reserved_threads": {
"type": "long"
},
"min_rows": {
"type": "long"
},
"min_used_grant_kb": {
"type": "long"
},
"min_used_threads": {
"type": "long"
},
"mode": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"plan_generation_num": {
"type": "long"
},
"query_hash": {
"type": "long"
},
"query_plan_hash": {
"type": "long"
},
"stmt_text": {
"type": "text",
"analyzer": "kuromoji",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 30000
}
}
},
"tags": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"text": {
"type": "text",
"analyzer": "kuromoji",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 30000
}
}
},
"total_dop": {
"type": "long"
},
"total_elapsed_time (ms)": {
"type": "float"
},
"total_grant_kb": {
"type": "long"
},
"total_ideal_grant_kb": {
"type": "long"
},
"total_logical_reads (page)": {
"type": "long"
},
"total_logical_writes (page)": {
"type": "long"
},
"total_physical_reads (page)": {
"type": "long"
},
"total_reserved_threads": {
"type": "long"
},
"total_rows": {
"type": "long"
},
"total_used_grant_kb": {
"type": "long"
},
"total_used_threads": {
"type": "long"
},
"total_worker_time (ms)": {
"type": "float"
}
}
}
}
}
Raw
usp_Get_Cache.sql
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Get_Cache]
/*********************************************/
-- mode
-- 1 : 実行回数の高いクエリ
-- 2 : 平均 CPU 使用率の高いクエリ
-- 3 : 実行時間の高いクエリ
-- 4 : 平均読み取り回数の高いクエリ
-- 5 : 平均書き込み回数の高いクエリ
/*********************************************/
@mode int =1
AS
BEGIN
SELECT TOP 300
GETDATE() AS collect_date,
CASE @mode
WHEN 1 THEN 'HighExecution'
WHEN 2 THEN 'HighAvgCPU'
WHEN 3 THEN 'HighAvgElapsedTime'
WHEN 4 THEN 'HighAvgRead'
WHEN 5 THEN 'HighAvgWrite'
ELSE 'HighExecution'
END AS mode,
[total_elapsed_time] / [execution_count] / 1000.0 AS [Average Elapsed Time (ms)],
[total_worker_time] / [execution_count] / 1000.0 AS [Average Worker Time (ms)],
[total_physical_reads] / [execution_count] AS [Average Physical Read Count],
[total_logical_reads] / [execution_count] AS [Average Logical Read Count],
[total_logical_writes] / [execution_count] AS [Average Logical Write],
[total_elapsed_time] / 1000.0 AS [total_elapsed_time (ms)],
[total_worker_time] / 1000.0 AS [total_worker_time (ms)],
[total_physical_reads] AS [total_physical_reads (page)],
[total_logical_reads] AS [total_logical_reads (page)],
[total_logical_writes] AS [total_logical_writes (page)],
[execution_count],
[total_rows],
[last_rows],
[min_rows],
[max_rows],
[total_dop],
[last_dop],
[min_dop],
[max_dop],
[total_grant_kb],
[last_grant_kb],
[min_grant_kb],
[max_grant_kb],
[total_used_grant_kb],
[last_used_grant_kb],
[min_used_grant_kb],
[max_used_grant_kb],
[total_ideal_grant_kb],
[last_ideal_grant_kb],
[min_ideal_grant_kb],
[max_ideal_grant_kb],
[total_reserved_threads],
[last_reserved_threads],
[min_reserved_threads],
[max_reserved_threads],
[total_used_threads],
[last_used_threads]
[min_used_threads],
[max_used_threads],
[plan_generation_num],
[creation_time],
[last_execution_time],
CAST([query_hash] AS int) AS query_hash_int,
CAST([query_plan_hash] AS int) AS query_plan_hash_int,
DB_NAME(st.dbid) AS db_name,
REPLACE(REPLACE(REPLACE(SUBSTRING(text,
([statement_start_offset] / 2) + 1,
((CASE [statement_end_offset]
WHEN -1 THEN DATALENGTH(text)
ELSE [statement_end_offset]
END - [statement_start_offset]) / 2) + 1),CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' ') AS [stmt_text],
REPLACE(REPLACE(REPLACE([text],CHAR(13), ''), CHAR(10), ' '), CHAR(9), ' ') AS [text]
-- ,query_plan
FROM
[sys].[dm_exec_query_stats]
CROSS APPLY
[sys].[dm_exec_sql_text]([sql_handle]) AS st
CROSS APPLY
[sys].[dm_exec_query_plan]([plan_handle])
WHERE
last_execution_time >= (DATEADD(day, -1, GETDATE()))
ORDER BY
CASE @mode
WHEN 1 THEN [execution_count]
WHEN 2 THEN [total_worker_time] / [execution_count] / 1000.0
WHEN 3 THEN [total_elapsed_time] / [execution_count] / 1000.0
WHEN 4 THEN ([total_physical_reads] / [execution_count]) + ([total_logical_reads] / [execution_count])
WHEN 5 THEN [total_logical_writes] / [execution_count]
ELSE [execution_count]
END DESC
OPTION (RECOMPILE)
END
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment