如何使用索引作业同时执行大型查询？

I have a large database (size 1.7 TB) and have a maintenance index job to rebuild-reorganize indexes. This job is scheduled at 11:00 pm.

我有一个大型数据库（大小为1.7 TB），并有一个维护索引作业来重建 - 重组索引。这项工作安排在晚上11点。

This morning, i was just checking the queries that running on the server and i noticed that the index job is still running (more than 10 hours) because another t-sql query that has been running on the server more than 22 hours and locked the table that the job was trying to rebuild the indexes of it. It was like an endless progress so i had to kill the blocking session (169) to let the index job keeps running. My question is; how can i avoid locking tables that index job is working on. I know that rebuilding index is locking the table bcs its offline, but should i do some optimizing on the t-sql query which was running more than 22 hours ? Bcs this t-sql query is running oftenly by our ERP application in the day.

今天早上，我只是检查在服务器上运行的查询，我注意到索引作业仍在运行（超过10个小时），因为另一个在服务器上运行的t-sql查询超过22小时并锁定了该作业试图重建其索引的表。这就像一个无休止的进展所以我不得不杀死阻塞会话（169）让索引作业继续运行。我的问题是;我怎样才能避免锁定索引作业正在处理的表。我知道重建索引将表bcs锁定为脱机，但是我应该对运行超过22小时的t-sql查询进行一些优化吗？ Bcs这个t-sql查询经常在我们的ERP应用程序中运行。

The query is;

查询是;

SELECT T1.ACCOUNTNUM,T1.AMOUNTCUR,T1.AMOUNTMST,T1.DUEDATE,T1.RECID,T1.RECVERSION,T1.REFRECID,T1.TRANSDATE,T1.RECVERSION,T2.INVOICE
,T2.TRANSTYPE,T2.TRANSDATE,T2.AMOUNTCUR,T2.ACCOUNTNUM,T2.VOUCHER,T2.COLLECTIONLETTERCODE,T2.SETTLEAMOUNTCUR,T2.CURRENCYCODE,
T2.CUSTBILLINGCLASSIFICATION,T2.RECVERSION,T2.RECID,T3.ACCOUNTNUM,T3.PARTY,T3.CURRENCY,T3.RECID,T3.RECVERSION 
FROM **CUSTTRANSOPEN** T1 
CROSS JOIN CUSTTRANS T2 
CROSS JOIN CUSTTABLE T3

 WHERE (((T1.PARTITION=@P1) AND (T1.DATAAREAID=@P2)) AND (T1.DUEDATE<@P3)) AND (((T2.PARTITION=@P4) AND 
 (T2.DATAAREAID=@P5)) AND (((((((T2.TRANSTYPE<=@P6) OR  (T2.TRANSTYPE=@P7)) OR ((T2.TRANSTYPE=@P8) OR (T2.TRANSTYPE=@P9)))
  OR (((T2.TRANSTYPE=@P10) OR (T2.TRANSTYPE=@P11)) OR (T2.TRANSTYPE=@P12))) AND (T2.AMOUNTCUR>=@P13)) 
 AND (T1.ACCOUNTNUM=T2.ACCOUNTNUM)) AND (T1.REFRECID=T2.RECID))) AND (((T3.PARTITION=@P14) AND (T3.DATAAREAID=@P15))
  AND (T2.ACCOUNTNUM=T3.ACCOUNTNUM)) ORDER BY T1.DUEDATE OPTION(FAST 5)

** The locked table is: CUSTTRANSOPEN

**锁定的桌子是：CUSTTRANSOPEN

I mean, for ex. should i put a WITH (NOLOCK) statement in the query ? How do you perform large queries at the same time with the index job?

我的意思是，对于前。我应该在查询中放置一个WITH（NOLOCK）语句吗？如何使用索引作业同时执行大型查询？

** I have standart edition sql server. So 'online rebuilding' is not possible.

**我有标准版sql server。所以'在线重建'是不可能的。

1 个解决方案

#1

You have two problems:
- Large query, which might be tuned
- Simultaneous running ALTER INDEX

您有两个问题： - 大型查询，可能会调整 - 同时运行ALTER INDEX

1. Tuning query:
   • You may put NOLOCK only if you do not care about the result.
     
   • 只有在不关心结果时才可以使用NOLOCK。
   • Your query does cartesian joins, which supposed to produce multiplication of rows of all three tables. No wonder that it takes 20 hours. It might be not the intention. So, determine what exactly you want. Here is a sample of simplified query. Verify if that produces the same logic:
   • 您的查询执行笛卡尔连接，它应该产生所有三个表的行的乘法。难怪需要20个小时。这可能不是意图。所以，确定你想要什么。以下是简化查询的示例。验证是否产生相同的逻辑：
