1

I have I problematic query on my SQL Server where I could not find the problem. I have two simple tables, one for the "Headers" and one for the "Items" (each with an PK column, total of five fields and some of the FKs).

My client application does the following things:

  1. Open a SQL Server transaction
  2. Insert a header row (1-5 msec)
  3. Insert first item row (with PK value of header row as FK - 5000-20000ms!)
  4. Insert second item row (1-5 msec)
  5. Insert n-th item row (1-5 msec)
  6. Commit
  7. Close transaction

Header table has around 2000 entries, Item table around 6000. No additional indexes defined, no triggers. It's a test system, so there's not much other load/locks. I updated the statistics.

The tables at issue are declared as follows:


INSERT INTO MachineParameterDataItems (
  PlantID
, MachineParameterDataHeaderID
, MachineParameterSetConfigID
, Value
)
VALUES (
  @P0
, @P1
, @P2
, CASE WHEN @P3 = -1 THEN NULL ELSE @P4 END
)

Header table:


/****** Object:  Table [dbo].[MachineParameterDataHeaders]    Script Date: 01.04.2019 11:26:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MachineParameterDataHeaders](
    [MachineParameterDataHeaderID] [int] IDENTITY(1,1) NOT NULL,
    [PlantID] [smallint] NOT NULL,
    [MachineParameterSetID] [int] NOT NULL,
    [BatchID] [int] NULL,
    [CreationDateTime] [datetime2](7) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [MachineParameterDataHeaderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MachineParameterDataHeaders]  WITH CHECK ADD  CONSTRAINT [FK__MachinePa__Batch__116A8EFB] FOREIGN KEY([BatchID])
REFERENCES [dbo].[Batches] ([BatchID])
GO

ALTER TABLE [dbo].[MachineParameterDataHeaders] CHECK CONSTRAINT [FK__MachinePa__Batch__116A8EFB]
GO

ALTER TABLE [dbo].[MachineParameterDataHeaders]  WITH CHECK ADD  CONSTRAINT [FK__MachinePa__Plant__0F824689] FOREIGN KEY([PlantID])
REFERENCES [dbo].[Plants] ([PlantID])
GO

ALTER TABLE [dbo].[MachineParameterDataHeaders] CHECK CONSTRAINT [FK__MachinePa__Plant__0F824689]
GO

ALTER TABLE [dbo].[MachineParameterDataHeaders]  WITH CHECK ADD  CONSTRAINT [FK__MachineParameterDataHeaders_MachineParameterSets] FOREIGN KEY([MachineParameterSetID])
REFERENCES [dbo].[MachineParameterSets] ([MachineParameterSetID])
GO

ALTER TABLE [dbo].[MachineParameterDataHeaders] CHECK CONSTRAINT [FK__MachineParameterDataHeaders_MachineParameterSets]
GO

Item table:


/****** Object:  Table [dbo].[MachineParameterDataItems]    Script Date: 01.04.2019 11:24:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MachineParameterDataItems](
    [MachineParameterDataItemID] [int] IDENTITY(1,1) NOT NULL,
    [PlantID] [smallint] NOT NULL,
    [MachineParameterDataHeaderID] [int] NOT NULL,
    [Value] [decimal](18, 6) NULL,
    [MachineParameterSetConfigID] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [MachineParameterDataItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MachineParameterDataItems]  WITH CHECK ADD  CONSTRAINT [FK__MachinePa__Machi__19FFD4FC] FOREIGN KEY([MachineParameterDataHeaderID])
REFERENCES [dbo].[MachineParameterDataHeaders] ([MachineParameterDataHeaderID])
GO

ALTER TABLE [dbo].[MachineParameterDataItems] CHECK CONSTRAINT [FK__MachinePa__Machi__19FFD4FC]
GO

ALTER TABLE [dbo].[MachineParameterDataItems]  WITH CHECK ADD  CONSTRAINT [FK__MachinePa__Plant__190BB0C3] FOREIGN KEY([PlantID])
REFERENCES [dbo].[Plants] ([PlantID])
GO

ALTER TABLE [dbo].[MachineParameterDataItems] CHECK CONSTRAINT [FK__MachinePa__Plant__190BB0C3]
GO

ALTER TABLE [dbo].[MachineParameterDataItems]  WITH CHECK ADD  CONSTRAINT [FK_MachineParameterDataItems_MachineParameterSetConfigs] FOREIGN KEY([MachineParameterSetConfigID])
REFERENCES [dbo].[MachineParameterSetConfigs] ([MachineParameterSetConfigID])
GO

ALTER TABLE [dbo].[MachineParameterDataItems] CHECK CONSTRAINT [FK_MachineParameterDataItems_MachineParameterSetConfigs]
GO

And here's the relevant execution plan.

Does anyone have a clue what the problem could be?

  • 1
    During the 5-20 seconds in step 3, what is happening? Does sys.dm_exec_requests show the session as blocked? What is the wait type? – Aaron Bertrand Apr 1 '19 at 11:07
  • Is your client running multiple times? A second client could be blocked by the first in this case. – Ben Ootjers Apr 1 '19 at 12:09
  • Yes, sys.dm_exec_requests shows a lock (LCK_M_S). – GxB Apr 2 '19 at 15:45
  • If I run the query posted below on the blocking session ID, I see that it's blocked by the INSERT of the header entry. After one or two clueless minutes, I found the reason: the client application didn't do the INSERT of the header in the same SQL transaction. I don't know how this worked (as it shouldn't be committed), but using the same transaction solved the problem. Thanks Aaron! DECLARE @sqltext VARBINARY(128) SELECT @sqltext = sql_handle FROM sys.sysprocesses WHERE spid = 82 SELECT TEXT FROM sys.dm_exec_sql_text(@sqltext) GO – GxB Apr 2 '19 at 15:54
1

The INSERT of the header row was not in the SQL transaction, so the INSERT of the first item row was locked by that. I don't know if there is some kind of auto-commit after some time, but adding the header INSERT to the SQL transaction solved the problem.

|improve this answer|||||
-1

How are the variables that are being used in the insert getting generated? Is this code being executed via stored procedure? If so then I would check for parameter sniffing, since the insert ranges from 5-20 seconds.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.