Query is in suspended mode

  • I am really fed up with one of the query which is taking forever. i could not figure out the issue. I have checked the indexes and all the indexes are perfect and uptodate.

    Query runs in a parallel mode so use the option max dop =1 but did not help.

    appreciated if someone will help me

  • Can you put some more information.

  • Perhaps you could show us the query?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Suspended indicates that the query is waiting for a resource. It may be a lock, it may be a latch, it may be for an IO to complete, it may be for time on the CPU or for a memory grant.

    What's the wait type (as indicated in sys.dm_exec_requests) and what's the wait resource?

    Can you post table definition, index definitions, query and the query's estimated exec plan (saved as a .sqlplan file, zipped and attached to your post)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Here is more information about the query.

    STATUS : SUSPENDED

    WAIT TYPE : CXPACKET

    WAIT CATEGORY : PARALLELISM

    #OF WAITERS : 2

    % WAIT TIME 100%

    I tried option maxdop = 1 but no luck.

    Here is the query.

    ALTER PROCEDURE [dbo].[spDuplicate_Invoices_Report_rev2]

    @ClientNamevarchar(60) = NULL,

    @StartDatedatetime = NULL

    --@StartDatevarchar(20) = NULL

    AS

    BEGIN TRY

    If @ClientName = 'ALL' Or @ClientName = '' Set @ClientName = Null

    SELECT DISTINCT

    DN200.CLIENT_NAME,

    INVOICE.GROUP_ZZ,

    DN100.NAME AS LOCATION,

    DIM_FSC.SC_FSC_CAT_NAME AS RptCat_1,

    PATIENT.PAT_NM,

    PATIENT.MRN,

    INVOICE.INV_NUM,

    TRANSAC.CHARGE_AMOUNT,

    DIM_FSC.FSC_NUMBER AS FSC,

    INVOICE.INV_CRE_DT,

    DIM_TIME.EXTERNAL_PERIOD AS INV_CRE_PD,

    TRANSAC.SER_DT,

    TRANSAC.BATCH_NUM AS BATCH,

    INVOICE.INV_SCHED_LOC AS SCHED_LOC,

    INVOICE.VIS_NUM,

    INVOICE.PATIENT,

    INVOICE.INVOICE,

    INVOICE.PROV,

    INVOICE.LOC,

    TRANSAC.PROC_ZZ,

    TRANSAC.MOD,

    INVOICE.DX_ONE,

    INVOICE.INV_BAL

    INTO #SearchData1

    FROM IDX.dbo.INVOICE INVOICE

    INNER JOIN IDX.dbo.TRANSAC TRANSAC ON

    INVOICE.INVOICE = TRANSAC.INVOICE AND

    INVOICE.PATIENT = TRANSAC.PATIENT AND

    INVOICE.GROUP_ZZ = TRANSAC.GROUP_ZZ

    INNER JOIN IDX.dbo.PATIENT PATIENT ON

    INVOICE.PATIENT = PATIENT.PATIENT

    INNER JOIN IDX_OPS.dbo.DIM_TIME DIM_TIME ON

    INVOICE.INV_CRE_DT = DIM_TIME.date_id

    INNER JOIN IDX.dbo.DN100 DN100 ON

    INVOICE.LOC = DN100.RECORD_NUMBER

    INNER JOIN IDX.dbo.DN200 DN200 ON

    INVOICE.GROUP_ZZ = DN200.RECORD_NUMBER

    INNER JOIN IDX_OPS.dbo.DIM_FSC DIM_FSC ON

    INVOICE.FSC = DIM_FSC.FSC_KEY

    WHERE

    ((@ClientName Is Null) Or (DN200.CLIENT_NAME = @ClientName)) AND

    (INVOICE.INV_CRE_DT >= @StartDate) AND

    (INVOICE.INV_BAL <> 0) AND

    (INVOICE.GROUP_ZZ <> 29) AND

    (TRANSAC.CHARGE_AMOUNT <> 0) AND

    (TRANSAC.PAY_CODE_NUM = 99) AND

    (DIM_FSC.FSC_NUMBER NOT IN ('999', '73', '67', '69', '76', '72', '68')) AND

    (DIM_FSC.CLIENT_MCA_NUMBER IS NULL) AND

    (DIM_FSC.SC_FSC_CAT_NAME <> 'SPECIAL BILLING')

    --------------------------------

    SELECT *

    INTO #SearchData2

    FROM #SearchData1

    --------------------------------

    /*Compare 2 Search Data tables & Return Duplicate Invoices to the Final Results temp table*/

    SELECTDISTINCT

    #SearchData1.CLIENT_NAME,

    #SearchData1.GROUP_ZZ,

    #SearchData1.RptCat_1'RptCat_1_A',

    #SearchData1.PAT_NM'PAT_NM_A',

    #SearchData1.MRN'MRN_A',

    #SearchData1.INV_NUM'INV_NUM_A',

    #SearchData1.Location'Location_A',

    #SearchData1.CHARGE_AMOUNT'CHARGE_AMOUNT_A',

    #SearchData1.FSC'FSC_A',

    #SearchData1.INV_CRE_DT'INV_CRE_DT_A',

    #SearchData1.INV_CRE_PD'INV_CRE_PD_A',

    #SearchData1.SER_DT'SER_DT_A',

    #SearchData1.BATCH'BATCH_A',

    #SearchData1.SCHED_LOC'SCHED_LOC_A',

    #SearchData1.VIS_NUM'VIS_NUM_A',

    #SearchData1.MOD'MODIFIER_A',

    #SearchData1.DX_ONE'DX_ONE_A',

    #SearchData1.INV_BAL'INV_BAL_A',

    #SearchData2.RptCat_1'RptCat_1_B',

    #SearchData2.PAT_NM'PAT_NM_B',

    #SearchData2.MRN'MRN_B',

    #SearchData2.INV_NUM'INV_NUM_B',

    #SearchData2.Location'Location_B',

    #SearchData2.CHARGE_AMOUNT'CHARGE_AMOUNT_B',

    #SearchData2.FSC'FSC_B',

    #SearchData2.INV_CRE_DT'INV_CRE_DT_B',

    #SearchData2.INV_CRE_PD'INV_CRE_PD_B',

    #SearchData2.SER_DT'SER_DT_B',

    #SearchData2.BATCH'BATCH_B',

    #SearchData2.SCHED_LOC'SCHED_LOC_B',

    #SearchData2.VIS_NUM'VIS_NUM_B',

    #SearchData2.MOD'MODIFIER_B',

    #SearchData2.DX_ONE'DX_ONE_B',

    #SearchData2.INV_BAL'INV_BAL_B'

    INTO #FinalResults

    FROM #SearchData1

    LEFT OUTER JOIN #SearchData2 ON

    #SearchData1.GROUP_ZZ = #SearchData2.GROUP_ZZ AND

    #SearchData1.PATIENT = #SearchData2.PATIENT AND

    #SearchData1.PROV = #SearchData2.PROV AND

    #SearchData1.LOC = #SearchData2.LOC AND

    #SearchData1.SER_DT = #SearchData2.SER_DT AND

    #SearchData1.PROC_ZZ = #SearchData2.PROC_ZZ AND

    #SearchData1.DX_ONE = #SearchData2.DX_ONE AND

    #SearchData1.CHARGE_AMOUNT = #SearchData2.CHARGE_AMOUNT AND

    #SearchData1.INVOICE <> #SearchData2.INVOICE

    WHERE

    (#SearchData1.MOD = #SearchData2.MOD AND

    #SearchData1.INV_NUM < #SearchData2.INV_NUM)

    OR

    (#SearchData1.MOD IS NULL AND

    #SearchData2.MOD IS NULL AND

    #SearchData1.INV_NUM < #SearchData2.INV_NUM)

    ---------------------------------

    /*SUM all the Invoice amounts for the final record set*/

    SELECT

    CLIENT_NAME,

    GROUP_ZZ,

    RptCat_1_A,

    PAT_NM_A,

    MRN_A,

    INV_NUM_A,

    LOCATION_A,

    SUM(CHARGE_AMOUNT_A) 'CHARGE_AMOUNT_A',

    FSC_A,

    INV_CRE_DT_A,

    INV_CRE_PD_A,

    SER_DT_A,

    BATCH_A,

    SCHED_LOC_A,

    VIS_NUM_A,

    MODIFIER_A,

    DX_ONE_A,

    RptCat_1_B,

    PAT_NM_B,

    MRN_B,

    INV_NUM_B,

    LOCATION_B,

    SUM(CHARGE_AMOUNT_B) 'CHARGE_AMOUNT_B',

    FSC_B,

    INV_CRE_DT_B,

    INV_CRE_PD_B,

    SER_DT_B,

    BATCH_B,

    SCHED_LOC_B,

    VIS_NUM_B,

    MODIFIER_B,

    DX_ONE_B

    FROM #FinalResults

    GROUP BY

    CLIENT_NAME,

    GROUP_ZZ,

    RptCat_1_A,

    PAT_NM_A,

    MRN_A,

    INV_NUM_A,

    LOCATION_A,

    FSC_A,

    INV_CRE_DT_A,

    INV_CRE_PD_A,

    SER_DT_A,

    BATCH_A,

    SCHED_LOC_A,

    VIS_NUM_A,

    MODIFIER_A,

    DX_ONE_A,

    RptCat_1_B,

    PAT_NM_B,

    MRN_B,

    INV_NUM_B,

    LOCATION_B,

    FSC_B,

    INV_CRE_DT_B,

    INV_CRE_PD_B,

    SER_DT_B,

    BATCH_B,

    SCHED_LOC_B,

    VIS_NUM_B,

    MODIFIER_B,

    DX_ONE_B

    -------------------------------

    DROP TABLE #FinalResults

    DROP TABLE #SearchData1

    DROP TABLE #SearchData2

    END TRY

    BEGIN CATCH

    SELECT

    CONVERT (TEXT, ERROR_MESSAGE()) AS ErrorMessage

    END CATCH

  • That's actually like three queries in one procedure there. Any idea which of the three is taking all of the time or what the breakdown is between them? And if it is the first one, then we will need the DDL of the source tables (along with any indexes). Also, query plans would help a lot here as well.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • The first query which is creating the problem. I checked the query plan and iit's taking index seek.

    How to attached showplan in the reply.?

  • If it is an XML *.sqlplan type file (which is much preferred), then put it in a ZIP file and attach it using the "Post Options", below.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • balbirsinghsodhi (10/31/2008)


    I am really fed up with one of the query which is taking forever. i could not figure out the issue. I have checked the indexes and all the indexes are perfect and uptodate.

    Query runs in a parallel mode so use the option max dop =1 but did not help.

    appreciated if someone will help me

    Here is a good query trouble shoot issues with.

    ---------

    SELECT r.session_id -- new column for SPID

    ,r.database_id

    ,r.user_id

    ,r.status

    ,st.text

    ,r.wait_type

    ,r.wait_time

    ,r.last_wait_type

    ,r.command

    ,es.host_name

    ,es.program_name

    ,es.nt_domain

    ,es.nt_user_name

    ,es.login_name

    ,mg.dop --Degree of parallelism

    ,mg.request_time --Date and time when this query requested the memory grant.

    ,mg.grant_time --NULL means memory has not been granted

    ,mg.requested_memory_kb --Total requested amount of memory in kilobytes

    ,mg.granted_memory_kb --Total amount of memory actually granted in kilobytes. NULL if not granted

    ,mg.required_memory_kb --Minimum memory required to run this query in kilobytes.

    ,mg.query_cost --Estimated query cost.

    ,mg.timeout_sec --Time-out in seconds before this query gives up the memory grant request.

    ,mg.resource_semaphore_id --Nonunique ID of the resource semaphore on which this query is waiting.

    ,mg.wait_time_ms --Wait time in milliseconds. NULL if the memory is already granted.

    ,CASE mg.is_next_candidate --Is this process the next candidate for a memory grant

    WHEN 1 THEN 'Yes'

    WHEN 0 THEN 'No'

    ELSE 'Memory has been granted'

    END AS 'Next Candidate for Memory Grant'

    ,rs.target_memory_kb --Grant usage target in kilobytes.

    ,rs.max_target_memory_kb --Maximum potential target in kilobytes. NULL for the small-query resource semaphore.

    ,rs.total_memory_kb --Memory held by the resource semaphore in kilobytes.

    ,rs.available_memory_kb --Memory available for a new grant in kilobytes.

    ,rs.granted_memory_kb --Total granted memory in kilobytes.

    ,rs.used_memory_kb --Physically used part of granted memory in kilobytes.

    ,rs.grantee_count --Number of active queries that have their grants satisfied.

    ,rs.waiter_count --Number of queries waiting for grants to be satisfied.

    ,rs.timeout_error_count --Total number of time-out errors since server startup. NULL for the small-query resource semaphore.

    ,rs.forced_grant_count --Total number of forced minimum-memory grants since server startup. NULL for the small-query resource semaphore.

    FROM sys.dm_exec_requests r

    INNER JOIN sys.dm_exec_sessions es

    ON r.session_id = es.session_id

    INNER JOIN sys.dm_exec_query_memory_grants mg

    ON r.session_id = mg.session_id

    INNER JOIN sys.dm_exec_query_resource_semaphores rs

    ON mg.resource_semaphore_id = rs.resource_semaphore_id

    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)st

  • Scott.. I ran the query you provided me and got the result. How value I should look for and do what.

    Here are the important values.

    Status : suspended

    wait_type : CXPACKET

    wait time : 81240

    last_Wait_type : CXPACKET

    command : select into

    dop : 8

    requested memory kb : 293440

    query cost : 1195.80491267745

    timeout_sec : 29895

    resource_semaphore_id : 0

    Next candidate for memory grant : memory has been granted

    target memory kb : 21443360

    max_target memory kb : 21443360

    Total memory kb 294464

    used memory kb 48929

    grantee_count : 2

  • That query is mainly to check to see if there are any memory problems.

    This query is will show in detail the query plan in the xml field click on the link and save as .sqlplan

    Then close the xml window and open the .sqlplan and look for any type of bottle necks. or if you can zip up the query plan and attach it we all can view it.

    SELECT

    A.Session_ID SPID,

    ISNULL(B.status,A.status) Status,

    A.login_name Login,

    A.host_name HostName,

    C.BlkBy,

    DB_NAME(B.Database_ID) DBName,

    B.Command,

    ISNULL(B.cpu_time, A.cpu_time) CPUTime,

    ISNULL((B.reads + B.writes),(A.reads + A.writes)) DiskIO,

    ISNULL(B.writes,A.writes) Writes,

    ISNULL(B.reads,A.reads) Reads,

    Wait_Type WaitType,

    Wait_Time WaitTime,

    A.last_request_start_time LastBatch,

    A.program_name ProgramName,

    T.text SQLStatement,

    P.query_plan QueryPlan

    FROM

    sys.dm_exec_sessions A

    LEFT JOIN sys.dm_exec_requests B

    ON A.session_id = B.session_id

    LEFT JOIN

    (SELECT

    A.request_session_id SPID,

    B.blocking_session_id BlkBy

    FROM sys.dm_tran_locks as A

    INNER JOIN sys.dm_os_waiting_tasks as B

    ON A.lock_owner_address = B.resource_address) C

    ON A.Session_ID = C.SPID

    OUTER APPLY

    sys.dm_exec_sql_text(sql_handle) T

    OUTER APPLY

    sys.dm_exec_query_plan(plan_handle) P

    Where

    ISNULL(B.status,A.status) not in ('sleeping','background','dormant') AND

    A.Session_id <> @@SPID

  • Also forgot to ask is this a single proc server or a quad ect? Only reason i ask SQL server has a problem if Mdop is set to use all proc's and you have over 8, Microsoft has said there might be some issues with some queries if you have over 8 cpu's and mdop set to 0.

    Our setup is set to 8, we have a quad quad beast

  • Attached is the query plan.

    I have a processor from 0 to 7.means 8 processor and the minimum server memory is 24000 mb and maximum memory is 28000 mb

    I am not sure if I have a quad processor. How to check it. ?

  • This - ((@ClientName Is Null) Or (DN200.CLIENT_NAME = @ClientName)) - is BAD BAD BAD. 🙂 The optimizer just cannot do the right thing when you have that. I have been fighting that with 2 clients of mine. Typical fix is 3-5 orders of magnitude faster.

    Here's what you can do:

    1) make the entire thing dynamic sql. If clientname is null, no filter for that, otherwise it will be dn200.client_name = 'actualclientnamevaluehere' ... This has an added benefit of having the date hard-coded in the query too, which can make for a very fast plan if the date is limiting. Cache bloat can become an issue here however.

    OR

    2) have an IF statement where you test @clientname. If it is NULL, have a select into your temp table that has NO filter in the where clause for clientname. If it is NOT NULL, have a DIFFERENT select in the ELSE clause of the IF that has Client_name = @Clientname. Now you will get the appropriate type of row estimation and join types for either case. I would also use the WITH RECOMPILE hint on the statement itself since the date is variable and the client name could result in few or many rows too.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • SQL Guru.. I know this query is not good for optimizer so I did modify and used in a following manner but still going in a suspended mode and the wait type is PAGEIOLATCH_SH. I think it is something related to multi processor. Looks like SQL is not good in multiprocessor.

    SELECT DISTINCT

    DN200.CLIENT_NAME,

    INVOICE.GROUP_ZZ,

    DN100.NAME AS LOCATION,

    DIM_FSC.SC_FSC_CAT_NAME AS RptCat_1,

    PATIENT.PAT_NM,

    PATIENT.MRN,

    INVOICE.INV_NUM,

    TRANSAC.CHARGE_AMOUNT,

    DIM_FSC.FSC_NUMBER AS FSC,

    INVOICE.INV_CRE_DT,

    DIM_TIME.EXTERNAL_PERIOD AS INV_CRE_PD,

    TRANSAC.SER_DT,

    TRANSAC.BATCH_NUM AS BATCH,

    INVOICE.INV_SCHED_LOC AS SCHED_LOC,

    INVOICE.VIS_NUM,

    INVOICE.PATIENT,

    INVOICE.INVOICE,

    INVOICE.PROV,

    INVOICE.LOC,

    TRANSAC.PROC_ZZ,

    TRANSAC.MOD,

    INVOICE.DX_ONE,

    INVOICE.INV_BAL

    --drop table #SearchData1

    INTO #SearchData1

    FROM IDX.dbo.INVOICE INVOICE

    INNER JOIN IDX.dbo.TRANSAC TRANSAC ON

    INVOICE.INVOICE = TRANSAC.INVOICE AND

    INVOICE.PATIENT = TRANSAC.PATIENT AND

    INVOICE.GROUP_ZZ = TRANSAC.GROUP_ZZ

    INNER JOIN IDX.dbo.PATIENT PATIENT ON

    INVOICE.PATIENT = PATIENT.PATIENT

    INNER JOIN IDX_OPS.dbo.DIM_TIME DIM_TIME ON

    INVOICE.INV_CRE_DT = DIM_TIME.date_id

    INNER JOIN IDX.dbo.DN100 DN100 ON

    INVOICE.LOC = DN100.RECORD_NUMBER

    INNER JOIN IDX.dbo.DN200 DN200 ON

    INVOICE.GROUP_ZZ = DN200.RECORD_NUMBER

    INNER JOIN IDX_OPS.dbo.DIM_FSC DIM_FSC ON

    INVOICE.FSC = DIM_FSC.FSC_KEY

    WHERE

    (DN200.CLIENT_NAME = 'name of the client') AND

    (INVOICE.INV_CRE_DT >= '2007-08-01 00:00:00') AND

    (INVOICE.INV_BAL <> 0) AND

    (INVOICE.GROUP_ZZ <> 29) AND

    (TRANSAC.CHARGE_AMOUNT <> 0) AND

    (TRANSAC.PAY_CODE_NUM = 99) AND

    (DIM_FSC.FSC_NUMBER NOT IN ('999', '73', '67', '69', '76', '72', '68')) AND

    (DIM_FSC.CLIENT_MCA_NUMBER IS NULL) AND

    (DIM_FSC.SC_FSC_CAT_NAME <> 'SPECIAL BILLING')

Viewing 15 posts - 1 through 15 (of 32 total)

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