Post reply

Select Query is blocking all other SQL queries on the table .

  • December 22, 2019 at 10:27 pm

    #3709862

    Hi All,

    I have a batch program which first executes the below select query and pulls all the records (22700 records)and based on that, it process each record one by one in a loop and executes the below update statement. This particular table is also used for inserting record  by another application. For last few days , we have started facing issue in this batch. The select statement blocks all other statements (update , insert) from getting executed and also the select statement is not getting completed. We have to contact DB admin team and kill the blocking session using SPID to resolve the issue. Please throw some light on my below questions.

    1. From my DB admin, I understood that select query is blocking all other queries from getting executed, but why the select query is not getting completed and runs forever until someone kills the SPID. This particular program was working in production in the same way for four years (takes five minutes to complete till last week) without issues. Even if the number of records increased over time (22700), it should eventually complete at some point right ?
    2. When the batch executes the select statement and goes into the loop for each record, if some other application tries to insert record to same table, does the select query  goes to a hang state ?

    Please advise. Queries are below.

    Select Query

    select ReportInstanceId from CQRReportInstance, CQRReport 
    where CQRReportInstance.ReportId = CQRReport.ReportId 
    and (    (LifeSpan <> 0 and datediff(hour,StartDate,getdate()) > LifeSpan)    
     or     (datediff(hour,StartDate,getdate()) > 12 and OutputURL = '') )

    Update Query

    update CQRReportInstance set ReportStatusId = 1  where ReportInstanceId = 189112

     

  • December 22, 2019 at 10:41 pm

    #3709863

    It is not uncommon that performance changes drastically from day to another in DBMS with a cost-based optimizer. As the data volume grows, the statistics changes, and the optimizer may decide on a different plan from yesterday, and this is may be a plan with very different characteristics. To the better. Or to the worse.

    When seeing the query, I am not really surprised that performance is not very good. It would be better to rewrite a condition like

    datediff(hour,StartDate,getdate()) > 12

    to

    StartDate > dateadd(HOUR, 12, getdate())

    as this improves the chances for an index on StartDate to be used.

    That alone is not likely to help. That OR condition is also problematic.

    It is unlikely that the INSERT statement causes the SELECT to be blocked for a longer time, unless the INSERT is also blocked by something.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • December 23, 2019 at 2:35 pm

    #3709963

    Do you have an index on CQRReportInstance(ReportInstanceId) ?

    If you use INSENSITIVE when you create the cursor it will make a copy of the data used by the cursor in tempdb https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-ver15

    Do you really need use a loop?

    Would this single query do the same job?

    UPDATE ri
       SET ri.ReportStatusId = 1
      FROM CQRReportInstance ri
     INNER JOIN CQRReport r
             ON r.ReportId = ri.ReportId
            AND ((LifeSpan <> 0 AND DATEDIFF(hour, StartDate, GETDATE()) > LifeSpan)
                 OR (DATEDIFF(hour, StartDate, GETDATE()) > 12 AND OutputURL = ''))

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply