sql - Stored Procedure to update table incredibly slow. Why? -

i have simple stored procedure takes bunch of parameters update existing record within table. creating , deleting record other stored procs takes little time execute. however, executing update stored proc takes 60 seconds , can't fathom why.

i've tried proposals article, without luck: sql server: query fast, slow procedure

here original sp:

create procedure [dbo].sp_update         @ownerid uniqueidentifier,         @dealid uniqueidentifier,         @title nvarchar(250),         @description nvarchar(max),         @projectvalue money,         @country nvarchar(250),         @countryregion nvarchar(max),         @worldregion nvarchar(250),         @marketsector nvarchar(250),         @projectstage nvarchar(250),         @creationdate datetime,         @expirydate datetime,         @imagefilepath nvarchar(max),         @isactive bit,         @isdeleted bit  begin      update sometable set      ownerid = @ownerid,      title = @title,     description = @description,      projectvalue = @projectvalue,      country = @country,      countryregion = @countryregion,      worldregion = @worldregion,      marketsector = @marketsector,      projectstage = @projectstage,     creationdate = @creationdate,      expirydate = @expirydate,      imagefilepath = @imagefilepath,      isactive = @isactive,     isdeleted = @isdeleted      dealid = @dealid end 

after trying of suggestions in above article, 1 one, ended this:

create procedure [dbo].sp_update2         @ownerid uniqueidentifier,         @dealid uniqueidentifier,         @title nvarchar(250),         @description nvarchar(max),         @projectvalue money,         @country nvarchar(250),         @countryregion nvarchar(max),         @worldregion nvarchar(250),         @marketsector nvarchar(250),         @projectstage nvarchar(250),         @creationdate datetime,         @expirydate datetime,         @imagefilepath nvarchar(max),         @isactive bit,         @isdeleted bit  recompile begin         set quoted_identifier off         set ansi_nulls on          declare @tempownerid uniqueidentifier         declare @tempdealid uniqueidentifier         declare @temptitle nvarchar(250)         declare @tempdescription nvarchar(max)         declare @tempprojectvalue money         declare @tempcountry nvarchar(250)         declare @tempcountryregion nvarchar(max)         declare @tempworldregion nvarchar(250)         declare @tempmarketsector nvarchar(250)         declare @tempprojectstage nvarchar(250)         declare @tempcreationdate datetime         declare @tempexpirydate datetime         declare @tempimagefilepath nvarchar(max)         declare @tempisactive bit         declare @tempisdeleted bit          set @tempownerid = @ownerid         set @tempdealid = @dealid         set @temptitle = @title         set @tempdescription = @description         set @tempprojectvalue = @projectvalue         set @tempcountry = @country         set @tempcountryregion = @countryregion         set @tempworldregion = @worldregion         set @tempmarketsector = @marketsector         set @tempprojectstage = @projectstage         set @tempcreationdate = @creationdate         set @tempexpirydate = @expirydate         set @tempimagefilepath = @imagefilepath         set @tempisactive = @isactive         set @tempisdeleted = @isdeleted       update sometable set      ownerid = @tempownerid,      title = @temptitle,     description = @tempdescription,      projectvalue = @tempprojectvalue,      country = @tempcountry,      countryregion = @tempcountryregion,      worldregion = @tempworldregion,      marketsector = @tempmarketsector,      projectstage = @tempprojectstage,     creationdate = @tempcreationdate,      expirydate = @tempexpirydate,      imagefilepath = @tempimagefilepath,      isactive = @tempisactive,     isdeleted = @tempisdeleted      dealid = @tempdealid      set quoted_identifier off     set ansi_nulls on  end 

note funny positioning ansi_nulls , quoted_identifier inside proc create instead of alter because of visual studio 2015's inability cope commands in ssms.

i'm @ loss how speed , don't want go down route of turning manually concatenated string of params can accept characters render query unsafe (i.e. single quotes, etc)

i'm calling execution of stored proc via dapper in c# .net, if makes difference.

any ideas?

update: here's table definition current indexes:

create table [dbo].[sometable] (     [dealid]        uniqueidentifier not null,     [ownerid]       uniqueidentifier not null,     [title]         nvarchar (250)   not null,     [description]   nvarchar (max)   not null,     [projectvalue]  money            not null,     [projectstage]  nvarchar (250)   not null,     [country]       nvarchar (250)   not null,     [countryregion] nvarchar (max)   null,     [worldregion]   nvarchar (250)   not null,     [marketsector]  nvarchar (250)   not null,     [imagefilepath] nvarchar (max)   null,     [creationdate]  datetime         not null,     [expirydate]    datetime         not null,     [isdeleted]     bit              default ((0)) not null,     [isactive]      bit              default ((0)) not null,     primary key clustered ([dealid] asc) );   go create nonclustered index [deal_owners]     on [dbo].[sometable]([ownerid] asc);   go create nonclustered index [deal_ids]     on [dbo].[sometable]([dealid] asc); 

assuming dealid unique -- update 1 row every time, wouldn't think parameter sniffing problem, usual suspect when works fast somewhere , elsewhere.

• the field dealid indexed , first (or only) column in index?

• have checked there no blocking causes procedure run slow?

• have looked @ actual plan of execution, if there strange happening, instead of index seek + key lookup.

ansi_nulls or quoted_identifier don't affect plan that. people think affect plan it's plan re-use , different plan gets created when session options don't match.