Compilations

Question

Compilations

msimone

SSCommitted

Points: 1846
More actions
May 13, 2014 at 3:52 am

#291316

Hello.
I have a problem with compilations due to "set options changed". The value is very high, 3 requests per compilation.
I don't have "sets" in the procedures and i don't know what's happening.
Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

The ANSI connection settings can be changed by the connecting application. That's where I'd focus.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

msimone SSCommitted Points: 1846 More actions · Answer 2

Thank you very much for your answer.

I shall look that.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Pretty common scenario actually, and some very nasty things can happen as a result of SET differences (much worse than recompilations). Check the settings the sprocs were compiled under and compare to the default (and explicit SETs) of application connection layer.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

msimone SSCommitted Points: 1846 More actions · Answer 4

I traced using eventclass 14 (audit logon) and 17 (existing connections). I didn't see changes in the set options.

any idea?

Thanks for all.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Look in BOL for @@OPTIONS to see a list of the different values.

sys.dm_exec_plan_attributes ( plan_handle ) has user options object was compiled with.

sys.dm_exec_requests has a number of bits representing various set options. So does sys.dm_exec_sessions.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

msimone SSCommitted Points: 1846 More actions · Answer 6

Hello.

Plan's set options and the set values in the connections (event audit logon) are the same.

Instalation: SQL 2012 SP1 Standart Edition version 11.0.3128.0 with pool connection.

In the procs we use set rowcount. Microsoft says this set option not cause recompiles but i detected that a change in the set rowcount recompiled the proc.

Is it correct?

Thanks.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

msimone (5/27/2014)
Hello.
Plan's set options and the set values in the connections (event audit logon) are the same.
Instalation: SQL 2012 SP1 Standart Edition version 11.0.3128.0 with pool connection.
In the procs we use set rowcount. Microsoft says this set option not cause recompiles but i detected that a change in the set rowcount recompiled the proc.
Is it correct?
Thanks.

This should be easy enough to test. Just make two identical sprocs but with one of them having the SET ROWCOUNT and the other not. Then execute them both from the same engine (SSMS for example) where they both will have the same execution SET options. If profiler says the SET ROWCOUNT one is doing a recompile each time and the other does not then you have your proof.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

msimone SSCommitted Points: 1846 More actions · Answer 8

Hello.

I think that it is very important.

Changes in SET ROWCOUNT cause RECOMPILES in 2012 and 2014. In 2008 and 2008-R2 not.

Why? I don't know, but the profiler doesn't lie. Without changes between executions, no problem, but a change in the value causes recompile with eventsubclass 4. Always.

Thanks.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 9

SET ROWCOUNT has always been slower than TOP for me. Further, it's been deprecated for a very long time (since SS 2005, IIRC). Here's the deprecation notice in the current version of BOL, which may be found at http://msdn.microsoft.com/en-us/library/ms188774.aspx.

Important
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax. For more information, see TOP (Transact-SQL).

My recommendation is, especially since you're now having difficulty with it, that now would be a good time to convert your code to use TOP.

--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)