Skip to content

Add queries for collecting query store runtime and wait statistics #8465

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
IgorKuchmienko wants to merge 9 commits into from
Closed

Add queries for collecting query store runtime and wait statistics #8465

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
9 commits
Select commit Hold shift + click to select a range
ccd678d
Add queries for collecting query store runtime statistics and query s…
IgorKuchmienko Nov 23, 2020
d2febb2
Fix 'pass lock by value' and 'column [measurement] truncated' issues
IgorKuchmienko Nov 24, 2020
fe89046
Remove last_compile_batch_sql_handle. Return start_time and end_time …
IgorKuchmienko Dec 15, 2020
3cf25f6
In query store queries, add grouping by plan_id, execution_type, runt…
IgorKuchmienko Dec 23, 2020
c74f99e
Add comments
IgorKuchmienko Dec 27, 2020
8e1dd45
Add metric page_server_io_reads
IgorKuchmienko Dec 27, 2020
ba9116f
Merge branch 'master' into add-querystore
IgorKuchmienko Dec 28, 2020
39383f8
Change string length from max to 20 when converting binary hashes to …
IgorKuchmienko Dec 28, 2020
c298065
Change string length from max to 30 when converting time to string
IgorKuchmienko Dec 28, 2020
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
12 changes: 8 additions & 4 deletions plugins/inputs/sqlserver/README.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -66,7 +66,7 @@ GO
# include_query = []

## A list of queries to explicitly ignore.
# exclude_query = []
# exclude_query = ["AzureSQLDBQueryStoreRuntimeStatistics", "AzureSQLDBQueryStoreWaitStatistics"]

## Queries enabled by default for database_type = "AzureSQLManagedInstance" are -
## AzureSQLMIResourceStats, AzureSQLMIResourceGovernance, AzureSQLMIDatabaseIO, AzureSQLMIServerProperties, AzureSQLMIOsWaitstats,
Expand All @@ -76,7 +76,7 @@ GO

# include_query = []

# exclude_query = []
# exclude_query = ["AzureSQLMIQueryStoreRuntimeStatistics", "AzureSQLMIQueryStoreWaitStatistics"]

## Queries enabled by default for database_type = "SQLServer" are -
## SQLServerPerformanceCounters, SQLServerWaitStatsCategorized, SQLServerDatabaseIO, SQLServerProperties, SQLServerMemoryClerks,
Expand Down Expand Up @@ -236,8 +236,10 @@ These are metrics for Azure SQL Database (single database) and are very similar
- AzureSQLDBServerProperties: Relevant Azure SQL relevant properties from such as Tier, #Vcores, Memory etc, storage, etc.
- AzureSQLDBWaitstats: Wait time in ms from `sys.dm_db_wait_stats`, number of waiting tasks, resource wait time, signal wait time, max wait time in ms, wait type, and wait category. The waits are categorized using the same categories used in Query Store. These waits are collected only as of the end of the a statement. and for a specific database only.
- *AzureSQLOsWaitstats*: Wait time in ms from `sys.dm_os_wait_stats`, number of waiting tasks, resource wait time, signal wait time, max wait time in ms, wait type, and wait category. The waits are categorized using the same categories used in Query Store. These waits are collected as they occur and instance wide
- *AzureSQLDBRequests: Requests which are blocked or have a wait type from `sys.dm_exec_sessions` and `sys.dm_exec_requests`
- *AzureSQLDBSchedulers* - This captures `sys.dm_os_schedulers` snapshots.
- *AzureSQLDBRequests*: Requests which are blocked or have a wait type from `sys.dm_exec_sessions` and `sys.dm_exec_requests`
- *AzureSQLDBSchedulers*: This captures `sys.dm_os_schedulers` snapshots
- AzureSQLDBQueryStoreRuntimeStatistics: Collects queries runtime statistics from `sys.query_store_runtime_stats`
- AzureSQLDBQueryStoreWaitStatistics: Collects queries wait statistics from `sys.query_store_wait_stats`


#### database_type = "AzureSQLManagedInstance
Expand All @@ -250,6 +252,8 @@ These are metrics for Azure SQL Managed instance, are very similar to version 2
- AzureSQLMIOsWaitstats: Wait time in ms from `sys.dm_os_wait_stats`, number of waiting tasks, resource wait time, signal wait time, max wait time in ms, wait type, and wait category. The waits are categorized using the same categories used in Query Store. These waits are collected as they occur and instance wide
- AzureSQLMIRequests: Requests which are blocked or have a wait type from `sys.dm_exec_sessions` and `sys.dm_exec_requests`
- AzureSQLMISchedulers - This captures `sys.dm_os_schedulers` snapshots.
- AzureSQLMIQueryStoreRuntimeStatistics: Collects queries runtime statistics from `sys.query_store_runtime_stats`
- AzureSQLMIQueryStoreWaitStatistics: Collects queries wait statistics from `sys.query_store_wait_stats`

#### database_type = "SQLServer
- SQLServerDatabaseIO: IO stats from `sys.dm_io_virtual_file_stats`
Expand Down
Loading