SQL Server Performance Forum – Threads Archive

Recompile-Procedure

Hi CREATE PROC MonitorUserActivity
AS
SET NOCOUNT ON WHILE 0 = 0
BEGIN WAITFOR DELAY ’00:00:10′
EXEC RefreshUserActivity END
the procedure is already running. i want to modify the delay to 20 seconds. can i do that without affecting current runnin procedure.it shoud not comedown . can anyone assist?

Alter the procedure by dropping and recreating and while running it will not have any effect, and try to create the SP WITH RECOMPILE option in order to take the new settings on cache. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya you meant to say i can do this without bringing down the current running procedure ?
can you pls pls tel me the steps involved?

Satya, see follwing procedure . *Kill 9 *Then soon alter with recompile. *restart the procedure. I think this is the only solution .
can you pls tel me diff between alter without recompile and withcompile. i checkd BOL ,didnt help Rajiv
SQL-DBA

I don’t think it will take more than 2 seconds to alter the SP and as you’re running the waitdelay for 10 seconds the new procedure will take affect on the next run. If you want to enhance the SP execution then its bettre to accept some downtime of process, rather than worrying about other things. BOL specifies about WITH RECOMPILE:
Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Use of this option is uncommon, and causes the stored procedure to execute more slowly because the stored procedure must be recompiled each time it is executed.
In your case I suggest to alter the SP and recompile that SP using
EXEC sp_recompile titles
statement.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

this process is workin with spid9 . KILL 9 any problem using this command to stop process? Satya please advice.I will b doin this today . Rajiv
SQL-DBA

No issues as I can see. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

AS spid 9 is is background process i dont think we can kill it.
my proces is configured to execute while startup.So its runnin as background with spid 9 . any methord to bring thsi process down ? its urgent
Rajiv
SQL-DBA

I believe if you’re running this process as a job then stop the job and run teh alter procedure.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

not as a job. CREATE PROC MonitorUserActivity
AS
SET NOCOUNT ON WHILE 0 = 0
BEGIN WAITFOR DELAY ’00:00:10′
EXEC RefreshUserActivity END
this proc is executing seperatly, not as a job
Rajiv
SQL-DBA

Then I don’t see other alternative other than using KILL statement for that SPID. BTW why do you want kill the SPID, just alter the procedure and recompile to take the new settings affected on next execution. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

i checked that now.
1)alter proce MonitorUserActivity
2)sp_recompile MonitorUserActivity
procedure text is changing.But execution is same only .First it need to comeout of the current execution no.as its a infinite while loop it wil never comout .So recompiling willnot take effect util i kill it and resatart. what you say Satya . Rajiv
SQL-DBA

Run DBCC FREEPROCCACHE to clear the cache and the new settings will take affect on next execution. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

i tried DBCC FREEPROCCACHE also no use. excecution is not stopin as its an infinite loop .so clearin cache has no effect . Rajiv

Satya i killed tht process. restarted it from OSQL .Now its al set .

]]>