在后台MS SQL中运行大型查询 [英] Running large queries in the background MS SQL

问题描述

我正在使用MS SQL Server 2008我有一个经常使用的表(数据总是在变化并插入其中)现在它包含约70密尔行，我正在尝试使用存储过程在表上运行一个简单查询，该过程应该花几天时间，

我需要表保持可用性，现在我执行了存储过程，并在一段时间后尝试在表上执行的每个简单的按身份选择查询都没有响应/运行太多时间以致破坏了表

我该怎么办?这是我的存储过程的样子:

  SET NOCOUNT ON;更新SOMETABLE放[some_col] = dbo.ufn_SomeFunction(CONVERT(NVARCHAR(500)，another_column))在哪里[some_col] = 243 

即使我在where子句(使用"and"逻辑..)上对此进行尝试:

  ID_COL>57000000并且ID_COL<60000000和 

它仍然不起作用

顺便说一句，SomeFunction做一些简单的数学运算，并在另一个包含约30万项但从未更改的表中查找行

解决方案

从我的角度来看，您的服务器存在严重的性能问题.即使我们假设查询中没有记录

 使用(nolock)选择some_col，其中id_col在57000000和57001000之间 

位于内存中，从磁盘顺序读取几页不需要花费21秒的时间(如果id_col上的聚簇索引是自动识别符，并且您没有做像添加a这样的愚蠢操作，则不应将它们分片索引定义的"desc").

但是，如果您不能/不会解决该问题，我的建议是一次以小包形式进行更新，例如一次100-1000条记录(取决于查找函数消耗的时间).一次更新/交易应不超过30秒.

您会看到，每次更新都会保留对其修改的所有记录的排他锁，直到事务完成为止.如果您不使用显式事务，则每个语句都在单个自动事务上下文中执行，因此在完成更新语句后将释放锁.

但是您仍然可以以这种方式陷入僵局，具体取决于其他进程的工作.如果它们一次也修改了一个以上的记录，或者即使它们在多行上收集并保持了读取锁，则可能会出现死锁.

为避免死锁，您的update语句需要锁定将立即修改的所有记录.这样做的方法是将单个update语句(只有几行受id_col限制)放在可序列化的事务中，例如

  IF @@ TRANCOUNT>0-错误:您已经处于交易环境中开启NOCOUNT集交易隔离级别可序列化-在此处插入Loop，以在id范围内工作"x"开始交易可以更新SET [some_col] = dbo.ufn_SomeFunction(CONVERT(NVARCHAR(500)，another_column))在[some_col] = 243 AND id_col x和x + 500之间的情况下-或将更新保持在较小时间范围内的任何方法犯罪-下一个循环-在运行循环的同时获取所有新记录.如果这些太多，则可能还需要分页:开始交易可以更新SET [some_col] = dbo.ufn_SomeFunction(CONVERT(NVARCHAR(500)，another_column))在[some_col] = 243并且id_col> = x的情况下犯罪 

对于每个更新，这将对给定记录(但仅记录，因为它们通过聚集索引键限制更新)将获得更新/独占键范围锁.它将等待同一条记录上的任何其他更新完成，然后获得它的锁(导致对所有其他事务进行阻塞，但仍然仅针对给定记录)，然后更新记录并释放该锁.

最后一个额外的语句很重要，因为它将把键范围锁定为无穷大"，从而防止在更新语句运行时在范围末尾均匀插入.

I am using MS SQL Server 2008 i have a table which is constantly in use (data is always changing and inserted to it) it contains now ~70 Mill rows, I am trying to run a simple query over the table with a stored procedure that should properly take a few days,

I need the table to keep being usable, now I executed the stored procedure and after a while every simple select by identity query that I try to execute on the table is not responding/running too much time that I break it

what should I do? here is how my stored procedure looks like:

 SET NOCOUNT ON;
update SOMETABLE
set
[some_col] = dbo.ufn_SomeFunction(CONVERT(NVARCHAR(500), another_column))
WHERE 
[some_col] = 243

even if i try it with this on the where clause (with an 'and' logic..) :

ID_COL > 57000000 and ID_COL < 60000000 and

it still doesn't work

BTW- SomeFunction does some simple mathematics actions and looks up rows in another table that contains about 300k items, but is never changed

解决方案

From my perspective your server has a serious performance problem. Even if we assume that none of the records in the query

select some_col with (nolock) where id_col between 57000000 and 57001000

was in memory, it shouldn't take 21 seconds to read the few pages sequentially from disk (your clustered index on the id_col should not be fragmented if it's an auto-identity and you didn't do something stupid like adding a "desc" to the index definition).

But if you can't/won't fix that, my advice would be to make the update in small packages like 100-1000 records at a time (depending on how much time the lookup function consumes). One update/transaction should take no more than 30 seconds.

You see each update keeps an exclusive lock on all the records it modified until the transaction is complete. If you don't use an explicit transaction, each statement is executed in a single, automatic transaction context, so the locks get released when the update statement is done.

But you can still run into deadlocks that way, depending on what the other processes do. If they modify more than one record at a time, too, or even if they gather and hold read locks on several rows, you can get deadlocks.

To avoid the deadlocks, your update statement needs to take a lock on all the records it will modify at once. The way to do this is to place the single update statement (with only the few rows limited by the id_col) in a serializable transaction like

IF @@TRANCOUNT > 0
  -- Error: You are in a transaction context already

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

-- Insert Loop here to work "x" through the id range
  BEGIN TRANSACTION
    UPDATE SOMETABLE
      SET [some_col] = dbo.ufn_SomeFunction(CONVERT(NVARCHAR(500), another_column))
      WHERE [some_col] = 243 AND id_col BETWEEN x AND x+500 -- or whatever keeps the update in the small timerange
  COMMIT
-- Next loop

-- Get all new records while you where running the loop. If these are too many you may have to paginate this also:
BEGIN TRANSACTION
  UPDATE SOMETABLE
    SET [some_col] = dbo.ufn_SomeFunction(CONVERT(NVARCHAR(500), another_column))
    WHERE [some_col] = 243 AND id_col >= x
COMMIT

For each update this will take an update/exclusive key-range lock on the given records (but only them, because you limit the update through the clustered index key). It will wait for any other updates on the same records to finish, then get it's lock (causing blocking for all other transactions, but still only for the given records), then update the records and release the lock.

The last extra statement is important, because it will take a key range lock up to "infinity" and thus prevent even inserts on the end of the range while the update statement runs.

这篇关于在后台MS SQL中运行大型查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！