Most Efficient Configuration Flag in SProc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Most Efficient Configuration Flag in SProc

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-11-30 : 05:45:29

I want to have a "Flag" in an Sproc to control whether it does A or B. This will be a global setting in the application. What is the lowest-cost means of implementing it?

In my particular case I want to be able to turn logging off at peak busy times.

I have a number of choices:


CREATE PROCEDURE usp_LogMe
    @Param1 ...
    @IsLoggingOn bit = 0
...

To turn it On or Off we have to edit the code, set the appropriate default value for @IsLoggingOn, and run the Sproc. Risk that DBA uses the wrong version of the code ... I would prefer the Sproc was self-contained.

There is no intention that the @IsLoggingOn is passed by the caller - we would have to modify all our code for that, but a caller could deliberately pass "0" so that it forced its entry to be logged - its just convenient to put the Definition at the top to make it easy to change. I could make @IsLoggingOn an OUTPUT so that the caller could determine if logging was on, or off, if it needed to.

We could add


SELECT @IsLoggingOn = SomeConfigColumn
FROM MyConfigTable
WHERE MyConfigPK = 1234

but this is going to add a READ to every execution - and the objective is to reduce (to close to zero) what the Sproc does when Logging is turned Off.

Is there some cheaper flag I can use? A user defined global system value for example?

Or an alternative method?

Thanks

Kristen
Test

22859 Posts

Posted - 2010-11-30 : 06:06:07

CONTEXT_INFO() was what was in my mind. That's session-specific I think, anything more global than that?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-11-30 : 07:31:12

Just alter the logging proc every time you want it on/off. flip-flop between 2 states. 2 roll-out scripts??

v1.
alter loggingproc with recompile
log the action, variables etc
return

v2.
alter logging_proc with recompile
do nothing
return

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-30 : 08:30:10

Haha, that was a lot smarter...why didn't I just do that in a project I was on not too long ago?? I'm so stupid.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Kristen
Test

22859 Posts

Posted - 2010-11-30 : 11:49:32

"Just alter the logging proc every time you want it on/off. flip-flop between 2 states. 2 roll-out scripts??"

Yeah, a rollout script would do, didn't think of that.

Unfortunately there are probably 20 sprocs doing various variations on "logging", and they also contain other code which will still be needed (so there are flavours like "Log this and get me the Current UserID" which centralises the logic for "Get me the current UserID")

I was hoping to provide Admin with a nice page where they could throw a switch to turn this on / off - I ain't letting them run a SQL rollout script!

Kristen
Test

22859 Posts

Posted - 2010-11-30 : 11:55:12

P.S. I don't want "WITH RECOMPILE" do I? This Sproc needs to be fast, so I want the Plan to be cached - or am I missing something?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-12-01 : 06:10:19

After an update don't you need the existing SP in cache to be cleared? Agree that it shouldn't run with RECOMPLILE for every execution.

Kristen
Test

22859 Posts

Posted - 2010-12-01 : 09:53:40

"After an update don't you need the existing SP in cache to be cleared? "

I thought re-creating it did that. I have a vague recollection that in SQL2000 (or maybe SQL 7) that using ALTER did not clear cache, whereas DROP and CREATE did - but I also thought that those "bugs" had been fixed in later/current versions

If not I need to use sp_Recompile on a few things pronto!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-12-01 : 11:59:03

No clear recollection myself. Don't have to do it generally.

Test???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources