February 18, 2014 at 6:49 pm
Hi,
I am running one stored procedure in which it contains union and inserting into another table which is referred in joining condition also. Its runnign morethan 5 hours but not completed yet but it useally tooks only 4 minutes earlier.
But its taking too long time to run and when i monitor this in activity monitor cxpacket wait type and wait resource of exchange event ID=pipe362 it shows.
Please suggest how can i fine tune this.
Regards,
Gangadhara
February 18, 2014 at 7:19 pm
Do you have code and/or execution plan for this procedure?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 18, 2014 at 9:20 pm
Here is my code
I am not able to retrieve the execution plan as the query itself is not completed.
Please help,I have created this as the SP
IF OBJECT_ID('tempdb..#MissingPAIntelorCP') IS NOT NULL
DROP TABLE #MissingPAIntelorCP
CREATE TABLE #MissingPAIntelorCP(
ActID NVARCHAR(50)
)
INSERT INTO #MissingPAIntelorCP
SELECT DISTINCT ActID
FROM DML.ExceptionsTrackingTable (NOLOCK)
WHERE ExceptionType IN ('No PAIntel Activations and CP Data' ,'No PAIntel Activations')
IF NOT EXISTS (SELECT Name
FROM sys.indexes
WHERE name = 'NCIX_MissingPAIntelorCP_ActID'
AND object_id = OBJECT_ID(N'#MissingPAIntelorCP'))
CREATE CLUSTERED INDEX NCIX_MissingPAIntelorCP_ActID ON #MissingPAIntelorCP(ActID)
INSERT INTO DML.ExceptionsTrackingTable
(AppCode
,LiveIDEncryptedKey
,ActivationModel
,DMLUpdatedTimestamp
,ActID
,MachineKey
,MachineType
,PartnerEntitlementId
,RunID
,BatchID
,ExceptionReason
,ExceptionType
,DMLIsDeletedFlag
,EntitlementID)
SELECT DISTINCT 'OREF' AppCode
,OLSEntitlement.LiveIDEncryptedKey
,'Subscription' ActivationModel
,GETDATE() DMLUpdatedTimestamp
,CAST(Machine.MachineKey AS VARCHAR(50)) COLLATE Latin1_General_CS_AS ActID
,Machine.MachineKey
,Machine.MachineType
,OLSEntitlement.PartnerEntitlementId
,@RunID RunID
,@BatchID BatchID
,N'No data from PAIntel Activations AND CP,but OLS has data' ExceptionReason
,N'No PAIntel Activations and CP Data' ExceptionType
,0 AS DMLIsDeletedFlag
,Machine.EntitlementId AS EntitlementID
FROM DML.ExtOLSMachine Machine
JOIN (SELECT EntitlementId
,LiveIDEncryptedKey
,PartnerEntitlementId
FROM DML.ExceptionsTrackingtable
WHERE EntitlementId IS NOT NULL and DMLIsDeletedFlag = 0) OLSEntitlement
ON OLSEntitlement.EntitlementId = Machine.EntitlementId
LEFT JOIN DML.ExtOLSEntitlement Entitlement
ON Entitlement.EntitlementId = Machine.EntitlementId
LEFT JOIN #MissingPAIntelorCP MissingPAIntelorCP
ON CAST(Machine.MachineKey AS VARCHAR(50)) COLLATE Latin1_General_CS_AS = MissingPAIntelorCP.ActID
WHERE Entitlement.EntitlementId IS NULL
AND MissingPAIntelorCP.ActID IS NULL
-- OPTION (MAXDOP 2)
UNION
SELECT DISTINCT 'OREF' AppCode
,OLSEntitlement.LiveIDEncryptedKey
,'Subscription' ActivationModel
,GETDATE() DMLUpdatedTimestamp
,CAST(Machine.MachineKey AS VARCHAR(50)) COLLATE Latin1_General_CS_AS ActID
,Machine.MachineKey
,Machine.MachineType
,OLSEntitlement.PartnerEntitlementId
,@RunID RunID
,@BatchID BatchID
,N'No data from PAIntel Activations AND CP,but OLS has data' ExceptionReason
,N'No PAIntel Activations and CP Data' ExceptionType
,0 AS DMLIsDeletedFlag
,Machine.EntitlementId AS EntitlementID
FROM DML.ExtOLSMachine Machine
JOIN (SELECT EntitlementId
,LiveIDEncryptedKey
,PartnerEntitlementId
FROM DML.ExceptionsTrackingTable
WHERE EntitlementId IS NOT NULL and DMLIsDeletedFlag = 0) OLSEntitlement
ON OLSEntitlement.EntitlementId = Machine.CalcEntitlementId
AND OLSEntitlement.EntitlementId <> Machine.EntitlementId
LEFT JOIN DML.ExceptionsTrackingTable ExcludeNewFormatExcep
ON Machine.EntitlementId = ExcludeNewFormatExcep.EntitlementId and DMLIsDeletedFlag = 0
LEFT JOIN DML.ExtOLSEntitlement Entitlement
ON Entitlement.EntitlementId = Machine.EntitlementId
LEFT JOIN #MissingPAIntelorCP MissingPAIntelorCP
ON CAST(Machine.MachineKey AS VARCHAR(50)) COLLATE Latin1_General_CS_AS = MissingPAIntelorCP.ActID
WHERE Entitlement.EntitlementId IS NULL
AND ExcludeNewFormatExcep.EntitlementId IS NULL
AND MissingPAIntelorCP.ActID IS NULL --OPTION (MAXDOP 2)
SELECT @RecordCount = @@ROWCOUNT
IF OBJECT_ID('tempdb..#MissingPAIntelorCP') IS NOT NULL
DROP TABLE #MissingPAIntelorCP
February 19, 2014 at 6:17 am
I'd be interested in seeing the execution plan to better understand how things are resolving, but, this alone will lead to scans of the temporary table:
ON CAST(Machine.MachineKey AS VARCHAR(50)) COLLATE Latin1_General_CS_AS
There may be other issues as well.
What is the value of Cost Threshold for Parallelism on your server? If it's set to the default value of 5, I'd suggest bumping it up to a higher value. You can average the costs of queries in your system and then set the value above them as a starting point. Or, take the lazy way out; 20 for a reporting system, 50 for an OLTP system and then adjust as necessary.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 19, 2014 at 6:28 am
Are all the distincts really necessary? Is the Union necessary or will union all work?
Personally I prefer creating clustered index on a temp table before putting data into it (if I put a clustered index on at all). Have you tested which is faster (insert first or create index first)?
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply