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]>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 > >
