SQL Server 2008 R2 上的插入和查询性能

Question

我们拥有一个SQL Server，其中包含相当庞大的表格(但不是超级巨大) - 也许每个表格大约有3000-4000万条记录。我们需要：

a）每秒向这些表之一(Table_A)插入约1000条记录（在特定时间内 - 但我们无法批处理插入），

b）同时从Table_B中选择*，其中id = _SOME_GUID_ID。

我已经注意到，在负载下，(a)当前花费500-600毫秒(每次插入，因为我们不能批量插入)，而(b)需要大约400毫秒（有索引）。

这远远达不到我们所期望的性能水平。有没有办法通过优化表/操作来提高它们的性能呢？

目前服务器有16GB的RAM，总数据库大小约为40GB。我们预计在未来几个月内这些表会变成目前大小的十倍。Sql Server是否可以进行任何分片？使用MySQL Percona版本能否看到任何性能改进？

希望这有意义。我想补充说明，我们正在使用NHibernate，但上述性能是花费的毫秒数，不包括应用程序或NH开销。

谢谢， Yannis

- Yannis

Answer 1

几件事情需要考虑。主键是GUID还是整数，如果是GUID且聚集在该字段上，则SQL Server将不得不在许多不同的位置插入数据，而不是按顺序进行插入。你的数据库大小如何？日志文件大小如何？它们是否一直自动增长？请参阅调整数据库文件大小。

运行Profiler并查看nhibernate创建的SQL类型。每秒1000次插入已经不那么令人印象深刻了，瓶颈很可能也是硬件问题，请确保其正确大小和配置。tempdb，日志和数据文件是否在同一驱动器上？如果是这样，请将它们移动到单独的驱动器中。

另一个选择是重新编写执行这些插入的代码段，并批量插入而不是逐行插入。

下面是我在SSMS中运行的示例：它在166毫秒内完成4999个插入，40毫秒内完成1000个插入。

CREATE TABLE Sometest(id INT PRIMARY KEY, 
SomeCol VARCHAR(200), SomeDate DATETIME,SomeCol2 VARCHAR(200), 
SomeDate2 DATETIME,SomeCol3 VARCHAR(200), 
SomeDate3 DATETIME,SomeCol4 VARCHAR(200), SomeDate4 DATETIME)
GO




DECLARE @start DATETIME = GETDATE()
SET NOCOUNT ON
DECLARE @id INT =1
WHILE @id < 1000
BEGIN
    INSERT Sometest
    SELECT @id ,'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla111111',GETDATE(),
    'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla2222',GETDATE(),
    'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla3333',GETDATE(),
    'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla4444',GETDATE()
SET @id+=1
END




SELECT DATEDIFF(ms,@start,GETDATE())
SELECT COUNT(*) FROM Sometest

如果您将整个操作包装在一个事务中，速度会更快：50000个插入操作只需要800毫秒，5000个插入操作只需103毫秒，1000个插入操作只需23毫秒。

TRUNCATE TABLE Sometest
DECLARE @start DATETIME = GETDATE()
SET NOCOUNT ON
BEGIN tran
DECLARE @id INT =1
WHILE @id < 50000
BEGIN
    INSERT Sometest
    SELECT @id ,'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla111111',GETDATE(),
    'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla2222',GETDATE(),
    'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla3333',GETDATE(),
    'BlaBlaBlaBlaBlaBlaBlaBlaBlaBla4444',GETDATE()
SET @id+=1
END
commit
SELECT DATEDIFF(ms,@start,GETDATE())
SELECT COUNT(*) FROM Sometest

在您的系统上运行类似于这样的东西，使用与您的表格相似的表格并调查问题所在，也许您需要预分配文件，分离文件，碎片整理表格等等。

- SQLMenace

---

TABLE_A有一个自增的int ID，而TABLE_B有一个guid id。在这样（相当重度索引）的表中，插入需要多长时间，考虑到我们的硬件呢？ - Yannis

从SSMS运行插入并进行测试，如果没有测试，您如何知道问题在哪里...可能是网络，驱动器。您的物理布局是什么样的，SQL Server可以看到多少RAM等等。您的性能测试计划是什么样的？ - SQLMenace

Answer 2

您的Guid可能是顺序Guid或随机Guid，您需要知道哪一个。您可以暂时禁用非聚集索引并在插入结束时重建它们，前提是您不介意该表在该期间（可能）变慢的选择。您有多少核心和临时数据库？尝试每个核心使用1个tempdb（最多8个核心），但一定要设置正确的跟踪标志以平衡tempdb使用情况。并且如下所述，请分批插入。

- Ian P