SQL数据库性能优化

Question

我们有一个写量大的SQL数据库( Server 2008)，插入速度在一段时间后下降。我读过类似的问题：Sql插入速度加快，如果不是CPU或IO，什么是减慢插入的速度？和加速插入。

此外，我还读过这个职位关于如何分析Server性能的文章，这有助于我了解如何查找瓶颈(例如，通过查询sys.dm_exec_requests和sys.dm_os_wait_stats)，但在解释查询结果和解决问题方面仍然有困难。

首先，我从查询sys.dm_exec_requests开始，它只返回状态“运行”的一个会话id (选择查询)(也就是说，我没有找到挂起的会话)。那么，如果没有任何东西阻止我的插入，为什么它会变慢呢？

接下来，我使用sys.dm_os_wait_stats检查所有等待类型的统计信息。结果表明，LATCH_EX、CXPACKET和PAGEIOLATCH_SH的wait_time含量最高(分别为694379 ms、310364 ms和308335 ms )。

然后，我使用sys.dm_os_latch_stats查找最流行的锁存类型，在我的例子中是ACCESS_METHODS_DATASET_PARENT。

• 首先，我不知道如何在查询session_id的结果中找到与每个sys.dm_exec_requests相关的查询。
• 第二，哪些等待时间(在sys.dm_os_wait_stats中)应该被认为是高的？如果上面提到的数字(sys.dm_os_wait_stats的结果)很高，我如何减少它们呢？
• 据我所知，ACCESS_METHODS_DATASET_PARENT与并行性有关，而我找到的解决方案之一则降低了并行度。是那么回事吗？
• 在MySQL中，可以在安装后立即执行一些调优设置 (例如，增加innodb缓冲池大小)，Server中是否有类似的内容？

更多信息：

• 使用sys.dm_db_index_usage_stats，读写次数分别为80336和70672。
• 我们最忙的表之一是trans_all (显示所有支付事务)，没有触发器，没有约束(对于所有表都是这样)，但是它有一个聚集索引，即，PK_trans_all由以下列组成: gs_id(smallint)、pt_id(tinyint)、fueling_time(datetime)、purchase_type(tinyint)。
• 数据库大小为2.6GB，trans_all表大小为155MB。

UPDATE_1

关于ACCESS_METHODS_DATASET_PARENT，我尝试了我找到的解决方案(即改变并行度)。我将其改为1，以确保查询永远不会发生并行性)，但它没有解决这个问题:是否应该再次更改它？我应该使用的默认值是什么？

UPDATE_2

我刚刚检查了我的数据库的事务日志文件大小，它是2.4GB，使用的日志空间%是98%。难道这就是我放慢插入速度的原因吗？我应该增加日志文件的大小吗？

此外，我还使用sys.database_files检查文件大小和数据库的空空间数量。结果表明，文件大小为195 MB，空空间为0.187 MB。

Answer 1

正如您所描述的，有许多因素可能导致插入速度减慢。

我在您的信息中看不到的是插入的数据库/表大小。随着表的增长，由于表的大小、碎片和将相同的数据写入索引，插入将花费的时间越长。

您正在运行的DM查询为您提供了一些良好的信息。我还建议使用布伦特·奥扎尔的工具包。链接

我建议你使用SP_BlitzIndex和SP_BlitzCache。这将为您提供另一个视图，以了解数据库中实际发生的事情以及您正在运行的insert查询当时正在执行的操作。

我还将检查上一次运行DBCC的时间，以及上次重新构建索引和统计数据的时间。

感谢您的更新，请参阅以下内容：

更多信息：

读/写的次数不多，所以我不太关心。

你知道上次重建trans_all索引是什么时候吗？如果您只有一个覆盖索引和多个插入，那么每次向它插入数据时，如果它已经过时，它将花费更长的时间。下面是一个查询，用于检查数据库表上的碎片：

Use <Database>
GO

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 30 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

更新1：

MAX DOP设置可能会影响您的查询运行时，但如果不查看您正在使用的查询，然后查看估计的计划结果以查看SQL server正在使用的内容，则无法确定它是否正在这样做。有两个统计信息可以用来了解在调整MAX DOP时查询是如何受到影响的。

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

这里有一些信息，肯德拉利特尔在布伦特奥扎尔使用这些给你提供指导。

tsql测量-性能-改进

和

Q-can-high-maxdop-make-a-较慢

更新2：

我会建议增加您的日志大小，如果它是98%满。要么添加另一个反式日志，要么增加它。您还可以查看TEMP_DB，看看它正在做什么，因为SQL将其用作许多事情的倾倒地。

除非你没有成长的空间，否则空旷空间并不是真正的问题。它只是告诉你还有多少空间，直到下一辆汽车增长。

Answer 2

如果您的数据库正在发送日志，那么可能发生的情况是日志中充满了事务，然后等到下一次日志备份之后才清除它们，并为新事务的继续处理腾出空间。尝试缩小日志传送期间之间的差距，或增加日志文件的大小。还有其他事情可以做，比如检查索引使用状态和删除未使用的索引，它们的维护将在日志中产生额外的需求。