Recompile is not creating new execution plan also its not solving the issues.Only after I run update stats,execution time comes back to normal.

Thanks for such a quick response.I don't see any locking or blocking during the issue.I also compared execution plan both before and after and its looks exactly same.

Thanks for quick response Kevin.Nolock is not an option for us as its not acceptable from business perspective.

Thanks John.

SR

You can use following stored procedure.You can refer books online for more information.

sp_add_job [ @job_name = ] 'job_name'

    [ , [ @enabled = ] enabled ]

    [ , [ @description...

Try something like this and let me know if it works.

USE MyDB

GO

SET IDENTITY_INSERT PrivateDocuments ON

GO

Insert into PrivateDocuments(columna,columnb)

Try this.

SET NOCOUNT ON

DECLARE @iFileExists INT

create table #table (File_exists int, File_directory int,parent_dir int)

insert into #table EXEC master..xp_fileexist 'D:\test.txt'

select @iFileExists=File_exists from #table

PRINT @iFileExists

drop table #table

Thanks,

SR

Thanks for response Bulent.

Even I taught so but one of my peer argues that it will improve performance if we have 2 subscribers on same server on same database and...

Log file looks good Bose.

Thanks,

SR

Thanks Andrew.I will check it in sqlteam.com.

I turned on trace but I didn't find anything unusual.

Thanks,

SR

Hi Andrew,

Is Parameter sniffing is applicable for functions also?

Thanks,

Sree

Thanks for your response Murphy.Its not a stored procedure its a user defined function.I have seen the excution plan.Its almost same in both QA and Prod server.

I will look into Parameter...

Thanks for nice explanation Gail.

Thanks,

Sree

Thanks Aaron.

Thanks for response Gail.How about if I do truncate table which is a non-logged transaction?

Thanks,

SR