0

I have a stored procedure in MS SQL Server that uses a Sybase linked server. Each time the stored procedure is executed it runs for a long time and looks like it is recompiling before execution. Any idea how to stop this recompilation?

Kara
  • 6,115
  • 16
  • 50
  • 57
user2296151
  • 1
  • 1
    'Looks like' sounds like the recompilation is a theory opposed to being known to be true. What evidence have you gathered that it is recompilation so far opposed to just a bad query performance. – Andrew Apr 18 '13 at 17:13
  • The stored procedure takes about 2 hours to compile. The first query within the stored procedure runs in about 2 minutes when run independently. However, when the stored procedure is run, the same query does not run until after two hours have passed. – user2296151 Apr 19 '13 at 01:56
  • That doesn't sound like a compilation cost, since compilation has cut offs on how much time it is willing to spend compiling before just choosing a good enough plan. – Andrew Apr 19 '13 at 09:16
  • Then why do you think the procedure is running for 2 hours just before it executes its 1st statement? Is it because of the SET ? there are few SET statements before the 1st query.(like SET @date =getdate() etc. Does this cause any problem? I even am not sure why the procedure takes 2 hours for compilation? Is it because of the db-links used within the procedure? – user2296151 Apr 19 '13 at 09:33
  • Well you could use option recompile on the query to check if 2nd run with a recompilation also then takes 2 hours, that would at least confirm it one way or another. – Andrew Apr 19 '13 at 13:06
  • Have you looked at some of the other Linked Server performance issue posts? http://stackoverflow.com/questions/440944/sql-server-query-fast-but-slow-from-procedure http://stackoverflow.com/questions/132305/linked-server-performance-and-options http://dba.stackexchange.com/questions/11127/sql-performance-issues-with-remote-query-across-linked-server – Mike Gardner Apr 19 '13 at 13:16
  • Does the Stored procedure change its query plan if the data it is processing changes each time it executes? – user2296151 May 08 '13 at 09:58

1 Answers1

0

its hard to believe that recompilation taking this much time. I suspects this procedure using some old plan to execute which is not optimized.

few points to check.

  1. excute with recompile option. which make new query plan.
  2. check which query inside the proc taking long time. if MDA tables installed check in mosSysStatement. or check in master..sysprocesses table while running that stored proc. it would show statement number which is currently running. it would give you fair idea which statement taking.
  3. run update statistics on tables used by stored proc.

Thanks, Gopal

Gopal Sanodiya
  • 106
  • 3