Last active
August 1, 2018 14:04
-
-
Save MasayukiOzawa/ec54e0b6859592c28992e0c6b97b674c to your computer and use it in GitHub Desktop.
Elastick Stack + SQL Server
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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{} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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{} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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" | |
} | |
} | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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" | |
} | |
} | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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