0

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.

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.

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

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?

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

enter image description here

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Yasin Bilir
  • 194
  • 3
  • 17

1 Answers1

1

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

  1. Tuning query:
    • You may put NOLOCK only if you do not care about the result.
    • 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:

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);

  • You have to look at the execution plan
  • Look if plan is better if you exclude OPTION (FAST 5)

  • See if you can improve query by indexing.

    1. You can do Altering indexes on one-by-one basis with exclusion of your CUSTTRANSOPEN table. and ALTER its indexes when query has finished.
Slava Murygin
  • 1,951
  • 1
  • 10
  • 10