May 4, 2008 at 3:09 pm
wondering if someone can verify something for me please.
While trying to identify why a batch job had blown out by 6 hrs, I noticed that some of the process ids be identified as being blocked by itself.
This is SQL Server 2K so in EM in the Locks/ProcessID tree when you expand it you would see
spid 71 (blocked by 71)
spid 71 (blocking)
is this caused by parallelism??
the machine is a 4 cpu Opteron 4GB RAM, WinServer 2003 R2 x64 sp2
thanks
May 4, 2008 at 5:18 pm
it can be caused by parallelism, waittype will generally be 'cxpacket', and there will be multi connections with the same spid.
Can also be caused by i/o, waittype will be various latch type waits.
Not necessarily indicative of a problem, this behaviour has always been there but was not written to the block column in sysprocesses until SP4.
I see it a lot in i/o intensive operations such as bulk inserts and indexdefrags, and other processes can be blocked out behind them, so perhaps indexdefrags are more intrusive than MS claim.
---------------------------------------------------------------------
May 4, 2008 at 9:46 pm
It's not uncommon for a proc to look like it's blocking itself... if a proc is taking way to long to run, it's not usually because it's blocking itself... it because the proc either has some form of RBAR or it's doing a full recompile for each row updated (hidden form of RBAR worse than a cursor).
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2008 at 10:45 pm
thanks for your replies -
what do you mean by recompiling for each row updated?
Are you saying that the query plan is being re-evaluated for each time a row has been updated and goes to the next row (top of the loop)
thanks
May 4, 2008 at 11:28 pm
john pan (5/4/2008)
thanks for your replies -what do you mean by recompiling for each row updated?
Are you saying that the query plan is being re-evaluated for each time a row has been updated and goes to the next row (top of the loop)
thanks
Yep... that's what I'm saying. And when it happens, it crushes at least one cpu for at least a couple of hours. You might want to post the offending query so we can take a look-see to see if it's one of "those" types of queries.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2008 at 5:55 am
Even if it's not RBAR, recompiles can cause queries to block themselves. See this link to identify what the cause is, if you're having a recompile. This MSDN article is focused on SQL Server 2000, but most of the same rules apply to 2005. And one more.
I hate recompiles. A consultant developed a system for us that resulted in LONG recompiles every 15 minutes or so, constantly blocking the system. I think I developed a phobia. The good news is, you can easily avoid most of them by simply following the best practices outlined in the links above.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2008 at 6:11 am
Grant,
hate recompiles? see this:
http://blogs.msdn.com/sqlprogrammability/archive/2007/01/17/10-0-plan-cache-flush.aspx
and this:
http://www.sqlservercentral.com/Forums/Topic494663-149-1.aspx#bm494664
---------------------------------------------------------------------
May 5, 2008 at 6:16 am
Maybe I need to clarify that. I hate out of control recompiles. On rare occasions I've found queries that benefit from having WITH RECOMPILE attached to them so that you get a new plan on each execution. Don't get me wrong, recompiles are all part of the process. It's those unexpected, in the middle of your query recompiles that can lead to seriously messed up systems.
Sorry for the absolutist statement. As usual, it depends is the correct answer... Depending...
😎
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2008 at 1:09 pm
OK - thanks guys
I'll see if I can do a trace and set up the recompile counter.
I'll see if I can post the proc today and read up on the posts
thanks
john
May 5, 2008 at 2:13 pm
Another question:
if a stored proc is being executed and its then recompiled before it finishes what happens to the thread that is in the middle of the execution - does it stall and wait for the recompile to finish?
And this is what I am seeing when I see a spid being blocked by another - is it the blocking spid that is doing the recompile then.
Where does parallelism come into this - or doesn't it. Its just another possible cause.
thanks
May 5, 2008 at 2:29 pm
apologies again. I have started to go through the links and have some more questions.
All this started because I put new indices and new statistics on columns recommended by the DTA.
So if a percentage of the rows change - as it would if you were inserting AND auto create stats and auto update stats was on then this would cause a recompile for EVERY column that new stats were being created for or updated?
So in a way I have caused this myself?
May 5, 2008 at 6:35 pm
OK - here's the code - one part of many, but this code I did notice blocking itself.
(Apologies if this is not how I should do it - just cutting and pasting it)
/****** Object: StoredProcedure [dbo].[AddressTransfer] Script Date: 05/06/2008 07:52:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
select distinct source, sourceid, sourceid1 from clientmatch
select count(*) FROM DataStore..Address --740991
select count(*) FROM DataStore..EntityAddressType --740991
select count(*) FROM Match_WITEst..xfr_Address where entityid is not null --855713
SELECT DISTINCT Entityid, AddressLine1, AddressLine2, AddressLine3, AddressLine4, location FROM Xtransfer.dbo.xfr_Address
EXEC AddressTransfer 'dsc',0
*/
CREATE PROC [dbo].[AddressTransfer]
@User NVARCHAR(255)='',
@Debug_RefreshXfrTable BIT = 1
AS
EXEC dbo.xAuditInsert '',@@PROCID,1,'Start',0,@@Error
DECLARE @ErrorNumber INT, @Rows INT, @MSG VARCHAR(255), @DebugLevel INT
DECLARE @user-id INT, @UserName VARCHAR(255)
SET @DebugLevel = 0
SET @user-id = dbo.fn_xfr_GetUserID(@User)
IF @Debug_RefreshXfrTable = 1
BEGIN
IF EXISTS (select * from Xtransfer..sysobjects where id = object_id(N'Xtransfer.dbo.xfr_Address') )
DROP TABLE Xtransfer.dbo.xfr_Address
CREATE TABLE Xtransfer.dbo.xfr_Address (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[EntityID] [int] NULL,
[RegistryID] [int] NULL,
[AddressForServiceID] [int] NULL,
[AddressID] [int] NULL,
[AddressID_Person] [int] NULL,
[PamphletRequestID] [int] NULL,
[PamphletRequestID_Person] [int] NULL,
[ERSRegistrationID] [INT] NULL,
[ERSRegistrationID_Person] [INT] NULL,
[FormClientID] [INT] NULL,
[OrganisationContactLinkHistoryID] [INT] NULL,
[ACCNumber] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACCNumberContact] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressTypeID] [int] NULL,
[AddressLine1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressLine2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressLine3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressLine4] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostCode] [NVARCHAR] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DataSourceTypeID] [smallint] NULL,
UpdatedDate DATETIME NULL,
[UniqueAddressString] VARCHAR (1000),
LocationID INT NULL,
LastWord VARCHAR(255) NULL,
WIAddressID [int] NULL,
WIIsDeleted [smallint] NULL
) ON [PRIMARY]
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Create xfr_Address'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
ALTER TABLE Xtransfer.dbo.xfr_Address WITH NOCHECK ADD CONSTRAINT [PK_xfr_Address] PRIMARY KEY CLUSTERED ([ID]) ON [PRIMARY]
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Create [PK_xfr_Address]'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
--Default datasource to legacy
ALTER TABLE Xtransfer.dbo.xfr_Address ADD CONSTRAINT [DF_xfr_Address_Source] DEFAULT (2) FOR [DataSourceTypeID]
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Create [DF_xfr_Address_Source]'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
EXEC xfr_Address_InsertCiMS
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='EXEC xfr_Address_InsertCiMS'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
EXEC xfr_Address_InsertACC
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='EXEC xfr_Address_InsertACC'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
EXEC xfr_Address_InsertERSRegistration
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='EXEC xfr_Address_InsertERSRegistration'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
--EMS Contact Addresses
EXEC xfr_Address_InsertEMS
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='EXEC xfr_Address_InsertEMS'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
--EChannel Addresses
EXEC xfr_Address_InsertEChannel
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='EXEC xfr_Address_InsertEChannel'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
EXEC xfr_Address_Tidyup
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='EXEC xfr_Address_Tidyup'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
--Tidyup is done within the procedure
EXEC xfr_Address_InsertWIMatched
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='EXEC xfr_Address_InsertWIMatched'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
CREATE INDEX [IX_RegIDAFSID] ON Xtransfer.dbo.xfr_Address([RegistryID], [AddressForServiceID]) WITH FILLFACTOR = 90 ON [PRIMARY]
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Create [IX_RegIDAFSID]'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
CREATE INDEX [IX_ACCNumber] ON Xtransfer.dbo.xfr_Address([ACCNumber]) ON [PRIMARY]
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Create [IX_ACCNumber]'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
CREATE INDEX [IX_AddressID] ON Xtransfer.dbo.xfr_Address([AddressID]) ON [PRIMARY]
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Create [IX_AddressID]'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
/***************************************************************************
ACC
****************************************************************************/
UPDATE Xtransfer.dbo.xfr_Address
SET
EntityID=cm.ClientMatchID
FROM
Xtransfer.dbo.xfr_Address addr
INNER JOIN dbo.ClientMatch cm ON cm.ClientMatchID = addr.EntityID
INNER JOIN dbo.SourceSystem ss on ss.ID = cm.SourceSystemID and ss.Code in ('ACCE','ACCD','ACCS')
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Assign the EntityID for all ACC records'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
/***************************************************************************
CiMS
****************************************************************************/
--Assign the EntityID for all CiMS Party records
UPDATE Xtransfer.dbo.xfr_Address
SET
EntityID=cm.ClientMatchID
FROM
Xtransfer.dbo.xfr_Address addr
INNER JOIN CiMSLive.dbo.PartyAddressForService pafs ON
addr.AddressForServiceID = pafs.AddressForServiceID AND
addr.RegistryID = pafs.RegistryID
INNER JOIN dbo.ClientMatch cm ON
cm.SourceID1 = pafs.FileID AND
cm.SourceID = pafs.PartyID AND
cm.SourceID2 = pafs.RegistryID
INNER JOIN dbo.SourceSystem ss ON
ss.ID = cm.SourceSystemID AND
ss.Code in ('CiMSParty','CimsPartyNonOrg')
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Assign the EntityID for all CiMS Party records'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
--Assign the EntityID for all CiMS PersonOrganisation records
UPDATE Xtransfer.dbo.xfr_Address
SET
EntityID=cm.ClientMatchID
FROM
Xtransfer.dbo.xfr_Address addr
INNER JOIN CiMSLive.dbo.Address a ON addr.AddressID = a.AddressID
INNER JOIN dbo.ClientMatch cm ON
cm.SourceID = a.PersonOrganisationID
INNER JOIN dbo.SourceSystem ss ON
ss.ID = cm.SourceSystemID AND
ss.Code = 'CiMSOrg'
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Assign the EntityID for all CiMS Organisation records'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
--Assign the EntityID for all CiMS Pamphlet Data records
UPDATE Xtransfer.dbo.xfr_Address
SET
EntityID=cm.ClientMatchID
FROM
Xtransfer.dbo.xfr_Address addr
INNER JOIN CiMSLive.dbo.PamphletRequest pr
ON addr.PamphletRequestID_Person = pr.PamphletRequestID
AND addr.RegistryID = pr.RegistryID
INNER JOIN dbo.ClientMatch cm ON
cm.SourceID = pr.PamphletRequestID AND
cm.SourceID1 = pr.RegistryID
INNER JOIN dbo.SourceSystem ss ON
ss.ID = cm.SourceSystemID AND
ss.Code = 'CiMSPamphletPerson'
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Assign the EntityID for all CiMS Pamphlet Person records'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
/***************************************************************************
ERS Registration
****************************************************************************/
--Assign the EntityID for all ERS Registrations records
UPDATE Xtransfer.dbo.xfr_Address
SET
EntityID=cm.ClientMatchID
FROM
Xtransfer.dbo.xfr_Address addr
INNER JOIN ERSRegistrations.dbo.Registration ers ON addr.ERSRegistrationID = ers.RegistrationID
INNER JOIN dbo.ClientMatch cm ON
cm.SourceID = ers.RegistrationID
INNER JOIN dbo.SourceSystem ss ON
ss.ID = cm.SourceSystemID AND
ss.Code = 'ERURegistrations'
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Assign the EntityID for all ERS Registrations records'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
/***************************************************************************
EMS
****************************************************************************/
--Assign the EntityID for all EMS Contact records
UPDATE Xtransfer.dbo.xfr_Address
SET
EntityID=cm.ClientMatchID
FROM
Xtransfer.dbo.xfr_Address addr
INNER JOIN EMS.dbo.OrganisationContactLinkHistory oclh ON addr.OrganisationContactLinkHistoryID = oclh.ID
INNER JOIN EMS.dbo.DirectoryRecord dr ON oclh.DirectoryRecordId = dr.Id
INNER JOIN EMS.dbo.Address ad ON dr.AddressId = ad.Id
INNER JOIN EMS.dbo.Contact c ON c.Id = ISNULL(oclh.ReplacementContactId,oclh.ContactId)
INNER JOIN dbo.ClientMatch cm ON cm.SourceID = c.Id
INNER JOIN dbo.SourceSystem ss ON
ss.ID = cm.SourceSystemID AND
ss.Code = 'EMSContact'
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Assign the EntityID for all EMS Contacts'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
/***************************************************************************
EChannel
****************************************************************************/
UPDATE Xtransfer.dbo.xfr_Address
SET
EntityID=cm.ClientMatchID
FROM
Xtransfer.dbo.xfr_Address addr
INNER JOIN FormsBuilder.dbo.FormClientDetails fcd
ON addr.FormClientID = fcd.FormClientID
INNER JOIN dbo.ClientMatch cm
ON cm.SourceID = addr.FormClientID
INNER JOIN dbo.SourceSystem ss
on ss.ID = cm.SourceSystemID
AND ss.Code = 'ECFB'
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Assign the EntityID for all EChannel contacts'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
/****************************************************************************/
--Update the EntityID for any records that have been matched
UPDATE Xtransfer.dbo.xfr_Address
SET
EntityID=cm.ParentClientMatchID
FROM
Xtransfer.dbo.xfr_Address em
INNER JOIN dbo.ClientMatch cm
ON cm.ClientMatchID = em.EntityID
WHERE
cm.ParentClientMatchID IS NOT NULL
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Assign the EntityID for all Matched Records'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
/****************************************************************************/
CREATE INDEX [IX_EntityID] ON Xtransfer.dbo.xfr_Address([EntityID], [AddressTypeID]) ON [PRIMARY]
END --the end for "IF @Debug_RefreshXfrTable = 1"
--Note: We use the [UniqueAddressString] column instead of joining over 6+ columns in the queries to update DataStore
-- Insert the addresses
INSERT INTO DataStore..Address
(
EntityID,
Address1,
Address2,
Address3,
Address4,
LocationID,
PostCode,
DataSourceTypeID,
IsDeleted,
CreatedDate,
UpdatedDate,
UpdatedBy,
CreatedBy
)
SELECT DISTINCT
xfr.Entityid,
xfr.AddressLine1,
xfr.AddressLine2,
xfr.AddressLine3,
xfr.AddressLine4,
xfr.LocationID,
xfr.PostCode,
xfr.DataSourceTypeID,
0,
xfr.UpdatedDate,
xfr.UpdatedDate,
FROM
(
SELECT
Entityid,
UniqueAddressString,
AddressLine1,
AddressLine2,
AddressLine3,
AddressLine4,
PostCode,
LocationID,
DataSourceTypeID,
UpdatedDate=MAX(UpdatedDate)
FROM
Xtransfer.dbo.xfr_Address
WHERE
Entityid IS NOT NULL
GROUP BY
Entityid,
UniqueAddressString,
AddressLine1,
AddressLine2,
AddressLine3,
AddressLine4,
PostCode,
LocationID,
DataSourceTypeID
) xfr
--Join to the Entity Table to ensure we only
--add addresses for entities that already exist
INNER JOIN DataStore.dbo.Entity E
ON xfr.EntityID = E.EntityID
--Outer join to workinfo addresses to check if they already exist
LEFT OUTER JOIN DataStore.dbo.vAddressAsUniqueString wi
ON wi.EntityID=xfr.EntityID
AND wi.UniqueAddressString=xfr.UniqueAddressString
ANDwi.DataSourceTypeID=xfr.DataSourceTypeID
WHERE
wi.Entityid IS NULL --check address doesn't exist.
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Insert Address'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
--Now Insert the Address Type for each Address just inserted.
-- Note we need to join to the Address table twice for the outer join to work correctly
INSERT INTO DataStore.dbo.AddressAddressType
(
AddressTypeID,
AddressID,
DataSourceTypeID,
IsDeleted,
UpdatedDate,
CreatedDate,
CreatedBy,
UpdatedBy
)
SELECT DISTINCT
xfr.AddressTypeID,
a.AddressID,
xfr.DataSourceTypeID,
0,
xfr.UpdatedDate,
xfr.UpdatedDate,
FROM
(
SELECT
Entityid,
UniqueAddressString,
AddressTypeID,
DataSourceTypeID,
UpdatedDate=MAX(UpdatedDate)
FROM
Xtransfer.dbo.xfr_Address
WHERE
Entityid IS NOT NULL
AND AddressTypeID IS NOT NULL
GROUP BY
Entityid,
UniqueAddressString,
AddressTypeID,
DataSourceTypeID
) xfr
INNER JOIN DataStore.dbo.vAddressAsUniqueString a
ON xfr.EntityID = a.EntityID
AND xfr.UniqueAddressString=a.UniqueAddressString
AND xfr.DataSourceTypeID = a.DataSourceTypeID
LEFT OUTER JOIN DataStore.dbo.Address a1
ON a.AddressID = a1.AddressID
LEFT OUTER JOIN DataStore.dbo.AddressAddressType aat
ON a1.AddressID = aat.AddressID
AND xfr.AddressTypeID=aat.AddressTypeID
WHERE
aat.AddressID IS NULL --check the address type does not already exist
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Insert Address Address Types Part 1'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
--UPDATE the mail out addresses
--By default the ACC sourced addresses should be set to be 'mailout'.
--But, if there is a 'mailout' address already specified sourced from Workinfo, this should remain.
--
DELETE
FROM
DataStore.dbo.AddressAddressType
WHERE
AddressTypeID=10
AND DataSourceTypeID = 1 --@ACCSystemID
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Insert Address Address Types Part 2'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
--DataSourceTypeID: 1 ACC; 2 Legacy; 3 Work Info
INSERT INTO DataStore.dbo.AddressAddressType
(
AddressTypeID,
DataSourceTypeID,
AddressID,
UpdatedBy,
CreatedBy
)
SELECT DISTINCT
10,
1,
AddressID ,
FROM
DataStore.dbo.Address a
LEFT OUTER JOIN
(
--These entities already have a 'mail out' address type
SELECT DISTINCT
a.EntityID
FROM
DataStore.dbo.Address a
INNER JOIN DataStore.dbo.AddressAddressType aat
ON aat.AddressID=a.AddressID
WHERE
aat.AddressTypeID=10
--AND aat.IsDeleted=0
) ent
ON ent.EntityID=a.EntityID
WHERE
a.DataSourceTypeID = 1 --@ACCSystemID
AND ent.EntityID IS NULL
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Insert Address Address Types Part 3'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
---------------------------------------------------------------------------------------
--Update the party contact details with the correct addressid
---------------------------------------------------------------------------------------
UPDATE
DataStore..PartyContactDetail
SET
AddressID = na.AddressID,
--If the new address is deleted mark the party contact details as deleted too
IsDeleted = WIIsDeleted
FROM
--Find mismatched party contact details
(
SELECT DISTINCT
xa.EntityID,
xa.WIAddressID,
pc.PartyContactDetailID,
xa.UniqueAddressString,
xa.WIIsDeleted
FROM
--Only work on addresses that has been sucked down from Work Info
(select EntityID, UniqueAddressString, WIAddressID, WIIsDeleted from Xtransfer.dbo.xfr_Address where wiaddressid IS NOT NULL) xa
INNER JOIN DataStore..Address oa ON oa.addressID = xa.WIAddressID
INNER JOIN DataStore..PartyContactDetail pc ON pc.AddressID = oa.AddressID
INNER JOIN DataStore..Party p ON p.ElementID = pc.PartyElementID
WHERE
p.EntityID <> oa.EntityID
) xa
--Identify possible Addresses
INNER JOIN
(
SELECT DISTINCT
xa.EntityID,
ISNULL(na.Address1, '') + ISNULL(na.Address2, '') + ISNULL(na.Address3, '') + ISNULL(na.Address4, '') + ISNULL(CAST(na.LocationID AS VARCHAR(10)), '') + ISNULL(na.PostCode, '') UniqueAddressString,
MAX(na.AddressID) AddressID
FROM
(select EntityID, UniqueAddressString, WIAddressID, WIIsDeleted from Xtransfer.dbo.xfr_Address where wiaddressid IS NOT NULL) xa
INNER JOIN DataStore..Address na ON na.EntityID = xa.EntityID AND na.IsDeleted = 0
GROUP BY
xa.EntityID,
ISNULL(na.Address1, '') + ISNULL(na.Address2, '') + ISNULL(na.Address3, '') + ISNULL(na.Address4, '') + ISNULL(CAST(na.LocationID AS VARCHAR(10)), '') + ISNULL(na.PostCode, '')
) na on xa.EntityID = na.EntityID AND xa.UniqueAddressString = na.UniqueAddressString
INNER JOIN DataStore..PartyContactDetail pc ON pc.PartyContactDetailID = xa.PartyContactDetailID
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Update the party contact details'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
----------------------------------------------------------------------------------------
--Clear out duplicate addresses wich has no location
----------------------------------------------------------------------------------------
--If there are addresses with an undefined Location id, that also have an identical address
--to a record with a defined location, we mark them as Deleted.
DELETE AddressAddressType
FROM
DataStore.dbo.AddressAddressType AddressAddressType
INNER JOIN
(
SELECT DISTINCT addr.AddressID
FROM DataStore.dbo.address addr
INNER JOIN
(
SELECT DISTINCT EntityID, Address=ISNULL(address1,'') + ISNULL(address2,'') + ISNULL(address3,'') + ISNULL(address4,'') + ISNULL(PostCode,'')
FROM DataStore.dbo.address
WHERE LocationID IS NOT NULL AND IsDeleted=0
) AddressesWithLocationDefined ON AddressesWithLocationDefined.EntityID=addr.EntityID AND
AddressesWithLocationDefined.Address = ISNULL(addr.address1,'') + ISNULL(addr.address2,'') + ISNULL(addr.address3,'') + ISNULL(addr.address4,'') + ISNULL(addr.PostCode,'')
WHERE addr.LocationID IS NULL
) AddressesToDelete ON AddressesToDelete.AddressID=AddressAddressType.AddressID
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Delete Address Address Types with no Location'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
UPDATE DataStore.dbo.address
SET IsDeleted=1
FROM
DataStore.dbo.address addr
INNER JOIN
(
SELECT DISTINCT EntityID, Address=ISNULL(address1,'') + ISNULL(address2,'') + ISNULL(address3,'') + ISNULL(address4,'') + ISNULL(PostCode,'')
FROM DataStore.dbo.address
WHERE LocationID IS NOT NULL AND IsDeleted=0
) AddressesWithLocationDefined ON AddressesWithLocationDefined.EntityID=addr.EntityID AND
AddressesWithLocationDefined.Address = ISNULL(addr.address1,'') + ISNULL(addr.address2,'') + ISNULL(addr.address3,'') + ISNULL(addr.address4,'') + ISNULL(addr.PostCode,'')
WHERE
addr.LocationID IS NULL
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Delete Address with no Location'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
--Delete all but the last occurence of identical address rows
SELECT EntityID, UniqueAddressString, AddressID=MAX(AddressID)
INTO #AddressesToKeep
FROM DataStore.dbo.vwAddressDuplicates
GROUP BY EntityID, UniqueAddressString
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Find Address to keep with Location'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
--Note we may have had instances where 2 identical address rows were entered on purpose
--and given different address types, rather than select multiple address types for the
--one record. We therefore need to ensure we copy over all address types from the records
--we are deleting to the records we are keeping.
INSERT INTO DataStore.dbo.AddressAddressType(AddressTypeID, AddressID, DataSourceTypeID, UpdatedDate, CreatedDate, UpdatedBy, CreatedBy)
SELECT AllAddressTypes.AddressTypeID, AllAddressTypes.AddressID, AllAddressTypes.DataSourceTypeID, AllAddressTypes.UpdatedDate, AllAddressTypes.CreatedDate, AllAddressTypes.UpdatedBy, AllAddressTypes.CreatedBy
FROM
(
SELECT AddressTypeID, atk.AddressID, MIN(aat.DataSourceTypeID) DataSourceTypeID, MIN(aat.UpdatedDate) UpdatedDate, MIN(aat.CreatedDate) CreatedDate, MIN(aat.UpdatedBy) UpdatedBy, MIN(aat.CreatedBy) CreatedBy
FROM
DataStore.dbo.vwAddressDuplicates ad
INNER JOIN DataStore.dbo.AddressAddressType aat ON ad.AddressID=aat.AddressID
INNER JOIN #AddressesToKeep atk ON ad.UniqueAddressString=atk.UniqueAddressString AND ad.EntityID=atk.EntityID
WHERE aat.IsDeleted=0
GROUP BY AddressTypeID, atk.AddressID
) AllAddressTypes
--join out to check which ones already exist
LEFT OUTER JOIN DataStore.dbo.AddressAddressType aat ON aat.AddressTypeID=AllAddressTypes.AddressTypeID AND aat.AddressID=AllAddressTypes.AddressID
WHERE
--join out to check which ones already exist
aat.AddressID IS NULL
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Copy Address Types to Address to keep with Location'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
--Now we can delete the duplicate addresses and their address types
DELETE AddressAddressType
FROM
DataStore.dbo.AddressAddressType AddressAddressType
INNER JOIN
(
SELECT ad.AddressID
FROM
DataStore.dbo.vwAddressDuplicates ad
LEFT OUTER JOIN #AddressesToKeep atk ON ad.AddressID=atk.AddressID
WHERE atk.AddressID IS NULL
) AddressesToDelete ON AddressesToDelete.AddressID=AddressAddressType.AddressID
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Delete Duplicate Address Types from Address to delete'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
UPDATE DataStore.dbo.address
SET IsDeleted=1
FROM
DataStore.dbo.Address Address
INNER JOIN
(
SELECT ad.AddressID
FROM
DataStore.dbo.vwAddressDuplicates ad
LEFT OUTER JOIN #AddressesToKeep atk ON ad.AddressID=atk.AddressID
WHERE atk.AddressID IS NULL
) AddressesToDelete ON AddressesToDelete.AddressID=Address.AddressID
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Delete Address to delete'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
--Update addresses in PartyContactDetail to ensure they are not linked to deleted ones.
UPDATE DataStore.dbo.PartyContactDetail
SET AddressID=atk.AddressID
FROM
DataStore.dbo.PartyContactDetail pcd
INNER JOIN DataStore.dbo.vAddressAsUniqueString aus ON pcd.AddressID=aus.AddressID
INNER JOIN #AddressesToKeep atk ON aus.UniqueAddressString=atk.UniqueAddressStringAND aus.EntityID=atk.EntityID
WHERE pcd.AddressID<>atk.AddressID
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='Delete Duplicate Address Types from Address to delete'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
DUMP TRANSACTION Match WITH NO_Log
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='DUMP TRANSACTION Match WITH NO_Log'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
DUMP TRANSACTION DataStore WITH NO_Log
SELECT @ErrorNumber = @@ERROR, @Rows = @@RowCount, @MSG ='DUMP TRANSACTION DataStore WITH NO_Log'
EXEC dbo.xAuditInsert '',@@PROCID,@DebugLevel,@MSG,@Rows,@ErrorNumber
IF @ErrorNumber > 0 GOTO ErrorHandler
EXEC dbo.xAuditInsert '',@@PROCID,1,'Stop',0,@ErrorNumber
RETURN
ErrorHandler:
EXEC dbo.xAuditInsert '',@@PROCID,1,'Error Occurred - Transactions rolled back',0,@ErrorNumber
RETURN
May 6, 2008 at 5:59 am
I didn't read through everything, but you are issuing DDL commands within your procedure which will cause recompiles of the procedure while it executes. That may not be the cause, but it is certainly a possible cause for the blocking. Execution will wait while the recompile finishes. Why are you creating a permanent table and why issue it DDL commands after adding data to it?
ALTER TABLE Xtransfer.dbo.xfr_Address WITH NOCHECK ADD CONSTRAINT [PK_xfr_Address] PRIMARY KEY CLUSTERED ([ID]) ON [PRIMARY]
I think the RBAR king is going to come after this one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2008 at 1:16 pm
thanks for your reply.
Based on what has been indicated in this thread I'd already identified those areas as recompile causes. Was hoping that you might spot something else that was odd.
Its not my code - I was bought in to optimise the application when users complained of slow responses - did the normal things and late last week implemented some new indices (7) in total and new columns to gather statistics on and this batch job blew out by 6 hrs. So had to back everything out and now the batch job is back to normal.
I've identified a few things during the processing that might have contributed to the slow patches - but the thing is that its was slow at different parts of the nightly batch job, and not replicating the slow problem on the two nights following the new implementations.
Full text indexing is one.
There is I/O bottlenecks but we were going to handle those on the weekend but moving things around.
I noticed that during the processing after the backouts a lot of these spid blocking itself so went down this track.
Its an application by third parties so the code example is evolved over time and I don't know why they have chosen to do it this way.
I'm trying to identify the problem areas and try and get those changed, and this batch job is the crux of the application and it critical to the next day business.
I've tested this on the test db and it doesn't blow out! In fact last night it finished half an hour early. Can't see in the trace yet if there are recompiles - we'll see.
thanks for your help
May 6, 2008 at 6:14 pm
Well, at least it has some documentation to let you know what's going on... that's a start.
You already know the bad news... this code has joins of concatenated address columns and a bunch of other SQL no-no's that make it just impossible for any kind of indexing to really help. You also have the added caveat of other code in the form of views... bet they would benefit from the same rewrite the rest of the code needs.
Any idea how many rows this processes overnight?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply