Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
affect sp runtime performance?
Does sql server encrypt the actual sp, query plans, etc... or does it
just not show you the sp contents when you try and modify the sp or
profile it?
It just doesn't show it to you. It's actually obfuscated, not really
encrypted...
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"bendlam" <bendlam@.gmail.com> wrote in message
news:1186683048.146983.90170@.x35g2000prf.googlegro ups.com...
> Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
> affect sp runtime performance?
> Does sql server encrypt the actual sp, query plans, etc... or does it
> just not show you the sp contents when you try and modify the sp or
> profile it?
>
|||On Aug 9, 2:26 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> It just doesn't show it to you. It's actuallyobfuscated, not really
> encrypted...
> --
> Adam MachanicSQLServerMVP -http://sqlblog.com
> Author, "ExpertSQLServer2005Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> "bendlam" <bend...@.gmail.com> wrote in message
> news:1186683048.146983.90170@.x35g2000prf.googlegro ups.com...
>
>
> - Show quoted text -
So does this affect performance?
|||> So does this affect performance?
Did you try it?
|||On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Did you try it?
Yes I did. Didn't look like there was much of a performance
difference. What I'm trying to understand is how obfuscating the code
affects performance. i.e. Is there more work for sql server to read
the code since its obfuscated? There is little to no documentation
on this in BOL.
|||The encrypted stored procedure text needs to be decrypted each time it
is compiled or recompiled. Since compiling a batch of statements is a
relatively expensive operation, I would expect the performance
difference to be unnoticable.
HTH,
Gert-Jan
bendlam wrote:
> On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
> Yes I did. Didn't look like there was much of a performance
> difference. What I'm trying to understand is how obfuscating the code
> affects performance. i.e. Is there more work for sql server to read
> the code since its obfuscated? There is little to no documentation
> on this in BOL.
Showing posts with label encrypting. Show all posts
Showing posts with label encrypting. Show all posts
Tuesday, February 14, 2012
does using WITH ENCRYPTION for sps affect performance?
Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
affect sp runtime performance?
Does sql server encrypt the actual sp, query plans, etc... or does it
just not show you the sp contents when you try and modify the sp or
profile it?It just doesn't show it to you. It's actually obfuscated, not really
encrypted...
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"bendlam" <bendlam@.gmail.com> wrote in message
news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
> Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
> affect sp runtime performance?
> Does sql server encrypt the actual sp, query plans, etc... or does it
> just not show you the sp contents when you try and modify the sp or
> profile it?
>|||On Aug 9, 2:26 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> It just doesn't show it to you. It's actuallyobfuscated, not really
> encrypted...
> --
> Adam MachanicSQLServerMVP -http://sqlblog.com
> Author, "ExpertSQLServer2005Development"http://www.apress.com/book/bookDis
play.html?bID=10220
> "bendlam" <bend...@.gmail.com> wrote in message
> news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
>
>
>
> - Show quoted text -
So does this affect performance?|||> So does this affect performance?
Did you try it?|||On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Did you try it?
Yes I did. Didn't look like there was much of a performance
difference. What I'm trying to understand is how obfuscating the code
affects performance. i.e. Is there more work for sql server to read
the code since its obfuscated? There is little to no documentation
on this in BOL.|||The encrypted stored procedure text needs to be decrypted each time it
is compiled or recompiled. Since compiling a batch of statements is a
relatively expensive operation, I would expect the performance
difference to be unnoticable.
HTH,
Gert-Jan
bendlam wrote:
> On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
> Yes I did. Didn't look like there was much of a performance
> difference. What I'm trying to understand is how obfuscating the code
> affects performance. i.e. Is there more work for sql server to read
> the code since its obfuscated? There is little to no documentation
> on this in BOL.|||On Aug 13, 1:45 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> The encrypted stored procedure text needs to be decrypted each time it
> is compiled or recompiled. Since compiling a batch of statements is a
> relatively expensive operation, I would expect the performance
> difference to be unnoticable.
> HTH,
> Gert-Jan
>
> bendlam wrote:
>
>
>
> - Show quoted text -
just to clarify,
Did you mean compiling a batch of statements is a relatively
"inexpensive" or "expensive" operation? in which case you'd expect
performance difference would be unnoticable.
I'm assuming you meant inexpensive, and that "WITH ENCRYPTION" doesn't
affect a sql servers ability to cache its query plans to avoid
excessive recompilations, correct?
Thanks for your response.
ben|||> just to clarify,
> Did you mean compiling a batch of statements is a relatively
> "inexpensive" or "expensive" operation? in which case you'd expect
> performance difference would be unnoticable.
I think what Gert-Jan meant was, the decryption operation is such a small
part of the work that it would be negligible overall.
A|||"Aaron Bertrand [SQL Server MVP]" wrote:
>
> I think what Gert-Jan meant was, the decryption operation is such a small
> part of the work that it would be negligible overall.
> A
Exactly. Compilation is a relatively expansive operation, but decryption
is not.
To answer your other question: encryption (or the lack thereof) does not
affect the caching behavior of compiled query plans.
Gert-Jan|||On Aug 14, 1:01 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>
>
>
>
> Exactly. Compilation is a relatively expansive operation, but decryption
> is not.
> To answer your other question: encryption (or the lack thereof) does not
> affect the caching behavior of compiled query plans.
> Gert-Jan
Thanks for your help guys.
affect sp runtime performance?
Does sql server encrypt the actual sp, query plans, etc... or does it
just not show you the sp contents when you try and modify the sp or
profile it?It just doesn't show it to you. It's actually obfuscated, not really
encrypted...
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"bendlam" <bendlam@.gmail.com> wrote in message
news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
> Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
> affect sp runtime performance?
> Does sql server encrypt the actual sp, query plans, etc... or does it
> just not show you the sp contents when you try and modify the sp or
> profile it?
>|||On Aug 9, 2:26 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> It just doesn't show it to you. It's actuallyobfuscated, not really
> encrypted...
> --
> Adam MachanicSQLServerMVP -http://sqlblog.com
> Author, "ExpertSQLServer2005Development"http://www.apress.com/book/bookDis
play.html?bID=10220
> "bendlam" <bend...@.gmail.com> wrote in message
> news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
>
>
>
> - Show quoted text -
So does this affect performance?|||> So does this affect performance?
Did you try it?|||On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Did you try it?
Yes I did. Didn't look like there was much of a performance
difference. What I'm trying to understand is how obfuscating the code
affects performance. i.e. Is there more work for sql server to read
the code since its obfuscated? There is little to no documentation
on this in BOL.|||The encrypted stored procedure text needs to be decrypted each time it
is compiled or recompiled. Since compiling a batch of statements is a
relatively expensive operation, I would expect the performance
difference to be unnoticable.
HTH,
Gert-Jan
bendlam wrote:
> On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
> Yes I did. Didn't look like there was much of a performance
> difference. What I'm trying to understand is how obfuscating the code
> affects performance. i.e. Is there more work for sql server to read
> the code since its obfuscated? There is little to no documentation
> on this in BOL.|||On Aug 13, 1:45 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> The encrypted stored procedure text needs to be decrypted each time it
> is compiled or recompiled. Since compiling a batch of statements is a
> relatively expensive operation, I would expect the performance
> difference to be unnoticable.
> HTH,
> Gert-Jan
>
> bendlam wrote:
>
>
>
> - Show quoted text -
just to clarify,
Did you mean compiling a batch of statements is a relatively
"inexpensive" or "expensive" operation? in which case you'd expect
performance difference would be unnoticable.
I'm assuming you meant inexpensive, and that "WITH ENCRYPTION" doesn't
affect a sql servers ability to cache its query plans to avoid
excessive recompilations, correct?
Thanks for your response.
ben|||> just to clarify,
> Did you mean compiling a batch of statements is a relatively
> "inexpensive" or "expensive" operation? in which case you'd expect
> performance difference would be unnoticable.
I think what Gert-Jan meant was, the decryption operation is such a small
part of the work that it would be negligible overall.
A|||"Aaron Bertrand [SQL Server MVP]" wrote:
>
> I think what Gert-Jan meant was, the decryption operation is such a small
> part of the work that it would be negligible overall.
> A
Exactly. Compilation is a relatively expansive operation, but decryption
is not.
To answer your other question: encryption (or the lack thereof) does not
affect the caching behavior of compiled query plans.
Gert-Jan|||On Aug 14, 1:01 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>
>
>
>
> Exactly. Compilation is a relatively expansive operation, but decryption
> is not.
> To answer your other question: encryption (or the lack thereof) does not
> affect the caching behavior of compiled query plans.
> Gert-Jan
Thanks for your help guys.
does using WITH ENCRYPTION for sps affect performance?
Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
affect sp runtime performance?
Does sql server encrypt the actual sp, query plans, etc... or does it
just not show you the sp contents when you try and modify the sp or
profile it?It just doesn't show it to you. It's actually obfuscated, not really
encrypted...
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"bendlam" <bendlam@.gmail.com> wrote in message
news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
> Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
> affect sp runtime performance?
> Does sql server encrypt the actual sp, query plans, etc... or does it
> just not show you the sp contents when you try and modify the sp or
> profile it?
>|||On Aug 9, 2:26 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> It just doesn't show it to you. It's actuallyobfuscated, not really
> encrypted...
> --
> Adam MachanicSQLServerMVP -http://sqlblog.com
> Author, "ExpertSQLServer2005Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> "bendlam" <bend...@.gmail.com> wrote in message
> news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
>
> > Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
> > affect sp runtimeperformance?
> > Doessqlserverencrypt the actual sp, query plans, etc... or does it
> > just not show you the sp contents when you try and modify the sp or
> > profile it... Hide quoted text -
> - Show quoted text -
So does this affect performance?|||> So does this affect performance?
Did you try it?|||On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > So does this affect performance?
> Did you try it?
Yes I did. Didn't look like there was much of a performance
difference. What I'm trying to understand is how obfuscating the code
affects performance. i.e. Is there more work for sql server to read
the code since its obfuscated? There is little to no documentation
on this in BOL.|||The encrypted stored procedure text needs to be decrypted each time it
is compiled or recompiled. Since compiling a batch of statements is a
relatively expensive operation, I would expect the performance
difference to be unnoticable.
HTH,
Gert-Jan
bendlam wrote:
> On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
> > > So does this affect performance?
> >
> > Did you try it?
> Yes I did. Didn't look like there was much of a performance
> difference. What I'm trying to understand is how obfuscating the code
> affects performance. i.e. Is there more work for sql server to read
> the code since its obfuscated? There is little to no documentation
> on this in BOL.|||On Aug 13, 1:45 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> The encrypted stored procedure text needs to be decrypted each time it
> is compiled or recompiled. Since compiling a batch of statements is a
> relatively expensive operation, I would expect the performance
> difference to be unnoticable.
> HTH,
> Gert-Jan
>
> bendlam wrote:
> > On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
> > <ten...@.dnartreb.noraa> wrote:
> > > > So does this affect performance?
> > > Did you try it?
> > Yes I did. Didn't look like there was much of a performance
> > difference. What I'm trying to understand is how obfuscating the code
> > affects performance. i.e. Is there more work for sql server to read
> > the code since its obfuscated? There is little to no documentation
> > on this in BOL.- Hide quoted text -
> - Show quoted text -
just to clarify,
Did you mean compiling a batch of statements is a relatively
"inexpensive" or "expensive" operation? in which case you'd expect
performance difference would be unnoticable.
I'm assuming you meant inexpensive, and that "WITH ENCRYPTION" doesn't
affect a sql servers ability to cache its query plans to avoid
excessive recompilations, correct?
Thanks for your response.
ben|||> just to clarify,
> Did you mean compiling a batch of statements is a relatively
> "inexpensive" or "expensive" operation? in which case you'd expect
> performance difference would be unnoticable.
I think what Gert-Jan meant was, the decryption operation is such a small
part of the work that it would be negligible overall.
A|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > just to clarify,
> >
> > Did you mean compiling a batch of statements is a relatively
> > "inexpensive" or "expensive" operation? in which case you'd expect
> > performance difference would be unnoticable.
> I think what Gert-Jan meant was, the decryption operation is such a small
> part of the work that it would be negligible overall.
> A
Exactly. Compilation is a relatively expansive operation, but decryption
is not.
To answer your other question: encryption (or the lack thereof) does not
affect the caching behavior of compiled query plans.
Gert-Jan|||On Aug 14, 1:01 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
> > > just to clarify,
> > > Did you mean compiling a batch of statements is a relatively
> > > "inexpensive" or "expensive" operation? in which case you'd expect
> > > performance difference would be unnoticable.
> > I think what Gert-Jan meant was, the decryption operation is such a small
> > part of the work that it would be negligible overall.
> > A
> Exactly. Compilation is a relatively expansive operation, but decryption
> is not.
> To answer your other question: encryption (or the lack thereof) does not
> affect the caching behavior of compiled query plans.
> Gert-Jan
Thanks for your help guys.
affect sp runtime performance?
Does sql server encrypt the actual sp, query plans, etc... or does it
just not show you the sp contents when you try and modify the sp or
profile it?It just doesn't show it to you. It's actually obfuscated, not really
encrypted...
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"bendlam" <bendlam@.gmail.com> wrote in message
news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
> Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
> affect sp runtime performance?
> Does sql server encrypt the actual sp, query plans, etc... or does it
> just not show you the sp contents when you try and modify the sp or
> profile it?
>|||On Aug 9, 2:26 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> It just doesn't show it to you. It's actuallyobfuscated, not really
> encrypted...
> --
> Adam MachanicSQLServerMVP -http://sqlblog.com
> Author, "ExpertSQLServer2005Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> "bendlam" <bend...@.gmail.com> wrote in message
> news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
>
> > Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
> > affect sp runtimeperformance?
> > Doessqlserverencrypt the actual sp, query plans, etc... or does it
> > just not show you the sp contents when you try and modify the sp or
> > profile it... Hide quoted text -
> - Show quoted text -
So does this affect performance?|||> So does this affect performance?
Did you try it?|||On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > So does this affect performance?
> Did you try it?
Yes I did. Didn't look like there was much of a performance
difference. What I'm trying to understand is how obfuscating the code
affects performance. i.e. Is there more work for sql server to read
the code since its obfuscated? There is little to no documentation
on this in BOL.|||The encrypted stored procedure text needs to be decrypted each time it
is compiled or recompiled. Since compiling a batch of statements is a
relatively expensive operation, I would expect the performance
difference to be unnoticable.
HTH,
Gert-Jan
bendlam wrote:
> On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
> > > So does this affect performance?
> >
> > Did you try it?
> Yes I did. Didn't look like there was much of a performance
> difference. What I'm trying to understand is how obfuscating the code
> affects performance. i.e. Is there more work for sql server to read
> the code since its obfuscated? There is little to no documentation
> on this in BOL.|||On Aug 13, 1:45 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> The encrypted stored procedure text needs to be decrypted each time it
> is compiled or recompiled. Since compiling a batch of statements is a
> relatively expensive operation, I would expect the performance
> difference to be unnoticable.
> HTH,
> Gert-Jan
>
> bendlam wrote:
> > On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
> > <ten...@.dnartreb.noraa> wrote:
> > > > So does this affect performance?
> > > Did you try it?
> > Yes I did. Didn't look like there was much of a performance
> > difference. What I'm trying to understand is how obfuscating the code
> > affects performance. i.e. Is there more work for sql server to read
> > the code since its obfuscated? There is little to no documentation
> > on this in BOL.- Hide quoted text -
> - Show quoted text -
just to clarify,
Did you mean compiling a batch of statements is a relatively
"inexpensive" or "expensive" operation? in which case you'd expect
performance difference would be unnoticable.
I'm assuming you meant inexpensive, and that "WITH ENCRYPTION" doesn't
affect a sql servers ability to cache its query plans to avoid
excessive recompilations, correct?
Thanks for your response.
ben|||> just to clarify,
> Did you mean compiling a batch of statements is a relatively
> "inexpensive" or "expensive" operation? in which case you'd expect
> performance difference would be unnoticable.
I think what Gert-Jan meant was, the decryption operation is such a small
part of the work that it would be negligible overall.
A|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > just to clarify,
> >
> > Did you mean compiling a batch of statements is a relatively
> > "inexpensive" or "expensive" operation? in which case you'd expect
> > performance difference would be unnoticable.
> I think what Gert-Jan meant was, the decryption operation is such a small
> part of the work that it would be negligible overall.
> A
Exactly. Compilation is a relatively expansive operation, but decryption
is not.
To answer your other question: encryption (or the lack thereof) does not
affect the caching behavior of compiled query plans.
Gert-Jan|||On Aug 14, 1:01 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
> > > just to clarify,
> > > Did you mean compiling a batch of statements is a relatively
> > > "inexpensive" or "expensive" operation? in which case you'd expect
> > > performance difference would be unnoticable.
> > I think what Gert-Jan meant was, the decryption operation is such a small
> > part of the work that it would be negligible overall.
> > A
> Exactly. Compilation is a relatively expansive operation, but decryption
> is not.
> To answer your other question: encryption (or the lack thereof) does not
> affect the caching behavior of compiled query plans.
> Gert-Jan
Thanks for your help guys.
Subscribe to:
Posts (Atom)