sql-server-2008 - 在运行大量插入时解决内存不足的选项有哪些？

Question

我正在尝试运行生成的大量插入语句(大约 30 兆字节)以重新同步一个数据库以匹配另一个数据库。我在具有 8 演出内存的 Win 7 机器上运行 SQL Server 2008 R2 64 位。

当我在 SSMS 中运行查询时，我得到“查询执行时出错”并且没有输出消息。经过一番摆弄并尝试在 SSMS 之外运行它之后，我意识到这是一个内存不足错误。事实上，即使在它出错之后，我发现 SQL Server 服务使用了大约 5.5 gigs 的内存!

是否有我可以调整的参数来允许 SQL 运行它？另一种方法是编写一个程序，将查询分成批处理并运行它们。有趣的是，这似乎只发生在包含插入的大型查询中。包含更新的大型查询似乎工作正常。也许这是由于它们写入事务日志的方式。但是，查询作为一系列插入输出输出，并且无法更改。

无论如何，我可以调整 SQL Server 中的设置来解决这个问题吗？我很感激任何人可以提供的任何建议。

编辑:感谢您的回复，蓝脚。当我们第一次看到无消息错误时，我和我的同事有同样的印象——看起来 SSMS 内存不足。但是，经过进一步检查，它看起来可能是 SQL Server。让我解释。我们使用 Red Gate 的 SQL 比较 SDK 和 Visual Studio 来生成差异查询，然后有条件地运行某些语句，根据某些规则使 2 个数据库收敛。于是，在遇到这个问题后，我们使用了包含的 Executor 对象来运行脚本。我们收到以下错误:

System.Data.SqlClient.SqlException occurred
  Message=There is insufficient system memory in resource pool 'internal' to run this query.
  Source=.Net SqlClient Data Provider
  ErrorCode=-2146232060
  Class=17
  LineNumber=79180
  Number=701
  Procedure=""
  Server=QA1
  State=123
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at RedGate.Shared.SQL.ExecutionBlock.BlockExecutor.ExecuteBlock(ExecutionBlock block, String server, String database, Boolean integratedSecurity, String userName, String password)
       at IO.Practiceware.DbSync.Csharp.Program.RunScript(String path, String server, String database)
  InnerException: 

我不知道这有什么用，但我知识渊博的同事说这基本上意味着 SQL Server 本身的内存不足。如果您对此有任何见解，我将不胜感激。

Answer 1

一些想法:

尝试将插入物分成几批。每 1000 行左右插入如下内容:

INSERT ...
INSERT ...
INSERT ...

GO

INSERT ...
INSERT ...
INSERT ...

发出 CHECKPOINT 也可能没有伤害偶尔减少对日志的影响。

与其发送数千个单独的插入语句，不如尝试将它们组合起来。您可以通过多种方式做到这一点，举几个例子:

INSERT dbo.table(col1,col2) SELECT 1,2
UNION ALL SELECT 2,2
UNION ALL SELECT 3,2;

-- or    

INSERT dbo.table(col1,col2)
VALUES(1,2),(2,2),(3,2);

此外，您可以将数据(而不是插入命令)存储在纯文本文件中并使用 BULK INSERT , bcp等，而不是常规插入。