Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an insert statement that is being generated by the .NET Entity Framework. In most cases, this particular insert will execute in 0ms according SQL Server Profiler. One out of every 30 or so inserts will jump to as high as 12 seconds duration, causing the .NET client on the other end to show as "Not Responding" while it waits. Server load should not be an issue as our server is very, very lightly loaded.

Here is the table the insert is being performed against:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ProductEvents](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[KID] [int] NOT NULL,
[EventDescription] [varchar](50) NOT NULL,
[EventDate] [datetime] NOT NULL,
[UserName] [varchar](50) NOT NULL,
[Notes] [varchar](max) NOT NULL,
[Version] [timestamp] NOT NULL,
[IsSynchronized] [bit] NOT NULL,
[LastSyncDate] [datetime] NULL,
 CONSTRAINT [PK_ProductEvents] PRIMARY KEY CLUSTERED 
([EventID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ProductEvents] ADD  CONSTRAINT [DF_ProductEvents_IsSychronized]
DEFAULT ((0)) FOR [IsSynchronized]
GO

ALTER TABLE [dbo].[ProductEvents]  WITH CHECK ADD  CONSTRAINT
[FK_ProductEvents_Products] FOREIGN KEY([KID])
REFERENCES [dbo].[Products] ([KID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ProductEvents] CHECK CONSTRAINT [FK_ProductEvents_Products]
GO

And the query as seen by SQL Server Profiler (actual example):

exec sp_executesql N'insert [dbo].[ProductEvents]([KID], [EventDescription],
[EventDate], [UserName], [Notes], [IsSynchronized], [LastSyncDate])
values (@0, @1, @2, @3, @4, @5, null)
select [EventID], [Version]
from [dbo].[ProductEvents]
where @@ROWCOUNT > 0 and [EventID] = scope_identity()',N'@0 int,@1 varchar(50),@2 
datetime2(7),@3 varchar(50),@4 varchar(max) ,@5 
bit',@0=1894,@1='Modified',@2='2013-08-12 
08:09:25.4766233',@3='KNEXTION\aellison',
@4='Description changed from Mini Awareness Ribbon Cookie Cutter - RM 1698 to Mini 
Awareness Ribbon Cookie Cutter - R&M 1698.',@5=0

I will also be glad to post the Query Plan, but I'm not sure in what format I should post it (I'm mainly a StackOverflow guy).

EDIT: Here is the execution plan as a screenshot from SSMS.

Execution Plan

Any ideas on how to begin to track this down?

share|improve this question

closed as too broad by Paul White, RolandoMySQLDBA, Mark Storey-Smith, StanleyJohns, dezso Aug 15 '13 at 7:53

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Is this a multi-user application? Could be other sessions are causing blocking. Check dba.stackexchange.com/questions/34313/… for more info. –  Max Vernon Aug 12 '13 at 16:05
1  
Do you have actual (not estimated) query plans from both a fast and slow incident? Do they match? If they do, then I suspect Max is correct about blocking (or other external factors). If they don't, then we can see what changed. You can post the .sqlplan files just about anywhere, or if you want to use SQL Sentry Plan Explorer, you can post plans directly to answers.sqlperformance.com. –  Aaron Bertrand Aug 12 '13 at 16:27
1  
You need to link externally. But if the plans are the same, there's no point, because they can't explain anything (well, aside from general inefficiencies that should apply to all executions, not just the slow instances). You'll need to monitor sys.dm_exec_requests when it is running slowly to see who/what it's waiting on. –  Aaron Bertrand Aug 12 '13 at 17:45
1  
Well you can also monitor wait statistics (it could be severely bottlenecked I/O, for example). Again, reproducing in Plan Explorer Pro will help here, as you can automatically get the wait statistics for your session (unless you fire up your own extended events session, you can only get system-wide waits on your own). –  Aaron Bertrand Aug 12 '13 at 17:53
2  
Also check auto-growth events and settings for the database and log. Occasional long delays can be caused by having to wait for a data or log file to grow. –  Paul White Aug 13 '13 at 4:40
show 4 more comments

Browse other questions tagged or ask your own question.