Agreed. A deadlock graph will show you the resources held and requested for each process involved.
It'd also be interesting to consider the whole NOLOCK, and dynamic TVF names that are being used here, but that's a discussion for another day : Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: <http://www.sqldownunder.com/> www.sqldownunder.com From: [email protected] [mailto:[email protected]] On Behalf Of Tony Wright Sent: Tuesday, 19 March 2013 3:28 PM To: ozDotNet Subject: Re: Transaction (Process ID 65) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. I use SQL Profiler to investigate deadlocks. See this article: Analyse Deadlocks with SQL Server Profiler. http://msdn.microsoft.com/en-us/library/ms188246.aspx On Tue, Mar 19, 2013 at 3:14 PM, Corneliu I. Tusnea <[email protected] <mailto:[email protected]> > wrote: Hi, I have a SQL Procedure that call a SQL Function that (way too often) dies with the above message. The SP looks like this ALTER PROCEDURE [dbo].[Reportxxxx] @FileId BIGINT , @Id BIGINT , @FunctionToExecute NVARCHAR(200) AS BEGIN DECLARE @UtcTime DATETIME SET @UtcTime = GETUTCDATE() DECLARE @ToUpdate TABLE(ReferenceId BIGINT, ReferenceLastUpdateTime DATETIME, RelatedReferenceId BIGINT, RelatedReferenceTypeId INT) DECLARE @sql NVARCHAR(MAX) SET @sql = N'SELECT * FROM ' + @FunctionToExecute + CONVERT(VARCHAR(20), @FileId) + N', ' + CONVERT(VARCHAR(20), @Id) + N')' INSERT INTO @ToUpdate EXEC sp_executesql @statement = @sql -- the crash is always in the EXEC line. The @FunctionToExecute looks like this: CREATE FUNCTION [dbo].[Validate_xxxx] ( @FileId BIGINT , @Id BIGINT ) RETURNS @Results TABLE(ReferenceId BIGINT, ReferenceLastUpdateTime DATETIME, RelatedReferenceId BIGINT, RelatedReferenceTypeId INT) AS BEGIN INSERT INTO @Results(ReferenceId, ReferenceLastUpdateTime, RelatedReferenceId, RelatedReferenceTypeId) SELECT DISTINCT T.Id, T.LastUpdateTime, T.ContactId, 1 FROM dbo.Data T WITH(NOLOCK) WHERE T.FileId = @FileId AND T.ContactId IS NULL RETURN END GO (I've removed various bits to keep it simple. Basically the function has a quite straight forward SELECT with a NOLOCK and the SP has an insert in a table variable. At the moment I have a failure rate of about 6 out of 10 and the failure. I'm running this on SQL2012 Standard (with which I'm having quite some hard time as it runs some queries much slower than my old SQL2008R2 on much lower hardware specs). Any ideas or clues in how to diagnose and fix this? Thanks, Corneliu
