SP recompilation due to a set command

• Roust_m

  SSCoach

  Points: 17410

  More actions

  January 21, 2007 at 6:33 pm

  #115415

  Hi,

  I have an SP which does one recompile every time it runs due to [SET DATEFORMAT  DMY] command.  Although this command is not listed as one causing recompiles in this KB:

  http://support.microsoft.com/kb/308737/

  I tried to remove the command and this allowed to get rid of the recompile.

  Just wondering is there a way to check the setting and in case it is correct avoid running the command reducing the total number of recompiles?

  Thanks.

   

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  January 21, 2007 at 9:11 pm

  #684591

  Not listed?  It's listed as "Set Option Changed in Batch" and "Set Option Changed".  And they kinda tell you how to build a test... create a proc with theoption you want to test, turn on the SQL Profiler, run the test code, look for the SPLRecompile event, and compare the numeric return value with those on the list at the URL you provided.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Roust_m

  SSCoach

  Points: 17410

  More actions

  January 21, 2007 at 9:42 pm

  #684595

  Sorry, I put the wrong URL above, it should be this one:

  http://support.microsoft.com/kb/243586/

  It has a list of options which cause the recompiles below.  Anyway the main point of the question was how to avoid the recompile having the need to run this command.

  The following table lists some common SET statements and whether or not changing the SET statement in a stored procedure causes a recompile:

  Set Statement	Recompile
  Set quoted_identifier	No
  Set arithabort	Yes
  Set ansi_null_dflt_on	Yes
  Set ansi_defaults	Yes
  Set ansi_warnings	Yes
  Set ansi_padding	Yes
  Set concat_null_yields_null	Yes
  Set numeric_roundabort	No
  Set nocount	No
  Set rowcount	No
  Set xact_abort	No
  Set implicit_transactions	No
  Set arithignore	No
  Set lock_timeout	No
  Set fmtonly	No

• Vladan

  SSC-Insane

  Points: 22072

  More actions

  January 22, 2007 at 4:07 am

  #684642

  Hi,

  as I see - at least in your post - some statement s are listed with Yes and some with No for recompliation. SET DATEFORMAT is not in the list at all, so it doesn't say anything - neither Yes nor No.

  As to your problem, I'd suggest to change the code to avoid the necessity of setting dateformat, if possible. In most cases it is possible. What exactly is your problem in this procedure?

  An example of code independent on settings - always returns day number as if Monday=1 (not my own idea, originally posted by Sergiy):

  SET datefirst 1

  SELECT (@@Datefirst + DATEPART(WEEKDAY, getdate())  -2 )%7 + 1

  SET datefirst 3

  SELECT (@@Datefirst + DATEPART(WEEKDAY, getdate())  -2 )%7 + 1

  You see that in both cases result is the same, which means you don't have to bother with setting dateformat.