Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· OpenAIAi2 WildChat-4.8M (AI2)

诊断和解决SQL Server实例的性能瓶颈, 的 sql 全部列给我

  1. User

    诊断和解决SQL Server实例的性能瓶颈, 的 sql 全部列给我

  2. Assistant

    抱歉,您需要给出更具体的问题和场景,才能够提供相关的 SQL语句。通常用于诊断和解决 SQL Server 实例性能瓶颈的 SQL 包括以下内容:

    1. 查看实例配置信息:

    SELECT ServerProperty('MachineName') AS 'Server Name', ServerProperty('Edition') as 'Edition', ServerProperty('ProductVersion') as 'Product Version', ServerProperty('Collation') as 'Collation', ServerProperty('InstanceName') AS 'Instance Name', ServerProperty('IsClustered') as 'Is Clustered', ServerProperty('MaxDop') as 'Max Degree of Parallelism', ServerProperty('ProcessID') as 'Process ID'

    1. 查询系统状态信息:

    SELECT cpu_percent = CONVERT(DECIMAL(10,2), ms_ticks/(1000*1.00) / SUM(cpu_ticks)*1.00), memory_usage = CONVERT(DECIMAL(10,2), (memory_usage_in_bytes * 1.00 / total_physical_memory_in_bytes) * 100.0), read_io_mb = CONVERT(DECIMAL(10,2), num_of_bytes_read/1024.0/1024.0), write_io_mb = CONVERT(DECIMAL(10,2), num_of_bytes_written/1024.0/1024.0), date_time = GETDATE()

    FROM sys.dm_os_performance_counters WITH (NOLOCK)

    WHERE object_name = 'SQLServer:Resource Pool Stats' AND counter_name = 'CPU Usage %' AND instance_name = 'default'

    1. 查询等待统计信息:

    SELECT wait_type, wait_time_ms, 100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, signal_wait_time_ms, 100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER() AS signal_pct, waiting_tasks_count, wait_category_desc

    FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'FT_IFTSHC_MUTEX', 'SPOOL', 'TRANSPORT_CONNECTIVITY', 'wait_for_results_to_send', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'HADR_WORK_QUEUE', 'XACT_OWN_TRANSACTION') ORDER BY pct DESC

    1. 查询缓存命中率:

    SELECT (1.0 * (total_buffer_hits - (database_buffer_hits + filegroup_buffer_hits))) / (1.0 * total_buffer_hits) AS [BufferCache Miss %], dateadd(ss,-1*DATEDIFF(ss,GETDATE(),GETUTCDATE()),GETDATE()) AS [Date Time] FROM ( SELECT SUM(cntr_value) AS total_buffer_hits FROM sys.dm_os_performance_counters WITH (NOLOCK) WHERE object_name LIKE '%Buffer Manager%' AND counter_name = 'Buffer cache hit ratio base' ) BASELINE,
    ( SELECT SUM(cntr_value) AS database_buffer_hits FROM sys.dm_os_performance_counters WITH (NOLOCK) WHERE object_name LIKE '%Buffer Manager%' AND counter_name = 'Database pages' ) DATABASE_PAGES, ( SELECT SUM(cntr_value) AS filegroup_buffer_hits FROM sys.dm_os_performance_counters WITH (NOLOCK) WHERE object_name LIKE '%Buffer Manager%' AND counter_name ='Filegroup pages' ) FILEGROUP_PAGES;

    1. 查询缓存使用信息:

    SELECT DatabaseName = DB_NAME(database_id), CachesizeMB = COUNT() * 8 / 1024, Cachetype = CASE([type]) WHEN 1 THEN 'Data pages' WHEN 2 THEN 'Index pages' WHEN 3 THEN 'Text/Image pages' ELSE 'Other pages' END FROM sys.dm_os_buffer_descriptors WITH (NOLOCK) GROUP BY DB_NAME(database_id), [type] ORDER BY [type], COUNT();

    1. 查询锁统计信息:

    SELECT CONVERT(VARCHAR(32), fs.database_id) AS database_id, DB_NAME(fs.database_id) AS database_name, fs.[file_id], CONVERT(VARCHAR(32), fs.page_id) AS page_id, fs.page_type, fs.page_alloc_unit_type_desc, fs.[is_allocated], fs.[is_iam_page], fs.[has_ghost_records], fs.[is_snapshot_page], fs.[is_mixed_extent_page], fs.[has_clustered_index], fs.[is_key_rollback], fs.[is_compressed], fs.lock_information FROM sys.dm_os_buffer_descriptors AS bd INNER JOIN sys.dm_os_waiting_tasks AS wt ON bd.page_address = wt.resource_address INNER JOIN sys.dm_os_memory_clerks AS mc ON bd.page_allocations_count > 0 AND bd.page_free_count = 0 AND (bd.database_id<>2) INNER JOIN sys.dm_db_file_space_usage AS fs ON (fs.database_id=bd.database_id AND fs.[file_id]=bd.[file_id] AND fs.page_id=bd.page_id) ORDER BY wt.blocking_session_id, fs.[file_id];

    1. 查询活动会话信息:

    SELECT session_id, login_time, last_request_start_time, last_request_end_time, status, open_transaction_count, cpu_time, total_elapsed_time, program_name, host_name, nt_username, login_name, text, (query_plan.value('(//StmtSimple/QueryPlan/@NoExecutions)[1]', 'float')) AS [Execution Count], total_rows, last_row_count, query_plan

    FROM sys.dm_exec_requests WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS ST CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS QP WHERE session_id > 50;

    这些 SQL 语句可以帮助您诊断和解决 SQL Server 实例的性能瓶颈问题,但建议在使用之前仔细阅读文档,确保理解其功能和适用范围。

  3. User

    还有更多吗

  4. Assistant

    是的,以下是其他一些用于诊断和解决 SQL Server 实例性能瓶颈问题的 SQL 语句:

    1. 查询活动死锁信息:

    SELECT er.session_id, er.blocking_session_id, er.status, er.cpu_time, er.total_elapsed_time, spid1.ecid as 'spid1_ecid', spid1.status as 'spid1_status', spid1.blocked as 'spid1_blocked', spid1.lastwaittype as 'spid1_lastwaittype', spid1.waitresource as 'spid1_waitresource', spid2.ecid as 'spid2_ecid', spid2.status as 'spid2_status', spid2.blocked as 'spid2_blocked', spid2.lastwaittype as 'spid2_lastwaittype', spid2.waitresource as 'spid2_waitresource', DB_NAME(er.database_id) as 'er_database_name', qp.query_plan, er.sql_text

    FROM sys.dm_exec_requests er INNER JOIN sys.sysprocesses spid1 ON er.session_id = spid1.spid INNER JOIN sys.sysprocesses spid2 ON er.blocking_session_id = spid2.spid CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) AS qp

    WHERE er.session_id IN (SELECT blocked FROM sys.sysprocesses)

    1. 查询慢 SQL 语句:

    SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END

    • qs.statement_start_offset)/2)+1) AS statement_text, qs.execution_count, qs.total_logical_reads, qs.total_logical_writes, qs.total_worker_time, total_elapsed_time = qs.total_elapsed_time / 1000000.0, average_elapsed_time = (qs.total_elapsed_time / qs.execution_count) / 1000000.0, qp.query_plan

    FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp ORDER BY qs.total_elapsed_time DESC;

    1. 查询缓存效率和策略:

    SELECT [db_id] = DB_ID(st.dbid), [object_name] = OBJECT_NAME(st.objectid, st.dbid), [io_stall_read_ms], [io_stall_write_ms], [num_of_reads], [num_of_writes], [io_stall], [size_on_disk_bytes], [total_buffer_usage_page_count], [arrival_rate_pages_per_sec], [eviction_rate_pages_per_sec], [cache_hit_ratio], [page_life_expectancy], [is_ansi_padded], [is_compressed], [is_replicated], [is_snapshot], [is_read_committed_snapshot], [is_automated_maintenance_policy_on], [is_mixed_page_allocation_on], [is_auto_create_stats_on], [is_auto_update_stats_on], [is_fulltext_enabled], [is_query_store_on], [query_store_capture_mode_desc], [query_store_disk_size_mb], [occur_time]

    FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) st CROSS JOIN (SELECT occur_time = GETDATE()) occur_time ORDER BY [cache_hit_ratio] DESC;

    这些 SQL 语句可以帮助您更深入地分析和诊断 SQL Server 实例的性能问题,但仍需要根据具体情况进行调整和筛选。

  5. User

    还有更多吗

  6. Assistant

    是的,以下是其他一些用于诊断和解决 SQL Server 实例性能瓶颈问题的 SQL 语句:

    1. 查询缓存表的使用:

    SELECT SCHEMA_NAME(st.schema_id) AS [SchemaName], OBJ.name AS [TableName], ISNULL(sum(ps.used_page_count) * 8,0) AS [Used(MB)], ISNULL(sum(ps.reserved_page_count) * 8,0) AS [Reserved(MB)], ISNULL((sum(ps.used_page_count) * 8) / NULLIF(sum(ps.reserved_page_count) * 8,0),0) AS [PercentUsed] FROM sys.dm_db_partition_stats ps INNER JOIN sys.tables OBJ ON ps.[object_id] = OBJ.[object_id] INNER JOIN sys.schemas st on OBJ.[schema_id] = st.[schema_id] WHERE index_id <= 1 GROUP BY st.schema_id, OBJ.name ORDER BY [PercentUsed] DESC;

    1. 查询分区表的使用:

    SELECT s.name, t.name, i.name, i.index_type_desc AS 'Index Type', i.is_padded, CAST(SUM(spa.used_pages)*8 AS VARCHAR(20)) AS 'Index Size (KB)', CAST(SUM(spa.reserved_pages)*8 AS VARCHAR(20)) AS 'Reserved Space (KB)', CAST(CEILING(SUM(a.total_pages) * 8 / 1024.0) AS VARCHAR(20)) AS 'Table Size (MB)', i.fill_factor, CASE WHEN i.allow_row_locks = 1 THEN 'Yes' ELSE 'No' END AS 'Row Locks', CASE WHEN i.allow_page_locks = 1 THEN 'Yes' ELSE 'No' END AS 'Page Locks'

    FROM sys.indexes i INNER JOIN sys.tables t ON i.[object_id] = t.[object_id] INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id] INNER JOIN sys.dm_db_partition_stats AS ps ON t.[object_id] = ps.[object_id] INNER JOIN sys.allocation_units AS a ON ps.partition_id = a.container_id INNER JOIN sys.dm_db_partition_stats AS spa ON spa.index_id = i.index_id AND spa.[object_id] = i.[object_id]

    WHERE i.index_id <= 1 GROUP BY s.name, t.name, i.name, i.index_type_desc, i.is_padded, i.fill_factor, i.allow_row_locks, i.allow_page_locks;

    1. 查询锁和事务信息:

    SELECT request_session_id, resource_type, resource_typedesc, resource_description, resource_database_id, resource_associated_entity_id, request_mode, request_status, request_owner_type, request_owner_guid, request_owner_id, request_owner_arc_id, request_owner_dbid, wait_duration_ms, wait_time_microseconds / 1000 as wait_time_ms, blocking_session_id, wait_type, wait_time_ms, last_wait_type, grant_time, convert(varchar(50), dateadd(second,(wait_duration_ms)/1000,0), 114) as wait_duration, convert(varchar(50), dateadd(second,(wait_time_microseconds / 1000)/1000.0,0), 114) as wait_time, convert(varchar(50), dateadd(second,(wait_time_ms)/1000.0,0), 114) as [wait_time (ms)], SUBSTRING(text, statement_start_offset/2 + 1, ((CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END) - statement_start_offset)/2) AS request_last_command,

    CASE WHEN EXISTS (SELECT 1 FROM sys.dm_tran_version_store WHERE tx_own_guid = rt.transaction_id) THEN 'YES' ELSE 'NO' END AS snapshot_transaction

    FROM sys.dm_tran_locks l LEFT JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id LEFT JOIN sys.dm_tran_request_transactions rt ON l.request_owner_id = rt.request_owner_id LEFT JOIN sys.dm_os_waiting_tasks wt ON l.request_session_id = wt.session_id CROSS APPLY sys.dm_exec_sql_text(wt.sql_handle) txt

  7. User

    还有更多吗

  8. Assistant

    是的,以下是其他一些用于诊断和解决 SQL Server 实例性能瓶颈问题的 SQL 语句:

    1. 查询磁盘使用情况:

    SELECT DISTINCT UPPER(LEFT(vs.volume_mount_point, 3)) AS Drive, vs.logical_volume_name, vs.file_system_type, vs.drive_type_desc, vs.total_bytes/1048576 AS [Total(MB)], vs.available_bytes/1048576 AS [Free(MB)], CONVERT(DECIMAL(18,2), ((vs.total_bytes-vs.available_bytes) * 100.00 / vs.total_bytes)) AS [Used(%)], CONVERT(DECIMAL(18,2), (vs.available_bytes * 100.00 / vs.total_bytes)) AS [Free(%)] FROM sys.master_files mf CROSS apply sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs ORDER BY [Used(%)] DESC;

    1. 查询备份压缩率:

    SELECT database_name, backup_start_date, compression_ratio, compressed_backup_size/1024/1024 AS 'Compressed Size(MB)', backup_size/1024/1024 AS 'Actual Size(MB)', encrypted, has_backup_checksums, is_copy_only

    FROM msdb..backupset ORDER BY backup_start_date DESC;

    1. 查询服务运行状况:

    SELECT [Service name], [Start type], [Status], [Startup], [Log on as], [Description]

    FROM sys.dm_server_services ORDER BY [Service name];

    这些 SQL 语句可以帮助您了解 SQL Server 实例在各个方面的运行情况,并帮助您更好地诊断和解决性能问题。

  9. User

    还有更多吗

  10. Assistant

    当然,这里还有一些其他的 SQL 查询用于诊断和解决 SQL Server 实例的性能瓶颈:

    1. 查询等待任务的信息:

    SELECT session_id,
    wait_duration_ms,
    wait_type,
    blocking_session_id,
    resource_description,
    REQUEST_TIME, sysprocesses.login_time, sysprocesses.[host_name], sysprocesses.[program_name], sysprocesses.nt_domain, sysprocesses.nt_user_name

    FROM sys.dm_os_waiting_tasks INNER JOIN sys.sysprocesses ON sys.dm_os_waiting_tasks.session_id = sys.sysprocesses.spid;

    1. 查询正在进行的查询的信息:

    SELECT p.spid,
    p.status,
    p.login_time,
    p.hostname,
    p.program_name, p.cmd, q.text FROM sys.sysprocesses p CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS q (WHERE p.status = 'runnable');

    1. 查询响应时间较长的查询:

    SELECT TOP 10 total_worker_time/1000 AS [Total Worker Time (ms)], total_elapsed_time/1000 AS [Total Elapsed Time (ms)], total_logical_reads, total_logical_writes, execution_count, plan_generation_num, last_execution_time, min_execution_time, max_execution_time, OBJECT_NAME(qt.objectid, qt.dbid) as [ObjectName], SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt WHERE qs.total_elapsed_time/1000 >= 60 -- in seconds ORDER BY qs.total_worker_time/1000 DESC;

    1. 查询索引使用情况:

    SELECT DB_NAME() AS [Database Name], OBJECT_SCHEMA_NAME(i.[object_id], DB_ID()) AS [Schema Name], OBJECT_NAME(i