SQL Server slow in simple insert statement

Question

Anyone have came across this problem? A simple insert statement took 7 seconds, and this table contain 300 row of records. There no trigger on this table.

Table:

CREATE TABLE [dbo].[Table]
(
    [intMsgIn] [int] IDENTITY(1,1) NOT NULL,
    [charType] [char](4) NOT NULL,
    [dtTrx] [datetime] NOT NULL,
    [varMsg] [varchar](4000) NULL,

    CONSTRAINT [PK_Tbl] PRIMARY KEY CLUSTERED ([intMsgIn] ASC)
        WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
              IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
              ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

SQL statement:

INSERT INTO Table (charType, dtTrx, varMsg)  
VALUES(@charType, getdate(), @varMsg)  

SELECT @@IDENTITY

This SQL statement was code in the store procedure and then is call by C# application.

Any possible reason that cause this slow?

What else is the DB doing? Are there queries running that could potentially lock the table? — Dave Bish, Jul 03 '15 at 15:53
I would recommend to use **`SCOPE_IDENTITY()`** instead of anything else (like `@@IDENTITY`) to grab the newly inserted identity value. [See this blog post for an explanation as to WHY](http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/) — marc_s, Jul 03 '15 at 15:55
On a side note, don't use `@@IDENTITY`, use `SCOPE_IDENTITY()`. — Kaf, Jul 03 '15 at 15:55
Why use "table" for the table name? That is asking for SQL trouble. Anyway, no reason from what you've put there, more likely to be other issues with locking, disk io, something in C# affecting the DB etc. — Lukos, Jul 03 '15 at 15:57
How are you calling it from C#? Can you post the code you're using? — E.Z. Hart, Jul 03 '15 at 15:58
Check if there are any locks on that table. There's a very good stored procedure that can help you with that: http://stackoverflow.com/questions/8749426/how-to-find-what-is-locking-my-tables — Francisco Goldenstein, Jul 03 '15 at 15:59

score 0 · Answer 1 · answered Jul 04 '15 at 23:14

Just a simple hint. Try it again and take a look at sp_who2 N'ACTIVE'. It should show you all active running queries. You'll have a column called BlkBy.

As your query is pretty simple, I'm sure there should be any kind of lock. The column BlkBy will give you the SPID of the task which is blocking your's. In this case you can recheck if there are concurrent INSERT, UPDATE or DELETE-Statements which generate another lock.

If this would be to fast to test with sp_who2, you can also setup a trace and check the results if there is any lock during your INSERT.

SQL Server slow in simple insert statement

1 Answers1