Does SET DATEFIRST cause recompilation of a procedure?

Transact-SQL
1

Hi!

According to the book Microsoft SQL Server 2012 internals (Delaney, Freeman) SET DATEFIRST causes a recompile. I cannot find information in Books online about this.

Maybe I did something wrong but I wasn't able to reproduce this behaviour. Does anyone of you know whether this is really the case?

With best regards,
Henrik Svensson

2

I have no reason to doubt something that Kalen Delaney has written - she is one of the most respected and knowledgeable people with regards to SQL Server Internals.

I did an experiment, and that seems to validate what she is saying. I created a simple stored procedure like shown below:

CREATE PROC dbo.TestDate
AS
	select 1;
GO

Then, executed it twice as shown below (after setting datefirst to 5 in each case before running).

-- case 1:
dbcc freeproccache
GO
exec dbo.TestDate
GO
set datefirst 4;
GO
exec dbo.TestDate
GO

Looking at the trace, you can see that there are two Cache Inserts. See screenshot below

Then, running the second test case where I don't change the datefirst setting

dbcc freeproccache
GO
exec dbo.TestDate
GO
--set datefirst 4;
GO
exec dbo.TestDate
GO

This shows only one Cache Insert, which leads me to believe that in this case the plan was reused.

So if what she says and what I am observing is true, then what happens when you have a set datefirst statement within a stored procedure? :smile:

1 Like
3

I would think it would recompile. Think about it: the results of any DATEPART(WEEKDAY, ...) would be different; as would any code that used @@DATEFIRST in some type of calc; etc.?. Seems to me rather than try to check for each possible function that might return a different result, it'd be best for SQL to recompile everything after that which could be affected just in case it was affected by the date setting changing.

1 Like
4

Given that changing @@DATEFIRST impacts only the session in which the change is made, invalidating the entire process cache seems like a high price to pay.

Or is it that the compiled version of a stored procedure has the DATEFIRST setting that was used to generate the query plan included with it, and the plan is invalidated only if the session that invokes the stored procedure has a different DATEFIRST setting?

Intuitively, I would have thought that DATEFIRST setting would affect ad-hoc queries in that session, but would not propagate to the code within stored procedures that are invoked from that session.

By extension, the question I was asking in my original reply - if I were to have a SET DATEFIRST statement in a stored procedure, does that mean that every time I run the stored procedure, the entire process cache is invalidated, or at the very least, any other stored procedure that is subsequently run in that session will be recompiled?

1 Like
5

Never mind about my question about setting DATEFIRST within a stored procedure invalidating the entire process cache. It seems like the behavior of DATEFIRST is sort of like the scope of temporary tables. See the example below:

create procedure dbo.Test
as
set datefirst  3;
GO

select @@datefirst;
exec dbo.Test;
select @@datefirst;

This produces the following output, indicating that the change in DATEFIRST within the stored procedure has not affected the calling session.

1 Like
6

Hi!

Thank you for the replies. I guess it is safe to say I did something wrong in my initial tests. The following code seems to prove the recompilations, as far as I can tell:

CREATE PROC testRecompile1 AS SET DATEFIRST 1 SELECT @@DATEFIRST AS testRecompile1 EXEC testRecompile2; GO

CREATE PROC testRecompile2
AS
SET DATEFIRST 2
SELECT @@DATEFIRST AS testRecompile2
GO

EXEC testRecompile2
EXEC testRecompile1

...since it resulted in the following events caught by SQL Server Profiler:

With best regards,
Henrik Svensson