July 24, 2006 at 11:39 am
Does anyone have any information, factual or anecdotal, on the performance impacts, if any, of encrypting stored procedures.
Obviously, performance tuning using Index Wizard is useless if the majority of db access is thru encrypted procedures....but that is the only side effect I have seen.
Gordon
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
July 25, 2006 at 3:25 am
Encrypting a SP only affects the SP definition, not the actual SP. Which means it works exactly as before with the notable exception that you cannot view the 'text' that created the encrypted SP.
Hanslindgren
July 25, 2006 at 4:13 am
It's also largely pointless - just check how many routines are available to decrypt them. You're better removing permissions from syscomments which will deter browsers of your code. There's never been an encrypted proc I haven't been able to decrypt - yet!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 25, 2006 at 8:46 am
I would think that the first time the proc is run, and any future time that it has been paged out of the cache, there will be more overhead for SQL to parse it, since it must decrypt it first. This should be minor overhead unless the server is already CPU constrained and/or the procs are especially large.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 28, 2006 at 9:41 am
Oh one other point, you can't run profiler for tuning against encrypted procs effectively or use showplan with QA effectively.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 10, 2006 at 11:54 am
Thanks for the responses.
By the way, the encryption was NOT my idea....they are vendor supplied procedures which the vendor has encrypted. (Not that it has stopped me from reading the procs! ).
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply