sql - 如何解决 SQL Server 中的性能问题

Question

我有一个相对较大的数据库(每个表有数百万条记录)，其中有许多表和索引都是由以前的开发人员设置的，我正试图找到一种解决一些性能问题的好方法。

我们的应用程序在其整个生命周期中进行了多次数据库调用，我正试图找到一种好方法来诊断一些较慢的查询，或者一些我们缺少索引或在我们写入更多索引的表上的实例我们从中读取。

有哪些快速查询可以让我深入了解我正在寻找的东西？

Answer 1

如果您不熟悉为您跟踪所有这些信息的 SQL Server 系统表，那么查找这些信息可能会非常痛苦。但是，希望通过探索我在下面列出的一些查询(针对最常见的问题)，您会发现对您的特定问题的一些见解。

它的作用:按当前数据库的平均输入/输出使用情况列出排名靠前的语句

SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name],
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
SUBSTRING(qt.[text],qs.statement_start_offset/2, 
               (CASE 
                              WHEN qs.statement_end_offset = -1 
                THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 
                              ELSE qs.statement_end_offset 
                END - qs.statement_start_offset)/2) AS [Query Text]         
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.[dbid] = DB_ID()
ORDER BY [Avg IO] DESC OPTION (RECOMPILE);

使用注意事项:帮助您找到最昂贵的 I/O 语句 SP

它的作用:可能的错误 NC 索引(写入 > 读取)

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

使用注意事项:— 寻找写入次数多而读取次数为零或非常少的索引— 考虑您的全部工作量— 在删除索引之前进一步调查

它的作用:根据 Index Advantage 缺少当前数据库的索引

SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], 
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID() -- Remove this to see for entire instance
ORDER BY index_advantage DESC OPTION (RECOMPILE);

使用注意事项:— 查看上次用户搜索时间、用户搜索次数以帮助确定来源和重要性— SQL Server 过于急于添加包含的列，所以要当心— 不要只是盲目地添加从该查询中显示的索引!!!

它的作用:获取当前数据库中超过特定​​大小的所有索引的碎片信息— 注意:在非常大的数据库上这可能需要一些时间

SELECT DB_NAME(database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name], 
i.name AS [Index Name], ps.index_id, index_type_desc,
avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats( NULL,NULL, NULL, NULL ,'LIMITED') AS ps
INNER JOIN sys.indexes AS i 
ON ps.[object_id] = i.[object_id] 
AND ps.index_id = i.index_id
WHERE database_id = DB_ID()
--AND page_count > 500
ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);

使用注意事项:— 帮助确定您的关系索引中是否存在碎片— 以及您的索引维护策略的有效性

这些应该可以帮助您入门!

我写了一篇关于这个主题的更长的博文 here对于那些感兴趣的人。