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.[object_id]) AS [Object Name], p.index_id, 

 CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],  

 COUNT(*) AS [BufferCount], p.Rows AS [Row Count],

 p.data_compression_desc AS [Compression Type]

 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.[object_id] > 100

 GROUP BY p.[object_id], p.index_id, p.data_compression_desc, p.[Rows]

 ORDER BY [BufferCount] 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 Sizes

-- Get Table names, row counts, and compression status for clustered index or heap

SELECT OBJECT_NAME(object_id) AS [ObjectName], 

SUM(Rows) AS [RowCount], data_compression_desc AS [CompressionType]

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.[object_id]) AS [ObjectName], p.[rows] AS [Table Rows], p.index_id, 

       p.data_compression_desc AS [Index Data Compression],

       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.[object_id] = p.[object_id]

WHERE OBJECT_NAME(t.[object_id]) NOT LIKE N'sys%'

ORDER BY OBJECT_NAME(t.[object_id]), 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 [Object Name], o.type_desc AS [Object Type],

      i.name AS [Index Name], STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], 

      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.[object_id] = i.[object_id]

INNER JOIN sys.stats AS s WITH (NOLOCK)

ON i.[object_id] = s.[object_id] 

AND i.index_id = s.stats_id

INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)

ON o.[object_id] = st.[object_id]

AND i.[index_id] = st.[index_id]

WHERE o.[type] IN ('U', 'V')

AND st.row_count > 0

ORDER BY STATS_DATE(i.[object_id], 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 [Object Name], o.[object_id], o.type_desc, s.name AS [Statistics Name], 

        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 [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name], 

 i.name AS [Index Name], 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.[object_id] = i.[object_id] 

 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.[object_id]) AS [ObjectName], i.name AS [IndexName], i.index_id, 

       s.user_seeks, s.user_scans, s.user_lookups,

       s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads], 

       s.user_updates AS [Writes],  

       i.type_desc AS [Index Type], i.fill_factor AS [Fill Factor], 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.[object_id] = s.[object_id]

AND i.index_id = s.index_id

AND s.database_id = DB_ID()

WHERE OBJECTPROPERTY(i.[object_id],'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.[object_id]) AS [ObjectName], i.name AS [IndexName], i.index_id,

       s.user_updates AS [Writes], s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads], 

       i.type_desc AS [Index Type], i.fill_factor AS [Fill Factor], 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.[object_id] = s.[object_id]

AND i.index_id = s.index_id

AND s.database_id = DB_ID()

WHERE OBJECTPROPERTY(i.[object_id],'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.[object_id]) AS [Object Name], 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.[object_id] = xis.[object_id] 

AND i.index_id = xis.index_id 

ORDER BY OBJECT_NAME(i.[object_id]) 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 [table_name], i.name AS [index_name], ios.index_id, ios.partition_number,

        SUM(ios.row_lock_wait_count) AS [total_row_lock_waits], 

        SUM(ios.row_lock_wait_in_ms) AS [total_row_lock_wait_in_ms],

        SUM(ios.page_lock_wait_count) AS [total_page_lock_waits],

        SUM(ios.page_lock_wait_in_ms) AS [total_page_lock_wait_in_ms],

        SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) AS [total_lock_wait_in_ms]

FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios

INNER JOIN sys.objects AS o WITH (NOLOCK)

ON ios.[object_id] = o.[object_id]

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON ios.[object_id] = i.[object_id] 

AND ios.index_id = i.index_id

WHERE o.[object_id] > 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 [Function Name], execution_count,

   total_elapsed_time/1000 AS [time_milliseconds], fs.[type_desc]

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 [Containing Object], qt.query_sql_text, 

od.total_duration AS [Total Duration (microsecs)], 

od.number_of_plans AS [Plan Count],

p.is_forced_plan, p.is_parallel_plan, p.is_trivial_plan,

q.query_parameterization_type_desc, p.[compatibility_level],

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 <= 50 

ORDER BY total_duration DESC OPTION (RECOMPILE);

-- New for SQL Server 2016

-- Requires that QueryStore is enabled for this database

Query #70 Recent Full Backups

-- Look at recent Full backups for the current database (Query 70) (Recent Full Backups)

SELECT TOP (30) bs.machine_name, bs.server_name, bs.database_name AS [Database Name], bs.recovery_model,

CONVERT (BIGINT, bs.backup_size / 1048576 ) AS [Uncompressed Backup Size (MB)],

CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) AS [Compressed Backup Size (MB)],

CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, bs.backup_size) /

CONVERT (FLOAT, bs.compressed_backup_size))) AS [Compression Ratio], bs.has_backup_checksums, bs.is_copy_only, bs.encryptor_type,--2014 onwords

DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)],

bs.backup_finish_date AS [Backup Finish Date]

FROM msdb.dbo.backupset AS bs WITH (NOLOCK)

WHERE bs.database_name = DB_NAME(DB_ID())

AND bs.[type] = 'D' -- Change to L if you want Log backups

ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);

-- Are your backup sizes and times changing over time?

-- Are you using backup compression?

-- Have you done any backup tuning with striped backups, or changing the parameters of the backup command?

-