2. 调整查询：只有在不关心结果的情况下才可以使用NOLOCK。您的查询执行笛卡尔连接，它应该产生所有三个表的行的乘法。难怪需要20个小时。这可能不是意图。所以，确定你想要什么。以下是简化查询的示例。验证是否产生相同的逻辑：

SELECT T1.ACCOUNTNUM, T1.AMOUNTCUR, T1.AMOUNTMST, T1.DUEDATE, T1.RECID , T1.RECVERSION, T1.REFRECID, T1.TRANSDATE, T1.RECVERSION, T2.INVOICE , T2.TRANSTYPE, T2.TRANSDATE, T2.AMOUNTCUR, T2.ACCOUNTNUM, T2.VOUCHER , T2.COLLECTIONLETTERCODE, T2.SETTLEAMOUNTCUR, T2.CURRENCYCODE, T2.CUSTBILLINGCLASSIFICATION, T2.RECVERSION , T2.RECID, T3.ACCOUNTNUM, T3.PARTY, T3.CURRENCY, T3.RECID, T3.RECVERSION FROM **CUSTTRANSOPEN** AS T1 INNER JOIN CUSTTRANS AS T2 ON T1.ACCOUNTNUM=T2.ACCOUNTNUM AND T1.REFRECID=T2.RECID AND T2.PARTITION=@P4 AND T2.DATAAREAID=@P5 AND T2.AMOUNTCUR>=@P13 AND (T2.TRANSTYPE<=@P6 OR T2.TRANSTYPE IN (@P7, @P8, @P9, @P10, @P11, @P12) INNER JOIN CUSTTABLE AS T3 ON T2.ACCOUNTNUM=T3.ACCOUNTNUM AND T3.PARTITION=@P14 AND T3.DATAAREAID=@P15 AND WHERE T1.PARTITION=@P1 AND T1.DATAAREAID=@P2 AND T1.DUEDATE<@P3 AND ORDER BY T1.DUEDATE OPTION (FAST 5);

SELECT T1.ACCOUNTNUM，T1.AMOUNTCUR，T1.AMOUNTMST，T1.DUEDATE，T1.RECID，T1.RECVERSION，T1.REFRECID，T1.TRANSDATE，T1.RECVERSION，T2.INVOICE，T2.TRANSTYPE，T2.TRANSDATE，T2 .AMOUNTCUR，T2.ACCOUNTNUM，T2.VOUCHER，T2.COLLECTIONLETTERCODE，T2.SETTLEAMOUNTCUR，T2.CURRENCYCODE，T2.CUSTBILLINGCLASSIFICATION，T2.RECVERSION，T2.RECID，T3.ACCOUNTNUM，T3.PARTY，T3.CURRENCY，T3.RECID ，T3.RECVERSION来自** CUSTTRANSOPEN **作为T1 INNER JOIN CUSTTRANS作为T2 ON T1.ACCOUNTNUM = T2.ACCOUNTNUM和T1.REFRECID = T2.RECID和T2.PARTITION=@P4 AND T2.DATAAREAID=@P5和T2。 AMOUNTCUR> = @ P13 AND（T2.TRANSTYPE <= @ P6或T2.TRANSTYPE IN（@ P7，@ P8，@ P9，@ P10，@ P11，@ P12）INNER JOIN CUSTTABLE为T3 ON T2.ACCOUNTNUM = T3。 ACCOUNTNUM和T3.PARTITION=@P14和T3.DATAAREAID=@P15以及T1.PARTITION=@P1和T1.DATAAREAID =@P2和T1.DUEDATE <@ P3和订购T1.DUEDATE OPTION（FAST 5）;

• You have to look at the execution plan
  
• 你必须看看执行计划
• Look if plan is better if you exclude OPTION (FAST 5)
• 如果排除OPTION（快速5），看看计划是否更好

• See if you can improve query by indexing.

  看看您是否可以通过索引改进查询。

  2. You can do Altering indexes on one-by-one basis with exclusion of your CUSTTRANSOPEN table. and ALTER its indexes when query has finished.
  3. 您可以逐个更改索引，但不包括CUSTTRANSOPEN表。查询完成后更改其索引。