Post reply

Query is getting Timed out

  • December 7, 2019 at 8:15 am

    #3705632

    Hi All,

    One of the query on Production, it is running over 5 overs and still not yet done. How can we tune such a query?

    App team has set a timeout of 2 hours and so query is getting timed out as soon as it reaches 2 hours time out value.

    So, I tied to run the same query in SSMS and Plan Explorer to check to see if the query gets completed and collect the Actual Execution plan. However, the query never finishes its execution.

    Status of the spid always shows = runnable , but there is no cpu pressure on server at all. Avg cpu utilization in task mgr is 40-65% sustained value.

    Note: Maxdop setting = 1. Server has 12 logical cpus though. I don't why this was set to 1.

    How can we make this query fast and finish its execution. Kindly help.

    If the query finishes, I will attach the Actual Execution plan collected using SQL Sentry Plan Explorer.

    Collected below information so far and attaching everything in single zip file named "Querytuning.zip"

    - Query executing for more than 5 hours and still NOT YET finished !! - "Executing forever.PNG"

    - sp_whoisactive output - "sp_WIA.PNG"

    - Table and Index defintions - ddl.sql

    - Query text - "src_qry.txt"

    - Estimated Execution Plan ( collected using SQL Sentry Plan Explorer) -"EstimatedPlan.pesession"

    - Table sizes and index fragmentation details "Tablesizes n Index Fragmentation details.PNG"

    - When was stats got updated for those tables - "statistics update date.PNG"

    - sp_configue settings -"sp_configure settings.PNG"

    - cpu info - "cpu_info.PNG"

    - memory info - "memory_info.PNG"

    Environment details

    =====================

    Windows

    =========

    Microsoft Windows Server 2012 R2 Datacenter

    SQL Server

    ==========

    Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)

    Aug 15 2017 10:23:29

    Copyright (c) Microsoft Corporation

    Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

    Regards,

    Sam

    Attachments:
    You must be logged in to view attached files.

  • December 8, 2019 at 9:10 am

    #3705720

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • December 16, 2019 at 4:18 pm

    #3708203

    Hello.  Did you resolve this?  If not, you may want to consider putting pictures and code into your post without using a zip file.  I wouldn't download and open files like that.

     

  • December 16, 2019 at 7:00 pm

    #3708268

    There's nothing wrong with a ZIP file, and having the pesession file that's in it is much more valuable than any pictures can provide.  Here's some clues I see with just a quick glance at the estimated execution plan and the statistics query results:

    • table DM_NOTF_CHG_EVNT_ATTR has 2.7 million rows, but since the join to it is on NOTF_CHG_EVNT_ID and there is no index on that foreign key column, that is why SQL Server does a full table scan on it, (estimated 83% of execution cost)
    •  It looks like the limiting factor on this query should be in the condition BTCH_DTL.NOTF_PRCS_JOB_ID = 167 but again the table DM_NOTF_BTCH_DTL doesn't have an index on that column
    • A lot of hash matches and sorts are occurring, which will consume a lot of CPU, again more indications of missing indexes.

    WHERE clause conditions and JOIN conditions are good clues what columns should be indexed in your tables

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

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