MicrosoftWhy is the following query running periodically and hammering my databases & elastic pools with poor performance? SELECT SYSUTCDATETIME() AS sample_time_utc,
@@SERVERNAME AS logical_server_name,
DB_NAME() AS database_name,
dso.database_id,
dso.elastic_pool_name,
rg.logical_database_guid AS logical_database_id,
rg.physical_database_guid AS physical_database_id,
CASE rg.replica_role
WHEN 0 THEN 'Primary'
WHEN 1 THEN 'HA secondary'
WHEN 2 THEN 'Geo-replication forwarder'
WHEN 3 THEN 'Named secondary'
ELSE NULL
END
AS replica_type,
TRY_CAST(DATABASEPROPERTYEX(DB_NAME(), 'ReplicaID') AS nvarchar(128)) AS replica_id,
CAST(SWITCHOFFSET(CONVERT(varchar(30), si.sqlserver_start_time, 121) + RIGHT(SYSDATETIMEOFFSET(), 6), '+00:00') AS datetime) AS database_engine_start_time_utc,
i.object_id,
i.index_id,
p.partition_number,
p.hobt_id,
p.columnstore_internal_rowset_type,
OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
OBJECT_NAME(i.object_id) AS object_name,
i.name AS index_name,
i.type_desc AS index_type,
IIF(p.columnstore_internal_rowset_type IS NULL, ic.key_columns, NULL) AS key_columns,
IIF(p.columnstore_internal_rowset_type IS NULL, ic.included_columns, NULL) AS included_columns,
IIF(p.columnstore_internal_rowset_type IS NULL, ic.columnstore_order_columns, NULL) AS columnstore_order_columns,
IIF(p.columnstore_internal_rowset_type IS NULL, ic.partitioning_column, NULL) AS partitioning_column,
i.is_unique,
i.ignore_dup_key,
i.is_primary_key,
i.is_unique_constraint,
i.fill_factor,
i.is_padded,
i.is_disabled,
i.allow_row_locks,
i.allow_page_locks,
i.has_filter,
i.filter_definition,
i.auto_created,
i.optimize_for_sequential_key,
p.partition_row_count,
p.partition_compression,
p.is_xml_compressed,
CAST(p.in_row_partition_size_mb AS decimal(19,6)) AS in_row_partition_size_mb,
CAST(p.row_overflow_partition_size_mb AS decimal(19,6)) AS row_overflow_partition_size_mb,
CAST(p.lob_partition_size_mb AS decimal(19,6)) lob_partition_size_mb,
ios.leaf_insert_count,
ios.leaf_delete_count,
ios.leaf_update_count,
ios.leaf_ghost_count,
ios.nonleaf_insert_count,
ios.nonleaf_delete_count,
ios.nonleaf_update_count,
ios.leaf_allocation_count,
ios.nonleaf_allocation_count,
ios.leaf_page_merge_count,
ios.nonleaf_page_merge_count,
ios.range_scan_count,
ios.singleton_lookup_count,
ios.forwarded_fetch_count,
ios.lob_fetch_in_pages,
ios.lob_fetch_in_bytes,
ios.row_overflow_fetch_in_pages,
ios.row_overflow_fetch_in_bytes,
ios.column_value_push_off_row_count,
ios.column_value_pull_in_row_count,
ios.row_lock_wait_count,
ios.row_lock_wait_in_ms AS row_lock_wait_ms,
ios.page_lock_wait_count,
ios.page_lock_wait_in_ms AS page_lock_wait_ms,
ios.index_lock_promotion_attempt_count,
ios.index_lock_promotion_count,
ios.page_latch_wait_count,
ios.page_latch_wait_in_ms AS page_latch_wait_ms,
ios.page_io_latch_wait_count,
ios.page_io_latch_wait_in_ms AS page_io_latch_wait_ms,
ios.page_compression_attempt_count,
ios.page_compression_success_count
FROM sys.indexes AS i
INNER JOIN (
SELECT p.object_id,
p.index_id,
p.partition_number,
p.hobt_id,
p.columnstore_internal_rowset_type,
p.data_compression_desc AS partition_compression,
ps.row_count AS partition_row_count,
p.is_xml_compressed,
ps.total_partition_size_mb,
ps.in_row_partition_size_mb,
ps.row_overflow_partition_size_mb,
ps.lob_partition_size_mb
FROM #index_metadata_partition AS p
LEFT JOIN #index_metadata_partition_agg_stats AS ps /* there are no rows for memory-optimized heaps, use an outer join */
ON ps.partition_id = p.partition_id
AND
ps.object_id = p.object_id
AND
ps.index_id = p.index_id
AND
ps.partition_number = p.partition_number
) AS p
ON i.object_id = p.object_id
AND
i.index_id = p.index_id
OUTER APPLY (
SELECT ic.index_id,
STRING_AGG(
CAST(
IIF(
ic.key_ordinal > 0,
CONCAT(
QUOTENAME(COL_NAME(ic.object_id, ic.column_id)),
IIF(ic.is_descending_key = 1, ' desc', ' asc')
),
NULL
) AS nvarchar(max)
), ', '
)
WITHIN GROUP (ORDER BY ic.key_ordinal ASC) AS key_columns,
STRING_AGG(
CAST(
IIF(
ic.is_included_column = 1,
QUOTENAME(COL_NAME(ic.object_id, ic.column_id)),
NULL
) AS nvarchar(max)
), ', '
)
WITHIN GROUP (ORDER BY ic.key_ordinal ASC, ic.column_store_order_ordinal ASC, ic.index_column_id ASC) AS included_columns,
STRING_AGG(
CAST(
IIF(
ic.column_store_order_ordinal > 0,
QUOTENAME(COL_NAME(ic.object_id, ic.column_id)),
NULL
) AS nvarchar(max)
), ', '
)
WITHIN GROUP (ORDER BY ic.key_ordinal ASC, ic.column_store_order_ordinal ASC) AS columnstore_order_columns,
MAX(IIF(ic.partition_ordinal > 0, COL_NAME(ic.object_id, ic.column_id), NULL)) AS partitioning_column
FROM sys.index_columns AS ic
WHERE ic.object_id = i.object_id
AND
ic.index_id = i.index_id
GROUP BY ic.index_id
) AS ic
CROSS APPLY (
SELECT TOP (1)
logical_database_guid,
physical_database_guid,
replica_role
FROM sys.dm_user_db_resource_governance
WHERE database_id = DB_ID()
) AS rg
CROSS JOIN sys.dm_os_sys_info AS si
CROSS JOIN #database_service_objective AS dso
LEFT HASH JOIN sys.dm_db_index_operational_stats(DB_ID(), default, default, default) AS ios
ON i.object_id = ios.object_id
AND
i.index_id = ios.index_id
AND
p.partition_number = ios.partition_number
AND
p.hobt_id = ios.hobt_id
WHERE i.type_desc IN ('HEAP','CLUSTERED','NONCLUSTERED','CLUSTERED COLUMNSTORE','NONCLUSTERED COLUMNSTORE')
AND
i.is_hypothetical = 0
AND
OBJECTPROPERTY(i.object_id, 'IsMSShipped') = 0
AND
OBJECT_SCHEMA_NAME(i.object_id) IS NOT NULL
AND
OBJECT_NAME(i.object_id) IS NOT NULL
OPTION (MAXDOP 4, USE HINT ('FORCE_DEFAULT_CARDINALITY_ESTIMATION','QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160','ENABLE_QUERY_OPTIMIZER_HOTFIXES'))
MicrosoftHi Keith_L_Corser​ - thanks for the question. This query is used to collect the data in the sqldb_database_index_metadata dataset, which provides monitoring data about index configuration and usage. It runs every 30 minutes on each database that you added as a SQL target to your watcher.
To troubleshoot any performance impact, we suggest that you open a support case. You can also email us at mailto:dbwatcherfeedback@microsoft.com with more details about the impact you are seeing, and the configuration of your databases and pools (the number of databases per pool, the compute size of the pool, etc.)
For background on elastic pool monitoring, please see Data collection in elastic pools.
