Very strange problem occurs when we removed from few tables indexes and added it again. Database after that operation is noticeably slower than the same DB before.
Also the DB after removal / adding indexes seems to be different in a unknown way.
I could prove that running SQL Analyzer - query plan analysis on both DBs are different before operation and after that!!! (?)
So the DBs are identical but in some strange way different.
Could you have any ideas or maybe it is known SQL Srv 2000 problem?
We run SQL 2000 SP3.
Kind regards,
Adam Heczko
Just recompile your stored procedures and views - generate script for
drop/create and run it.
Bojidar Alexandrov
|||Adam
Try to update statistics
"Adam Heczko" <a.heczko@.zibico.com.pl> wrote in message
news:933E6C88-A99E-40BB-A080-B0C8FBB722C7@.microsoft.com...
> Very strange problem occurs when we removed from few tables indexes and
added it again. Database after that operation is noticeably slower than the
same DB before.
> Also the DB after removal / adding indexes seems to be different in a
unknown way.
> I could prove that running SQL Analyzer - query plan analysis on both DBs
are different before operation and after that!!! (?)
> So the DBs are identical but in some strange way different.
> Could you have any ideas or maybe it is known SQL Srv 2000 problem?
> We run SQL 2000 SP3.
> Kind regards,
> Adam Heczko
|||are you sure you rebuilt the indexes in the same way they were built before
?
Fill Factor Settings
Pad_Index Settings
File Group, etc etc etc
?
Greg Jackson
PDX, Oregon
|||> are you sure you rebuilt the indexes in the same way they were built before
> ?
> Fill Factor Settings
> Pad_Index Settings
> File Group, etc etc etc
>
> ?
> Greg Jackson
> PDX, Oregon
>
Yes, we tried to rebuild stored procedures, views etc. - nothing really
helped.
Adam Heczko
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Thursday, March 29, 2012
Dramatical performance degradation after removing / adding indexes
Very strange problem occurs when we removed from few tables indexes and adde
d it again. Database after that operation is noticeably slower than the same
DB before.
Also the DB after removal / adding indexes seems to be different in a unknow
n way.
I could prove that running SQL Analyzer - query plan analysis on both DBs ar
e different before operation and after that!!! (?)
So the DBs are identical but in some strange way different.
Could you have any ideas or maybe it is known SQL Srv 2000 problem?
We run SQL 2000 SP3.
Kind regards,
Adam HeczkoJust recompile your stored procedures and views - generate script for
drop/create and run it.
Bojidar Alexandrov|||Adam
Try to update statistics
"Adam Heczko" <a.heczko@.zibico.com.pl> wrote in message
news:933E6C88-A99E-40BB-A080-B0C8FBB722C7@.microsoft.com...
> Very strange problem occurs when we removed from few tables indexes and
added it again. Database after that operation is noticeably slower than the
same DB before.
> Also the DB after removal / adding indexes seems to be different in a
unknown way.
> I could prove that running SQL Analyzer - query plan analysis on both DBs
are different before operation and after that!!! (?)
> So the DBs are identical but in some strange way different.
> Could you have any ideas or maybe it is known SQL Srv 2000 problem?
> We run SQL 2000 SP3.
> Kind regards,
> Adam Heczko|||are you sure you rebuilt the indexes in the same way they were built before
?
Fill Factor Settings
Pad_Index Settings
File Group, etc etc etc
?
Greg Jackson
PDX, Oregon|||> are you sure you rebuilt the indexes in the same way they were built before">
> ?
> Fill Factor Settings
> Pad_Index Settings
> File Group, etc etc etc
>
> ?
> Greg Jackson
> PDX, Oregon
>
Yes, we tried to rebuild stored procedures, views etc. - nothing really
helped.
Adam Heczkosql
d it again. Database after that operation is noticeably slower than the same
DB before.
Also the DB after removal / adding indexes seems to be different in a unknow
n way.
I could prove that running SQL Analyzer - query plan analysis on both DBs ar
e different before operation and after that!!! (?)
So the DBs are identical but in some strange way different.
Could you have any ideas or maybe it is known SQL Srv 2000 problem?
We run SQL 2000 SP3.
Kind regards,
Adam HeczkoJust recompile your stored procedures and views - generate script for
drop/create and run it.
Bojidar Alexandrov|||Adam
Try to update statistics
"Adam Heczko" <a.heczko@.zibico.com.pl> wrote in message
news:933E6C88-A99E-40BB-A080-B0C8FBB722C7@.microsoft.com...
> Very strange problem occurs when we removed from few tables indexes and
added it again. Database after that operation is noticeably slower than the
same DB before.
> Also the DB after removal / adding indexes seems to be different in a
unknown way.
> I could prove that running SQL Analyzer - query plan analysis on both DBs
are different before operation and after that!!! (?)
> So the DBs are identical but in some strange way different.
> Could you have any ideas or maybe it is known SQL Srv 2000 problem?
> We run SQL 2000 SP3.
> Kind regards,
> Adam Heczko|||are you sure you rebuilt the indexes in the same way they were built before
?
Fill Factor Settings
Pad_Index Settings
File Group, etc etc etc
?
Greg Jackson
PDX, Oregon|||> are you sure you rebuilt the indexes in the same way they were built before">
> ?
> Fill Factor Settings
> Pad_Index Settings
> File Group, etc etc etc
>
> ?
> Greg Jackson
> PDX, Oregon
>
Yes, we tried to rebuild stored procedures, views etc. - nothing really
helped.
Adam Heczkosql
Dramatical performance degradation after removing / adding indexes
Very strange problem occurs when we removed from few tables indexes and added it again. Database after that operation is noticeably slower than the same DB before
Also the DB after removal / adding indexes seems to be different in a unknown way
I could prove that running SQL Analyzer - query plan analysis on both DBs are different before operation and after that!!! (?
So the DBs are identical but in some strange way different
Could you have any ideas or maybe it is known SQL Srv 2000 problem
We run SQL 2000 SP3
Kind regards
Adam HeczkoJust recompile your stored procedures and views - generate script for
drop/create and run it.
Bojidar Alexandrov|||Adam
Try to update statistics
"Adam Heczko" <a.heczko@.zibico.com.pl> wrote in message
news:933E6C88-A99E-40BB-A080-B0C8FBB722C7@.microsoft.com...
> Very strange problem occurs when we removed from few tables indexes and
added it again. Database after that operation is noticeably slower than the
same DB before.
> Also the DB after removal / adding indexes seems to be different in a
unknown way.
> I could prove that running SQL Analyzer - query plan analysis on both DBs
are different before operation and after that!!! (?)
> So the DBs are identical but in some strange way different.
> Could you have any ideas or maybe it is known SQL Srv 2000 problem?
> We run SQL 2000 SP3.
> Kind regards,
> Adam Heczko|||are you sure you rebuilt the indexes in the same way they were built before
?
Fill Factor Settings
Pad_Index Settings
File Group, etc etc etc
?
Greg Jackson
PDX, Oregon|||> are you sure you rebuilt the indexes in the same way they were built before
> ?
> Fill Factor Settings
> Pad_Index Settings
> File Group, etc etc etc
>
> ?
> Greg Jackson
> PDX, Oregon
>
Yes, we tried to rebuild stored procedures, views etc. - nothing really
helped.
Adam Heczko
Also the DB after removal / adding indexes seems to be different in a unknown way
I could prove that running SQL Analyzer - query plan analysis on both DBs are different before operation and after that!!! (?
So the DBs are identical but in some strange way different
Could you have any ideas or maybe it is known SQL Srv 2000 problem
We run SQL 2000 SP3
Kind regards
Adam HeczkoJust recompile your stored procedures and views - generate script for
drop/create and run it.
Bojidar Alexandrov|||Adam
Try to update statistics
"Adam Heczko" <a.heczko@.zibico.com.pl> wrote in message
news:933E6C88-A99E-40BB-A080-B0C8FBB722C7@.microsoft.com...
> Very strange problem occurs when we removed from few tables indexes and
added it again. Database after that operation is noticeably slower than the
same DB before.
> Also the DB after removal / adding indexes seems to be different in a
unknown way.
> I could prove that running SQL Analyzer - query plan analysis on both DBs
are different before operation and after that!!! (?)
> So the DBs are identical but in some strange way different.
> Could you have any ideas or maybe it is known SQL Srv 2000 problem?
> We run SQL 2000 SP3.
> Kind regards,
> Adam Heczko|||are you sure you rebuilt the indexes in the same way they were built before
?
Fill Factor Settings
Pad_Index Settings
File Group, etc etc etc
?
Greg Jackson
PDX, Oregon|||> are you sure you rebuilt the indexes in the same way they were built before
> ?
> Fill Factor Settings
> Pad_Index Settings
> File Group, etc etc etc
>
> ?
> Greg Jackson
> PDX, Oregon
>
Yes, we tried to rebuild stored procedures, views etc. - nothing really
helped.
Adam Heczko
Friday, March 9, 2012
Dose Perspective to an extent improve the cube process performance?
Hi, all experts here,
Thank you very much for your kind attention.
Would please any expert here give me any guidance and advice that if a Perspective of a cube really improve the performance of the process in terms of process speed.
Thank you very much in advance for any guidance and advice for that.
With best regards,
Yours sincerely,
Perspectives don't affect cube processing in one way or another. Think about Perspective vs. Cube as about View vs. Table.|||
Hi, Mosha, thank you very much for your guidance.
So is it in a way like views for tables make retriveving data faster from a perspective rather than a large cube as what views meant to tables. Dose that sense perspective mean to cube (besides in a way to well organize information in cube for specific perspective)? Thanks a lot.
With best regards,
Yours sincerely,
|||Retrieving data from perspective will have exactly same performance as retrieving data from the cube. Perspective is mostly just a way to hide some dimensions/measures/actions/calculated members to have less cluttered and more focused view on the cube. But all the queries go against the cube.|||
Hi, Mosha, thank you very much for your guidance.
With best regards,
Yours sincerely,
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
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.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.
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.
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)