Slow performing insert with values

[NilsBevaart]

I have a table with very straight forward fields. To populate this table, I have the following stored procedure:

ALTER PROCEDURE proc_AddPostBack
@PostBackID UNIQUEIDENTIFIER,
@UserID UNIQUEIDENTIFIER,
@UserIP CHAR(15),
@PBControlID VARCHAR(255),
@BillingMetadataKey CHAR(32) = NULL
AS
INSERT INTO tblPostBack (
PostBackID,
UserID,
UserIP,
PostBackControl,
PBCreateDate,
BillingMetadataKey)
VALUES (
@PostBackID,
@UserID,
@UserIP,
@PBControlID,
GETUTCDATE(),
@BillingMetadataKey)

The table has only a primary key on PostBackID (clustered) and it's statistics are up to date and the index is defragmented.
Often, this stored procedure takes minutes to run. After this, all consecutive runs are processed in 0 seconds.

How come sometimes this is so slow? I tried moving the database to a new server. There are NO other processes or databases running on the server. Our monitoring tool doesn't show any disk, memory or cpu use.

Any help is appriciated




Nils Bevaart

 

[SQLDenis]

How about recompilation of the sp?

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[NilsBevaart]

If I simulate this with

exec sp_recompile proc_AddPostBack
go
exec proc_AddPostBack ....

it still executes in 0 seconds. If recompiling causes this error, I would expect this to take longer

Nils Bevaart

 

[SQLDenis]

Is everything else slowing down also
What about your cache hit ratio
Are there locks on THIS table periodically so that your SP has to 'wait' for the other process to release these locks
I would suggest running profiler for a couple of hours and analyzing the output




“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/