SQL Server 诊断查询-(5)
Query #57 Buffer Usage-- Breaks down buffers used by current database by object (table, index) in the buffer cache
-- Note: This query could take some time on a busy instance
SELECT OBJECT_NAME(p.) AS , p.index_id,
CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS ,
COUNT(*) AS , p.Rows AS ,
p.data_compression_desc AS
FROM sys.allocation_units AS a WITH (NOLOCK)
INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK)
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON a.container_id = p.hobt_id
WHERE b.database_id = CONVERT(int,DB_ID())
AND p. > 100
GROUP BY p., p.index_id, p.data_compression_desc, p.
ORDER BY DESC OPTION (RECOMPILE);
-- Tells you what tables and indexes are using the most memory in the buffer cache
-- It can help identify possible candidates for data compression
Query #58 Table>
-- Get Table names, row counts, and compression status for clustered index or heap
SELECT OBJECT_NAME(object_id) AS ,
SUM(Rows) AS , data_compression_desc AS
FROM sys.partitions WITH (NOLOCK)
WHERE index_id < 2 --ignore the partitions from the non-clustered index if any
AND OBJECT_NAME(object_id) NOT LIKE N'sys%'
AND OBJECT_NAME(object_id) NOT LIKE N'queue_%'
AND OBJECT_NAME(object_id) NOT LIKE N'filestream_tombstone%'
AND OBJECT_NAME(object_id) NOT LIKE N'fulltext%'
AND OBJECT_NAME(object_id) NOT LIKE N'ifts_comp_fragment%'
AND OBJECT_NAME(object_id) NOT LIKE N'filetable_updates%'
AND OBJECT_NAME(object_id) NOT LIKE N'xml_index_nodes%'
AND OBJECT_NAME(object_id) NOT LIKE N'sqlagent_job%'
AND OBJECT_NAME(object_id) NOT LIKE N'plan_persist%'
GROUP BY object_id, data_compression_desc
ORDER BY SUM(Rows) DESC OPTION (RECOMPILE);
-- Gives you an idea of table sizes, and possible data compression opportunities
Query #59 Table Properties
-- Get some key table properties
SELECT OBJECT_NAME(t.) AS , p. AS , p.index_id,
p.data_compression_desc AS ,
t.create_date, t.lock_on_bulk_load, t.is_replicated, t.has_replication_filter,
t.is_tracked_by_cdc, t.lock_escalation_desc, t.is_memory_optimized, t.durability_desc, t.is_filetable,
t.temporal_type_desc, t.is_remote_data_archive_enabled, t.remote_data_archive_migration_state_desc, t.is_external -- new for SQL Server 2016
FROM sys.tables AS t WITH (NOLOCK)
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON t. = p.
WHERE OBJECT_NAME(t.) NOT LIKE N'sys%'
ORDER BY OBJECT_NAME(t.), p.index_id OPTION (RECOMPILE);
-- Gives you some good information about your tables
-- Is Memory optimized and durability description are Hekaton-related properties that were new in SQL Server 2014
-- temporal_type_desc, is_remote_data_archive_enabled, remote_data_archive_migration_state_desc, is_external are new in SQL Server 2016
Query #60 Statistics Update
-- When were Statistics last updated on all indexes?
SELECT SCHEMA_NAME(o.Schema_ID) + N'.' + o.NAME AS , o.type_desc AS ,
i.name AS , STATS_DATE(i., i.index_id) AS ,
s.auto_created, s.no_recompute, s.user_created, s.is_incremental, s.is_temporary,
st.row_count, st.used_page_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o. = i.
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i. = s.
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o. = st.
AND i. = st.
WHERE o. IN ('U', 'V')
AND st.row_count > 0
ORDER BY STATS_DATE(i., i.index_id) DESC OPTION (RECOMPILE);
-- Helps discover possible problems with out-of-date statistics
-- Also gives you an idea which indexes are the most active
Query #61 Volatile Indexes
-- Look at most frequently modified indexes and statistics
SELECT o.name AS , o., o.type_desc, s.name AS ,
s.stats_id, s.no_recompute, s.auto_created,
sp.modification_counter, sp.rows, sp.rows_sampled, sp.last_updated
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON s.object_id = o.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE o.type_desc NOT IN (N'SYSTEM_TABLE', N'INTERNAL_TABLE')
AND sp.modification_counter > 0
ORDER BY sp.modification_counter DESC, o.name OPTION (RECOMPILE);
Query #62 Index Fragmentation
-- Get fragmentation info for all indexes above a certain size in the current database
-- Note: This query could take some time on a very large database
SELECT DB_NAME(ps.database_id) AS , OBJECT_NAME(ps.OBJECT_ID) AS ,
i.name AS , ps.index_id, ps.index_type_desc, ps.avg_fragmentation_in_percent,
ps.fragment_count, ps.page_count, i.fill_factor, i.has_filter, i.filter_definition
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , N'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps. = i.
AND ps.index_id = i.index_id
WHERE ps.database_id = DB_ID()
AND ps.page_count > 2500
ORDER BY ps.avg_fragmentation_in_percent DESC OPTION (RECOMPILE);
-- Helps determine whether you have framentation in your relational indexes
-- and how effective your index maintenance strategy is
Query #63 Overall Index Usage – Reads
-- Index Read/Write stats (all tables in current DB) ordered by Reads
SELECT OBJECT_NAME(i.) AS , i.name AS , i.index_id,
s.user_seeks, s.user_scans, s.user_lookups,
s.user_seeks + s.user_scans + s.user_lookups AS ,
s.user_updates AS ,
i.type_desc AS , i.fill_factor AS , i.has_filter, i.filter_definition,
s.last_user_scan, s.last_user_lookup, s.last_user_seek
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
ON i. = s.
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.,'IsUserTable') = 1
ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC OPTION (RECOMPILE); -- Order by reads
-- Show which indexes in the current database are most active for Reads
Query #64 Overall Index Usage – Writes
-- Index Read/Write stats (all tables in current DB) ordered by Writes
SELECT OBJECT_NAME(i.) AS , i.name AS , i.index_id,
s.user_updates AS , s.user_seeks + s.user_scans + s.user_lookups AS ,
i.type_desc AS , i.fill_factor AS , i.has_filter, i.filter_definition,
s.last_system_update, s.last_user_update
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
ON i. = s.
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.,'IsUserTable') = 1
ORDER BY s.user_updates DESC OPTION (RECOMPILE); -- Order by writes
-- Show which indexes in the current database are most active for Writes
Query #65 XTP Index Usage
-- Get in-memory OLTP index usage
SELECT OBJECT_NAME(i.) AS , i.index_id, i.name, i.type_desc,
xis.scans_started, xis.scans_retries, xis.rows_touched, xis.rows_returned
FROM sys.dm_db_xtp_index_stats AS xis WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON i. = xis.
AND i.index_id = xis.index_id
ORDER BY OBJECT_NAME(i.) OPTION (RECOMPILE);
-- This gives you some index usage statistics for in-memory OLTP
-- Returns no data if you are not using in-memory OLTP
Query #66 Lock Waits
-- Get lock waits for current database
SELECT o.name AS , i.name AS , ios.index_id, ios.partition_number,
SUM(ios.row_lock_wait_count) AS ,
SUM(ios.row_lock_wait_in_ms) AS ,
SUM(ios.page_lock_wait_count) AS ,
SUM(ios.page_lock_wait_in_ms) AS ,
SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) AS
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
INNER JOIN sys.objects AS o WITH (NOLOCK)
ON ios. = o.
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ios. = i.
AND ios.index_id = i.index_id
WHERE o. > 100
GROUP BY o.name, i.name, ios.index_id, ios.partition_number
HAVING SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) > 0
ORDER BY total_lock_wait_in_ms DESC OPTION (RECOMPILE);
-- This query is helpful for troubleshooting blocking and deadlocking issues
Query #67 UDF Statistics
-- Look at UDF execution statistics
SELECT OBJECT_NAME(object_id) AS , execution_count,
total_elapsed_time/1000 AS , fs.
FROM sys.dm_exec_function_stats AS fs WITH (NOLOCK)
WHERE database_id = DB_ID()
ORDER BY OBJECT_NAME(object_id) OPTION (RECOMPILE);
-- New for SQL Server 2016
-- Helps you investigate UDF performance issues
Query #68 QueryStore Options
-- Get QueryStore Options for this database
SELECT actual_state, actual_state_desc, readonly_reason,
current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options WITH (NOLOCK)
OPTION (RECOMPILE);
-- New for SQL Server 2016
-- Requires that QueryStore is enabled for this database
Query #69 High Aggregate Duration Queries
-- Get highest aggregate duration queries over last hour
WITH AggregatedDurationLastHour
AS
(SELECT q.query_id, SUM(count_executions * avg_duration) AS total_duration,
COUNT (distinct p.plan_id) AS number_of_plans
FROM sys.query_store_query_text AS qt WITH (NOLOCK)
INNER JOIN sys.query_store_query AS q WITH (NOLOCK)
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p WITH (NOLOCK)
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs WITH (NOLOCK)
ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi WITH (NOLOCK)
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(hour, -1, GETUTCDATE())
AND rs.execution_type_desc = N'Regular'
GROUP BY q.query_id),
OrderedDuration AS
(SELECT query_id, total_duration, number_of_plans,
ROW_NUMBER () OVER (ORDER BY total_duration DESC, query_id) AS RN
FROM AggregatedDurationLastHour)
SELECT OBJECT_NAME(q.object_id) AS , qt.query_sql_text,
od.total_duration AS ,
od.number_of_plans AS ,
p.is_forced_plan, p.is_parallel_plan, p.is_trivial_plan,
q.query_parameterization_type_desc, p.,
p.last_compile_start_time, q.last_execution_time,
CONVERT(xml, p.query_plan) AS query_plan_xml
FROM OrderedDuration AS od
INNER JOIN sys.query_store_query AS q WITH (NOLOCK)
ON q.query_id= od.query_id
INNER JOIN sys.query_store_query_text AS qt WITH (NOLOCK)
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p WITH (NOLOCK)
ON q.query_id = p.query_id
WHERE od.RN
页:
[1]