Drill down slow: affect

Showing posts with label affect. Show all posts

Sunday, February 26, 2012

Don

Hi every one,
I learn a lot reading your comments,
Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
performance or create locks ?
I mean : While UPDATE STATISTICS is runnning, will it
affect performance ?
Thanks !
DonYes it will have an impact. Whether this will be noticeable to users are
not is another question...
Regarding locking, SQL Server takes two types of locks:
"Sch-S: Schema Stability Lock
--
This lock ensures that a schema element, such as a table or index, will
not be dropped while any session holds a schema stability lock on the
schema element.
Sch-M-UPD-STATS: Schema Modification Lock
---
This is a non-blocking lock that is used by the system to ensure that
only one automatic UPDATE STATISTICS process is run against a table at
any given point in time. The sp_lock stored procedure will report this
lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Above information found here:
INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work
http://support.microsoft.com/kb/q195565/
They should not block usual DML queries (insert, update, delete), but
may block DDL queries (drop table, alter table, alter database, etc).
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
PERFORMANCE DURING UPDATING STATS wrote:
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don|||"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
It will have an effect on performance, in that it uses system resources.
How much of an effect is dependant on your server's horsepower, the size of
the table you're updating statistics for, etc... It will, like any other
read operation, create shared locks, but I have not seen it create exclusive
locks. So blocking should not be an issue.|||"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OtavF8dtEHA.2072@.tk2msftngp13.phx.gbl...
> any given point in time. The sp_lock stored procedure will report this
> lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Mark,
I've never seen the SCH-M lock; I always get only SCH-S when I use
UPDATE STATISTICS on my systems. Is there an option that controls which
lock types are taken?|||Adam,
No, not that I'm aware of.
Adam Machanic wrote:
> I've never seen the SCH-M lock; I always get only SCH-S when I use
> UPDATE STATISTICS on my systems. Is there an option that controls which
> lock types are taken?
>|||UPDATE STATISTICS doesn't use locks, but it will affect performance, because
data pages are randomly accessed from the table to create the statistics.
Most likely not all of these pages are in memory and have to be read from
disk.
--
Jacco Schalkwijk
SQL Server MVP
"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don

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.

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.

Sunday, February 26, 2012

Don

Tuesday, February 14, 2012

does using WITH ENCRYPTION for sps affect performance?

does using WITH ENCRYPTION for sps affect performance?

does using WITH ENCRYPTION for sps affect performance?

Drill down slow

Blog Archive

About Me