什么是 View 对象上的 LOP_INSERT_ROWS 和 LOP_DELETE_ROWS 操作? [英] What is the LOP_INSERT_ROWS and LOP_DELETE_ROWS operation on a View object?

问题描述

我正在检查 MS SQL 事务日志以调查问题，发现在单个user_transaction"中的 SQL 视图对象上有大量 LOP_INSERT_ROWS 和 LOP_DELETE_ROWS 操作，持续时间超过一分钟.

I'm examining the MS SQL Transaction Log for investigating a problem, and found there are a huge amount of LOP_INSERT_ROWS and LOP_DELETE_ROWS operations on a SQL View object within a single "user_transaction" last for over a minute.

我只是好奇对 View 对象的 LOP_INSERT_ROWS 和 LOP_DELETE_ROWS 操作是什么意思?它们是指创建和删除 View 对象的操作吗?

I just curious what is the meaning of a LOP_INSERT_ROWS and LOP_DELETE_ROWS operation on a View object? Do they mean the action on creating and dropping a View object?

谢谢.

[更新于 2016-05-12]

下面是我上面提到的user_transaction (0000:0f20ab9b).它从 10:00:12 开始，到 10:01:44 结束.它在大约 1.5 分钟内生成了超过 3,000,000 个事务日志操作.本次交易前半部分99%是对PartitionId=72057594040877056的LOP_DELETE_ROWS操作，后半部分99%是对同一个PartitionId进行LOP_INSERT_ROWS操作.

The following is the user_transaction (0000:0f20ab9b) I mentioned above. It starts at 10:00:12 and ends at 10:01:44. It generated over 3,000,000 transaction log operations within ~1.5mins. The 99% of first half of this transaction are the LOP_DELETE_ROWS operation on the PartitionId=72057594040877056, and the 99% of second half of this transaction are the LOP_INSERT_ROWS operation on the same PartitionId.

因此，我通过以下查询检查了属于该 PartitionId=72057594040877056 的对象名称和 id，查询显示它是一个用户视图对象 (id=125243501).

Hence, I checked the object name and id belonged to this PartitionId=72057594040877056 by the following query, and the query show it is a user view object (id=125243501).

有人见过这种症状吗?

[2016-05-25 更新]视图定义如下所示:

CREATE  VIEW [dbo].[get_xxxxxxxxxxxxxxxxxxxxx_vw]
WITH SCHEMABINDING
AS
    SELECT  Apple.rr_id, Apple.r_date, Apple.r_num, Apple.rr_num,
            Apple.h_code, Apple.j_code, Apple.t_code,
            Apple.is_scratch, Apple.result, Apple.is_replaced,
            Apple.draw, Apple.weight, Apple.rating, Apple.gear,
            Orange.s_id, Banana.p_id,
            Orange.l_index, Orange.e_index, Banana.c_key,
            Grape.price, Grape.ss_id, Grape.price_time, Grape.price_trend, Grape.choice_id, 
            Banana.c_id
    FROM    dbo.Apple, dbo.Pear, dbo.Orange, dbo.Grape, dbo.Banana
    WHERE   Apple.r_date = Pear.curr_r_date
    AND     Orange.c_id = Banana.c_id
    AND     Banana.c_id = Grape.c_id
    AND     Orange.rr_id = Apple.rr_id

(抱歉，我不能透露所有的源代码，只是打乱了表名，因为代码不是我写的.)

(Sorry that I can't disclose all the source code but just scrambled the tables name, as the code is not written by me.)

推荐答案

这是一个正在维护的索引视图.这是完全正常的.

It's an indexed view that is being maintained. This is completely normal.

维护的原因是该视图的基表之一已被修改.由于一个基表行可以对应任意数量的视图行，因此写入活动可能会激增.

The reason for the maintenance is that one of the base tables of that view has been modified. Since one base table row can correspond to an arbitrary amount of view rows there can be an explosion of write activity.

您会在对基表执行的 DML 执行计划中找到索引视图维护.

You will find indexed view maintenance in the execution plan of the DML carried out on the base table.

索引视图权衡了 DML 速度、存储空间和缓冲池占用，以提高查询速度.您决定这对您来说是否是一个好的权衡.

Indexed views trade off DML speed, storage space and buffer pool occupation for improved query speed. You decide whether that is a good trade-off for you or not.

屏幕截图显示在基表中修改了一行(第 2 行).这会导致视图中出现大量删除/插入对.这可能是因为基表中的修改值导致视图索引中的行移动到不同的位置.

The screenshot shows one row modified in the base table (line 2). This causes big amounts of delete/insert pairs in the view. This likely is because the modified values in the base table cause rows to move in the indexes on the view to a different place.

这篇关于什么是 View 对象上的 LOP_INSERT_ROWS 和 LOP_DELETE_ROWS 操作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！