SQL Server查询优化：索引与性能监控

“Sql Server查询优化语句.md”主要涵盖了SQL Server数据库的一些关键优化技巧，包括查询索引大小或使用率、查找运行慢的SQL语句、查询触发器、重新生成索引、使用DBCC命令以及查询堵塞的处理。 ## 1. 查询索引大小或使用率 索引是数据库性能优化的关键因素。以下SQL语句可以用来获取表中每个索引的大小（以KB为单位）： ```sql SELECT OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName, OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName, 8 * SUM(au.used_pages) AS IndexSizeInKB FROM sys.indexes AS i JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id JOIN sys.allocation_units AS au ON au.container_id = p.partition_id GROUP BY i.OBJECT_ID, i.index_id, i.name ORDER BY OBJECT_NAME(i.OBJECT_ID), i.index_id ``` 通过这个查询，你可以了解数据库中各个索引的占用空间，从而判断是否需要进行索引维护或优化。 ## 2. 查询运行慢的SQL语句 找出执行效率低下的SQL语句对于性能调优至关重要。下面的查询可以帮助你找到执行次数多且资源消耗大的语句： ```sql SELECT TOP 100 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, qp.query_plan, qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time, qs.last_worker_time, qs.total_elapsed_time, qs.last_elapsed_time, qs.last_execution_time 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 ``` 这个查询返回了执行计划、执行次数、逻辑读写等信息，帮助你识别并优化性能瓶颈。 ## 3. 查询触发器 触发器是数据库中的一种特殊对象，用于在特定操作（如INSERT、UPDATE、DELETE）发生时自动执行。查询触发器的命令如下： ```sql SELECT OBJECT_NAME(object_id) AS TriggerName, parent_object_id AS TableName, is_disabled, definition FROM sys.triggers WHERE parent_object_id IN (SELECT object_id FROM sys.tables) ORDER BY TableName, TriggerName ``` 这个查询可以列出所有与表相关的触发器及其状态，便于检查和管理。 ## 4. 重新生成索引 索引的碎片可能导致查询性能下降。重新生成索引可以消除碎片，提高查询速度： ```sql ALTER INDEX ALL ON [TableName] REBUILD; ``` 将`[TableName]`替换为你想要重建索引的表名。如果需要在单个索引上操作，可以指定索引名称： ```sql ALTER INDEX [IndexName] ON [TableName] REBUILD; ``` ## 5. 使用DBCC命令 DBCC（Database Consistency Checker）命令用于检查数据库一致性。例如，DBCC CHECKDB 可以检查整个数据库的完整性： ```sql DBCC CHECKDB ('DatabaseName'); ``` 将`'DatabaseName'`替换为你的数据库名。这有助于发现潜在的问题并修复。 ## 6. 查询堵塞 数据库中的查询堵塞通常发生在一个事务阻塞其他事务执行时。以下查询可以帮助你识别当前的堵塞情况： ```sql SELECT spid, status, loginame, hostname, db_name(dbid) AS DatabaseName, command, cpu_time, total_elapsed_time, program_name, OBJECT_NAME(object_id) AS LockedObjectName FROM sys.dm_exec_requests r LEFT JOIN sys.dm_os_waiting_tasks w ON r.session_id = w.session_id WHERE r.session_id != @@SPID ORDER BY total_elapsed_time DESC ``` 这个查询会显示正在运行的请求，特别是那些导致阻塞的请求，以便进行问题排查。 SQL Server查询优化涉及到多个方面，包括监控索引使用、找出慢查询、管理触发器、维护索引、使用DBCC命令以及解决查询堵塞问题。这些技巧能帮助你提升数据库性能，确保系统稳定高效运行。