Measuring the duration of encrypted stored procedure

Hi Everybody,
I am trying to measure the difference in execution between the encrypted
version versus the non-encrypted version of a stored procedure. I found that
in some tries the encrypted version of the stored procedure takes less time
to execute than the unencrypted version!!! (I an using SQL Profiler and a
custom template to store the result in a table, which is on a different sql
server on a different machine).
Any thoughts on why this is happening would be highly appreciated.
Thanks.
Regards,
Soumitra BanerjeeI'm not aware of an reasons the encrypted proc should be any different.
There certainly could be reaons I'm not aware of though...
what types of performance differences are you seeing?
<<
I found that
> in some tries the encrypted version of the stored procedure takes less
time
> to execute than the unencrypted version!!!
needless to say... a proc isn't guaranteed to take the same amount of time
every time... are you sure there's a correlation in time based on whether
it's encrypted? Could it simply be that your proc takes different amounts of
time to run based on load on the server or the parameters you're passing in?
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
news:%231sdZal%23DHA.2664@.TK2MSFTNGP09.phx.gbl...
> Hi Everybody,
> I am trying to measure the difference in execution between the encrypted
> version versus the non-encrypted version of a stored procedure. I found
that
> in some tries the encrypted version of the stored procedure takes less
time
> to execute than the unencrypted version!!! (I an using SQL Profiler and a
> custom template to store the result in a table, which is on a different
sql
> server on a different machine).
> Any thoughts on why this is happening would be highly appreciated.
> Thanks.
> Regards,
> Soumitra Banerjee
>|||Thanks for the mail. The result of my testing are as follows:
SQL Server was restarted before TRY 1 . After TRY 1 the same stored
procedure is executed 4 times and the time differences are noted using SQL
Profiler.
SET 1
Non Encrypted Version
Encrypted Version
TRY 1 4694
4589.4
TRY 2 635.2
406.2
TRY 3 330
346.2
TRY 4 534
589
TRY 5 357
303.2
SET 2
Non Encrypted Version
Encrypted Version
TRY 1 8439
8435
TRY 2 1350
956
TRY 3 944
856
TRY 4 1014
813
TRY 5 911
792
SET 3
Non Encrypted Version
Encrypted Version
TRY 1 8900
2800
TRY 2 1197
1356
TRY 3 1059
1033
TRY 4 764
1150
TRY 5 1095
1086
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:ubCjbjl#DHA.3232@.TK2MSFTNGP10.phx.gbl...
> I'm not aware of an reasons the encrypted proc should be any different.
> There certainly could be reaons I'm not aware of though...
> what types of performance differences are you seeing?
> <<
> I found that
> time
> needless to say... a proc isn't guaranteed to take the same amount of time
> every time... are you sure there's a correlation in time based on whether
> it's encrypted? Could it simply be that your proc takes different amounts
of
> time to run based on load on the server or the parameters you're passing
in?
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
> news:%231sdZal%23DHA.2664@.TK2MSFTNGP09.phx.gbl...
> that
> time
a
> sql
>|||Soumitra,
Are you using
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
between executions, as well as exactly the same parameter set?
If not, you cannot expect similar results.
And, even if you are, you still cannot expect standardized results until
you're working in a sandbox.
Machine load variations are a huge factor.
James Hokes
"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
news:%231sdZal%23DHA.2664@.TK2MSFTNGP09.phx.gbl...
> Hi Everybody,
> I am trying to measure the difference in execution between the encrypted
> version versus the non-encrypted version of a stored procedure. I found
that
> in some tries the encrypted version of the stored procedure takes less
time
> to execute than the unencrypted version!!! (I an using SQL Profiler and a
> custom template to store the result in a table, which is on a different
sql
> server on a different machine).
> Any thoughts on why this is happening would be highly appreciated.
> Thanks.
> Regards,
> Soumitra Banerjee
>