October 31, 2008 at 3:46 pm
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
October 31, 2008 at 8:04 pm
Can you put some more information.
October 31, 2008 at 9:07 pm
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]
November 1, 2008 at 2:45 am
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
November 2, 2008 at 7:02 am
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
November 2, 2008 at 12:09 pm
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]
November 3, 2008 at 6:51 am
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.?
November 3, 2008 at 8:01 am
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]
November 3, 2008 at 8:22 am
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
November 3, 2008 at 10:08 am
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
November 3, 2008 at 10:23 am
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
November 3, 2008 at 10:25 am
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
November 3, 2008 at 9:41 pm
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. ?
November 4, 2008 at 9:35 am
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
November 4, 2008 at 10:54 am
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