November 11, 2011 at 2:31 am
I have a situation at the moment with deadlocks which have just started happening with a very minor change to a stored proc.
Inside the proc, the first thing it does is insert a row into a table with getdate()
CREATE TABLE ncdba.logtimes
(
ID INT IDENTITY,
StartTime DATETIME,
EndTime DATETIME,
Type CHAR(2)
)
1st part of proc
DECLARE @ID INT
INSERT INTO ncdba.logtimes VALUES (Getdate(),null,'CA')
SET @ID = @@IDENTITY
It then goes and does the main bit of the proc and returns the results, then the last part is to set the EndTime where ID = @ID
UPDATE ncdba.logtimes SET EndTime = Getdate() WHERE ID = @ID
All was good until yesterday where we added a new parameter to the proc and changed the underlying main table with a new column
Now we are experiencing deadlocks on the UPDATE statement
I tried putting in ROWLOCK and UPDLOCK into the UPDATE statement, which seemed to have solved it for a few minutes atleast until I got another error in the errorlog
UPDATE ncdba.logtimes WITH(UPDLOCK, ROWLOCK) SET EndTime = Getdate() WHERE ID = @ID
Now I am stumped, the only thing I can think of is that for some reason @@IDENTITY is giving the same ID to the variable if it is run simultainiously.
Any ideas guys
This is the trace dump
deadlock-list
deadlock victim=process72f2bc8
process-list
process id=process72f2bc8 taskpriority=0 logused=0 waitresource=OBJECT: 5:1183759720:0 waittime=3044 ownerId=6286382509 transactionname=UPDATE lasttranstarted=2011-11-11T09:03:28.753 XDES=0x13ca743b0 lockMode=X schedulerid=3 kpid=2552 status=suspended spid=90 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-11-11T09:03:28.620 lastbatchcompleted=2011-11-11T09:03:28.620 clientapp=web1.domain.com hostname=378024-WEB1 hostpid=5392 loginname=domain-website isolationlevel=serializable (4) xactid=6286382509 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=domain.search.VehicleSimpleDynamic line=262 stmtstart=27316 sqlhandle=0x030005002affb96632b89000989f00000100000000000000
update ncdba.logtimes WITH (UPDLOCK, ROWLOCK) set endtime = getdate() where id = @id
inputbuf
Proc [Database Id = 5 Object Id = 1723465514]
process id=process72f3b88 taskpriority=0 logused=0 waitresource=OBJECT: 5:1183759720:0 waittime=3044 ownerId=6286382576 transactionname=UPDATE lasttranstarted=2011-11-11T09:03:28.757 XDES=0x1499fa3b0 lockMode=X schedulerid=3 kpid=4680 status=suspended spid=85 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-11-11T09:03:28.183 lastbatchcompleted=2011-11-11T09:03:28.183 clientapp=web1.domain.com hostname=378025-WEB2 hostpid=5340 loginname=domain-website isolationlevel=serializable (4) xactid=6286382576 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=domain.search.VehicleSimpleDynamic line=262 stmtstart=27316 sqlhandle=0x030005002affb96632b89000989f00000100000000000000
update logtimes WITH (UPDLOCK, ROWLOCK) set endtime = getdate() where id = @id
inputbuf
Proc [Database Id = 5 Object Id = 1723465514]
resource-list
objectlock lockPartition=0 objid=1183759720 subresource=FULL dbid=5 objectname=domain.ncdba.LogTimes id=lock1df620400 mode=IX associatedObjectId=1183759720
owner-list
owner id=process72f3b88 mode=IX
waiter-list
waiter id=process72f2bc8 mode=X requestType=convert
objectlock lockPartition=0 objid=1183759720 subresource=FULL dbid=5 objectname=domain.ncdba.LogTimes id=lock1df620400 mode=IX associatedObjectId=1183759720
owner-list
owner id=process72f2bc8 mode=IX
waiter-list
waiter id=process72f3b88 mode=X requestType=convert
November 11, 2011 at 2:36 am
The deadlock is not on the table that you're hinting and updating (logtimes), so no, you don't have an identity error.
The deadlock comes from object-level (table locks) on domain.ncdba.LogSearchTimes. Is there a trigger on LogTimes? Are there any indexes on LogSearchTimes?
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 11, 2011 at 2:52 am
there is no trigger and the table was a heap until I noticed that there was no clustered index, which I created around 20 minutes ago on the ID column as a primary key clustered
edit
sorry I should of said that LogTimes and LogSearchTimes are the same object, I thought I had changed that in the error log extract
November 11, 2011 at 2:55 am
So how does the LogSearchTimes table some into this. That's clearly the table that's getting the deadlocks and, it's table-level locks.
Edit: never mind, your queries have the wrong table name specified. From the deadlock graph it's
update logsearchtimes WITH (UPDLOCK, ROWLOCK) set endtime = getdate() where id = @id
Both processes take an IX (intent exclusive) on the table (they're compatible with other IX locks) then both try to convert to exclusive, which causes the deadlock
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 11, 2011 at 2:56 am
Sorry Gail, I just edited the above post, I thought I had changed the object in the error log extract to LogTimes
LogTimes and LogSearchTimes are the same object
November 11, 2011 at 3:00 am
One thing to note: The rowlock hint just tells SQL to start with row locks. If it decides that it needs to escalate, it will escalate, and you very clearly have a request for a full table-level exclusive lock here.
Can you post the rest of domain.search.VehicleSimpleDynamic please? Also clarify what the minor changes were.
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 11, 2011 at 3:02 am
p.s. Any particular reason you've chosen serialisable isolation level here?
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 11, 2011 at 3:10 am
USE [Netcars]
GO
/****** Object: StoredProcedure [search].[VehicleSimpleDynamic] Script Date: 11/11/2011 10:03:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [search].[VehicleSimpleDynamic](@CapManId int = NULL, @CapRangeId int = NULL, @MinPrice money = NULL, @MaxPrice money = NULL, @PostCodeArea nvarchar(4) = NULL, @Distance int = NULL, @RowNumberStart int = NULL, @RowNumberEnd int = NULL, @SortOrder int = NULL, @DealerId int = NULL, @DealerGroupId int = NULL, @DealerStatusId int = NULL, @VehicleStatusId int = NULL, @IsMarkdown BIT = 1)
with recompile
AS
/**
Uses dynamic sql and parameters to return query results for simple search
**/
SET NOCOUNT ON;
/**
Procedure: VehicleSimpleDynamic
Description: Uses dynamic sql and parameters to return query results for simple search
Modified: 18/01/2011 by Michael Binks.
Notes: Now also searches on DealerId
Modified: 23/02/2011 by Michael Binks.
Notes: result set includes RedirectVehicleUrl - value taken from - precedence is on DealerGroup table
Modified: 02/03/2011 by Michael Binks.
Notes: added the tariff id and thumbnail images to the result set
Modified: 21/03/2011 by Edafe Onerhime
Notes: added merged body style
Modified: 10/05/2011 by Michael Binks.
Notes: added the thumbnail image path to be used by the toolbar
Modified: 01/06/2011 by Michael Binks.
Notes: Search table now includes DealerStatusId 2 (live) and 15 (click limit) We need this because the dealer template
websites also query this table and we dont want these sites emptying of cars when the click limit is reached
Modified: 10/06/2011 by Michael Binks.
Notes: Search table now stores DealerStatusId -1 for private sales.
Updated the query to select on -1 and therefore return private sales
Modified: 28/06/2011 by Andrew Moorcroft.
Notes: Search table now stores VehicleStatusId 1 (enabled) and 2 (disabled) to hide certain cars from search
Updated the query to select by VehicleStatusId and hide cars from search
Modified: 03/10/2011 by Michael Binks.
Notes: Added dealer group id to filters by all dealers in a group
Query based on dynamic SQL
**/
DECLARE @sql nvarchar(MAX)
,@paramlist nvarchar(4000)
,@sortsql nvarchar(MAX)
,@id bigint;
insert into ncdba.logtimes (type, starttime) values ('CB',getdate())
select @id = @@IDENTITY
/** Get sort order **/
IF @SortOrder IS NULL
BEGIN
SET @SortOrder = 1;
END
SELECT @sortsql = [ColumnName] + ' ' + [SortOrder]
FROM search.sortColumns
WHERE [Id] = @sortOrder
-- Build the parameter list
set @paramlist = N'@p_CapManId int'; -- Manufacturer
set @paramlist = @paramlist + N', @p_CapRangeId int'; -- Range
set @paramlist = @paramlist + N', @p_MinPrice money'; -- Min Price
set @paramlist = @paramlist + N', @p_MaxPrice money'; -- Max Price
set @paramlist = @paramlist + N', @p_PostCodeArea nvarchar(4)'; -- Buyer's postcode
set @paramlist = @paramlist + N', @p_Distance int'; -- Distance between buyer's and seller's postcode
set @paramlist = @paramlist + N', @p_DealerId int'; -- DealerId
set @paramlist = @paramlist + N', @p_DealerGroupId int'; -- DealerGroupId
set @paramlist = @paramlist + N', @p_DealerStatusId int'; -- DealerStatusId
set @paramlist = @paramlist + N', @p_VehicleStatusId int'; -- VehicleStatusId
set @paramlist = @paramlist + N', @p_RowNumberStart int'; -- Start paging number
set @paramlist = @paramlist + N', @p_RowNumberEnd int'; -- End paging number
set @paramlist = @paramlist + N', @p_IsMarkdown bit'; -- Will make revenue
-- Build the query string
-- The paging block
set @sql = N'SET NOCOUNT ON;'
set @sql = N' WITH VehiclePaging AS (';
set @sql = @sql + N' SELECT ROW_NUMBER() OVER (Order By ' + @sortsql + N') AS RowNumber'; -- Generate unique Id
set @sql = @sql + N', COUNT(*) OVER() AS TotalRecords'; -- Count all records
-- The general SELECT block
set @sql = @sql + N', [search].[SearchVehicle].[VehicleId]';
set @sql = @sql + N', [search].[SearchVehicle].[ListMainImagePath]';
set @sql = @sql + N', [dbo].[CapMan].[cman_name] AS Manufacturer';
set @sql = @sql + N', [dbo].[CapRange].[cran_name] AS RangeName';
set @sql = @sql + N', [search].[SearchVehicle].[Trim]';
set @sql = @sql + N', [search].[SearchVehicle].[YearOfManufacture]';
set @sql = @sql + N', coalesce(nvd_bs.[Description], cbs_bs.[Description], ''Unknown'') AS BodyStyle'; -- Modified for merged bodystyle
set @sql = @sql + N', [search].[SearchTransmission].[Description] AS Transmission';
set @sql = @sql + N', [search].[SearchVehicle].[Colour]';
set @sql = @sql + N', [search].[SearchFuelType].[Description] AS Fuel';
set @sql = @sql + N', [search].[SearchVehicle].[Options]';
set @sql = @sql + N', [search].[SearchVehicle].[Price]';
set @sql = @sql + N', [search].[SearchVehicle].[Mileage]';
set @sql = @sql + N', [dbo].[Dealer].[DealerId] AS DealerId';
set @sql = @sql + N', ISNULL(CASE WHEN ISNULL(dbo.Dealer.UseOverrideDealerName,0) = 1 THEN OverrideDealerName ELSE DealerName END,''Private Seller'') AS DealerName';
set @sql = @sql + N', [dbo].[Dealer].[IsApprovedDealer] AS DealerIsApprovedDealer';
set @sql = @sql + N', [dbo].[Dealer].[AllowDealerClick] AS DealerAllowDealerClick';
set @sql = @sql + N', [dbo].[Upload].[UploadGuid] AS DealerImage';
set @sql = @sql + N', dbo.[Upload].[SubFolder]';
set @sql = @sql + N', dbo.[UploadFileType].[FileTypeExtension]';
set @sql = @sql + N', [search].[SearchVehicle].[NumberOfImages]';
set @sql = @sql + N', [search].[SearchVehicle].[YoutubeVideoUrl]';
set @sql = @sql + N', CASE WHEN dbo.DealerGroup.RedirectVehicleClick IS NOT NULL THEN dbo.DealerGroup.RedirectVehicleClick ELSE dbo.Dealer.RedirectVehicleClick END AS DealerRedirectVehicleClick';
set @sql = @sql + N', [search].[SearchVehicle].[RedirectVehicleUrl]';
set @sql = @sql + N', ISNULL([dbo].[DealerGroup].[TariffId], [dbo].[Dealer].[TariffId]) AS DealerTariff';
set @sql = @sql + N', [search].[SearchVehicle].[ThumbnailImagePath1]';
set @sql = @sql + N', [search].[SearchVehicle].[ThumbnailImagePath2]';
set @sql = @sql + N', [search].[SearchVehicle].[ThumbnailImagePath3]';
set @sql = @sql + N', [search].[SearchVehicle].[ToolbarThumbnailImagePath]';
set @sql = @sql + N', [search].[SearchVehicle].[RackspaceCdnContainerUrl]';
IF @PostCodeArea > ''
BEGIN
set @sql = @sql + N', [dbo].[PostcodeDistrictDistance].[Distance]';
END
ELSE
BEGIN
set @sql = @sql + N', NULL AS [Distance]';
END
-- The general FROM block
set @sql = @sql + N' FROM [search].[SearchVehicle] WITH (NOLOCK)';
set @sql = @sql + N' INNER JOIN dbo.CapFuelType ON [search].[SearchVehicle].[cft_code_int] = dbo.CapFuelType.cft_code_int';
SET @sql = @sql + N' INNER JOIN dbo.CapTransmission ON [search].[SearchVehicle].[ct_code_int] = dbo.CapTransmission.ct_code_int';
SET @sql = @sql + N' INNER JOIN [search].[SearchCapManMapping] ON [search].[SearchVehicle].[cman_code] = [search].[SearchCapManMapping].[cman_code]';
SET @sql = @sql + N' INNER JOIN [dbo].[CapMan] ON [search].[SearchCapManMapping].[CapManId] = [dbo].[CapMan].[cman_code]';
SET @sql = @sql + N' INNER JOIN [search].[SearchCapRangeMapping] ON [search].[SearchVehicle].[cran_code] = [search].[SearchCapRangeMapping].[cran_code]';
SET @sql = @sql + N' INNER JOIN [dbo].[CapRange] ON [search].[SearchCapRangeMapping].[CapRangeId] = [dbo].[CapRange].[cran_code]';
SET @sql = @sql + N' INNER JOIN[search].[SearchFuelMapping] ON [dbo].[CapFuelType].[cft_code_int] = [search].[SearchFuelMapping].[cft_code_int]';
SET @sql = @sql + N' INNER JOIN[search].[SearchFuelType] ON [search].[SearchFuelMapping].[Id] = [search].[SearchFuelType].[Id]';
SET @sql = @sql + N' INNER JOIN [search].[SearchTransmissionMapping] ON [dbo].[CapTransmission].[ct_code_int] = [search].[SearchTransmissionMapping].[ct_code_int]';
SET @sql = @sql + N' INNER JOIN [search].[SearchTransmission] ON [search].[SearchTransmissionMapping].[Id] = [search].[SearchTransmission].[Id]';
SET @sql = @sql + N' LEFT OUTER JOIN search.SearchMergedBodyStyleMapping cbs_bsm ON search.SearchVehicle.cbs_code_int = cbs_bsm.cbs_code_int';
SET @sql = @sql + N' LEFT OUTER JOIN search.SearchMergedBodyStyleMapping nvd_bsm ON search.SearchVehicle.bs_code = nvd_bsm.bs_code';
SET @sql = @sql + N' LEFT OUTER JOIN search.SearchMergedBodyStyle cbs_bs ON cbs_bsm.SearchMergedBodyStyleId = cbs_bs.Id';
SET @sql = @sql + N' LEFT OUTER JOIN search.SearchMergedBodyStyle nvd_bs ON nvd_bsm.SearchMergedBodyStyleId = nvd_bs.Id';
SET @sql = @sql + N' LEFT OUTER JOIN dbo.[Dealer] ON [search].[SearchVehicle].[DealerId] = dbo.[Dealer].DealerId';
SET @sql = @sql + N' LEFT OUTER JOIN dbo.[DealerGroup] ON dbo.[Dealer].[DealerGroupId] = dbo.[DealerGroup].DealerGroupId';
SET @sql = @sql + N' LEFT OUTER JOIN dbo.[Upload] ON dbo.[Upload].UploadId = dbo.[Dealer].LogoUploadId';
SET @sql = @sql + N' LEFT OUTER JOIN dbo.[UploadFileType] ON dbo.[Upload].UploadFileTypeId = dbo.[UploadFileType].UploadFileTypeId';
SET @sql = @sql + N' LEFT OUTER JOIN dbo.NetCarsUser ON [search].[SearchVehicle].[NetCarsUserId] = dbo.NetCarsUser.NetCarsUserId';
IF @PostCodeArea > ''
BEGIN
SET @sql = @sql + N' LEFT OUTER JOIN dbo.Address ON dbo.Dealer.SiteAddressId = dbo.Address.AddressId';
SET @sql = @sql + N' INNER JOIN dbo.PostcodeDistrictDistance ON dbo.PostcodeDistrictDistance.PostcodeDistrictFrom = @p_PostCodeArea AND dbo.PostcodeDistrictDistance.PostcodeDistrictTo = ISNULL(dbo.Address.PostcodeArea, dbo.NetcarsUser.PostcodeArea)';
END
-- Where cause goes here.
set @sql = @sql + N' WHERE 1 = 1'; -- Allows us to add where conditions with an and and has no imact apart from looking ugly!
-- Only search for the make if the model is not provided
IF @CapManId IS NOT NULL AND @CapRangeId IS NULL
BEGIN
set @sql = @sql + N' AND [search].[SearchCapManMapping].[CapManId] = @p_CapManId';
END
-- Only search for the model if the make + model are not provided
IF @CapRangeId IS NOT NULL
BEGIN
set @sql = @sql + N' AND [search].[SearchCapRangeMapping].[CapRangeId] = @p_CapRangeId';
END
-- Add search for price
IF @MinPrice IS NOT NULL AND @MaxPrice IS NOT NULL -- sets price between min and max
BEGIN
set @sql = @sql + N' AND [search].[SearchVehicle].[Price] between @p_MinPrice and @p_MaxPrice';
END
IF @MinPrice IS NOT NULL AND @MaxPrice IS NULL -- sets price greater than min
BEGIN
set @sql = @sql + N' AND [search].[SearchVehicle].[Price] > @p_MinPrice';
END
IF @MaxPrice IS NOT NULL AND @MinPrice IS NULL -- price between min and max would already be set above, set less than max only
BEGIN
set @sql = @sql + N' AND [search].[SearchVehicle].[Price] < @p_MaxPrice';
end
-- Restrict by distance
IF @PostCodeArea > '' AND @distance IS NOT NULL
BEGIN
set @sql = @sql + N' AND [dbo].[PostcodeDistrictDistance].Distance <= @p_Distance';
END
-- Restrict by dealerId
IF @DealerId IS NOT NULL
BEGIN
set @sql = @sql + N' AND [search].[SearchVehicle].[DealerId] = @p_DealerId';
END
-- Restrict by dealerGroupId
IF @DealerGroupId IS NOT NULL
BEGIN
set @sql = @sql + N' AND [search].[SearchVehicle].[DealerId] in (
SELECT DealerId
FROM Dealer
WHERE DealerGroupId = @p_DealerGroupId
AND IsDeleted = 0
AND DealerStatusId = 2)';
END
-- Restrict by dealerStatusId
IF @DealerStatusId IS NOT NULL
BEGIN
set @sql = @sql + N' AND [search].[SearchVehicle].[DealerStatusId] IN (@p_DealerStatusId, -1) '; -- minus 1 is for private sales
END
-- Restrict by vehicleStatusId
IF @VehicleStatusId IS NOT NULL
BEGIN
set @sql = @sql + N' AND [search].[SearchVehicle].[VehicleStatusId] = @p_VehicleStatusId';
END
IF @IsMarkdown IS NOT NULL
BEGIN
set @sql = @sql + N' AND [search].[SearchVehicle].[IsMarkdown] = @p_IsMarkdown';
END
set @sql = @sql + N'SELECT [RowNumber]'; -- Generate unique Id
set @sql = @sql + N', [TotalRecords]';
set @sql = @sql + N', [VehicleId]';
set @sql = @sql + N', [ListMainImagePath]';
set @sql = @sql + N', [Manufacturer]';
set @sql = @sql + N', [RangeName]';
set @sql = @sql + N', [Trim]';
set @sql = @sql + N', [YearOfManufacture]';
set @sql = @sql + N', [BodyStyle]';
set @sql = @sql + N', [Transmission]';
set @sql = @sql + N', [Colour]';
set @sql = @sql + N', [Fuel]';
set @sql = @sql + N', [Options]';
set @sql = @sql + N', [Price]';
set @sql = @sql + N', [Mileage]';
set @sql = @sql + N', [DealerId]';
set @sql = @sql + N', [DealerName]';
set @sql = @sql + N', [DealerIsApprovedDealer]';
set @sql = @sql + N', [DealerAllowDealerClick]';
set @sql = @sql + N', [DealerImage]';
set @sql = @sql + N', [SubFolder]';
set @sql = @sql + N', [FileTypeExtension]';
set @sql = @sql + N', [NumberOfImages]';
set @sql = @sql + N', [YoutubeVideoUrl]';
set @sql = @sql + N', [Distance]';
set @sql = @sql + N', [DealerRedirectVehicleClick]';
set @sql = @sql + N', [RedirectVehicleUrl]';
set @sql = @sql + N', [DealerTariff]';
set @sql = @sql + N', [ThumbnailImagePath1]';
set @sql = @sql + N', [ThumbnailImagePath2]';
set @sql = @sql + N', [ThumbnailImagePath3]';
set @sql = @sql + N', [ToolbarThumbnailImagePath]';
set @sql = @sql + N', [RackspaceCdnContainerUrl]';
set @sql = @sql + N' FROM VehiclePaging';
-- Return paged results only
IF @RowNumberStart IS NOT NULL AND @RowNumberEnd IS NOT NULL
BEGIN
set @sql = @sql + N' WHERE RowNumber BETWEEN @p_RowNumberStart AND @p_RowNumberEnd';
END
--print @sql;
--print @paramlist;
EXEC sp_executesql @sql, @paramlist, @CapManId, @CapRangeId, @MinPrice, @MaxPrice, @PostCodeArea, @distance, @DealerId, @DealerGroupId, @DealerStatusId, @VehicleStatusId, @RowNumberStart, @RowNumberEnd, @IsMarkdown;
update ncdba.logtimes WITH (UPDLOCK, ROWLOCK) set endtime = getdate() where id = @id
The change is on search.searchvehicle a new column of IsMarkdown BIT was added
The proc was given a new parameter IsMarkdown BIT = 0
A new line of IF @IsMarkdown IS NOT NULL .............. was added to enforce using the new column and parameter
The isolation level will have been set in the website where the proc is called from, the DB and the proc itself is read-committed, but the web overrides it.
November 11, 2011 at 3:26 am
Can you run this and post the execution plan (it'll rollback so no permanent changes are made)
declare @id int
select @id = max(id) from ncdba.logtimes
begin transaction
update ncdba.logtimes WITH (UPDLOCK, ROWLOCK) set endtime = getdate() where id = @id
rollback transaction
p.s. There's no point in a UPDLock hint on an update. That's what it'll take by default to start.
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 11, 2011 at 3:32 am
I have dropped the primary key constraint which I created after investigating the table around an hour ago and then readded the primary key both execution plans are uploaded.
Have to say since the creation of the PK not had a deadlock, we where getting atleast 1 every 30 minutes.
November 11, 2011 at 3:44 am
Without the pk, you were getting table scans, which aren't pretty for updates and concurrency. The pk should fix this completely.
btw, just something I noticed. What's the reason for with recompile on the proc? With nearly all the code in dynamic SQL, it doesn't really seem to make sense to recompile the procedure on every execution, the only query it has in it is this update.
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 11, 2011 at 4:00 am
Think that has come from the company not having a DBA until two months ago when I started and myself for not completly understanding dynamic sql and execution plans, I left it in as the parameters could be left blank or given a value which affects which joins and where clauses to build into the @sql parameter, which then will generate a plan, but if the dynamic sql plan is flushed from procedure cach on execution of the proc I will remove it
November 11, 2011 at 4:10 am
anthony.green (11/11/2011)
but if the dynamic sql plan is flushed from procedure cach on execution of the proc I will remove it
No. Just the procedure's plan (which has just about nothing in it).
Each piece of dynamic SQL will have its own plan, matching on the text of the statement.
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 11, 2011 at 4:17 am
Thanks Gail
I will remove the recompile from the proc, thats another thing I have learned today.
November 11, 2011 at 12:11 pm
Try to use scope_identity() instead of @@IDENTITY. scope_identity() is session wide. @@IDENTITY is global and it may be source of your problems.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply