|
Home > Archive > MS SQL Server > December 2006 > perf question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| SalamElias 2006-12-05, 7:12 pm |
| Is it true that switching to "Simple" mode contribute to increase
performance? I undersatnd that in whatever mode the DB is, logging is to
transaction is done withe the difference that sql truncates the transaction
log when the DB is in "Simple".
If this is true why should we gain in perf?
Thanks
| |
| Robert Klemme 2006-12-05, 7:12 pm |
| On 05.12.2006 16:58, SalamElias wrote:
> Is it true that switching to "Simple" mode contribute to increase
> performance? I undersatnd that in whatever mode the DB is, logging is to
> transaction is done withe the difference that sql truncates the transaction
> log when the DB is in "Simple".
>
> If this is true why should we gain in perf?
You should also ask what you loose. I suggest you read up in BOL on
this topic as well as on backup and recovery.
robert
| |
| Tracy McKibben 2006-12-05, 7:12 pm |
| SalamElias wrote:
> Is it true that switching to "Simple" mode contribute to increase
> performance? I undersatnd that in whatever mode the DB is, logging is to
> transaction is done withe the difference that sql truncates the transaction
> log when the DB is in "Simple".
>
> If this is true why should we gain in perf?
> Thanks
Canned answer - "it depends". Simple mode does two things:
- makes certain operations "non-logged", reducing the amount of data
that is written to the transaction log
- automatically truncates the log, flushing out committed transactions,
eliminating the need to do transaction log backups.
That said, those operations that are now non-logged may run faster, they
may not, the log is just one of many variables.
A common misconception is that putting a database in Simple mode will
prevent unwanted transaction log growth. This is NOT necessarily true.
The log still must be large enough to hold any transaction that is
performed, so a single giant transaction can cause the log file to grow
quite large, even in Simple mode.
You also need to consider the loss of recovery options. Running in
Simple mode means you can't recover to a point in time, you can only
recover to the last full backup. Your exposure to data loss is much
greater.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
| |
| SalamElias 2006-12-05, 7:12 pm |
| So many thanks. In fact I was reviewing one of the documents regarding
day-to-day operations for a Biztalk plateforme, the IT manager indicated that
he switched all Dbs to simple mode in order to gain performance which I doubt
In reading BOL it is mentioned that even in simple or bulk-logged mode there
is log operations. So In a Biztalk plateform, I think it is a lot of
Insert,delete update operations which I should think that willnot benefit
from switching to simplemode? do you agree?
"Tracy McKibben" wrote:
> SalamElias wrote:
>
> Canned answer - "it depends". Simple mode does two things:
>
> - makes certain operations "non-logged", reducing the amount of data
> that is written to the transaction log
>
> - automatically truncates the log, flushing out committed transactions,
> eliminating the need to do transaction log backups.
>
> That said, those operations that are now non-logged may run faster, they
> may not, the log is just one of many variables.
>
> A common misconception is that putting a database in Simple mode will
> prevent unwanted transaction log growth. This is NOT necessarily true.
> The log still must be large enough to hold any transaction that is
> performed, so a single giant transaction can cause the log file to grow
> quite large, even in Simple mode.
>
> You also need to consider the loss of recovery options. Running in
> Simple mode means you can't recover to a point in time, you can only
> recover to the last full backup. Your exposure to data loss is much
> greater.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
| |
| Tracy McKibben 2006-12-05, 7:12 pm |
| SalamElias wrote:
> So many thanks. In fact I was reviewing one of the documents regarding
> day-to-day operations for a Biztalk plateforme, the IT manager indicated that
> he switched all Dbs to simple mode in order to gain performance which I doubt
>
> In reading BOL it is mentioned that even in simple or bulk-logged mode there
> is log operations. So In a Biztalk plateform, I think it is a lot of
> Insert,delete update operations which I should think that willnot benefit
> from switching to simplemode? do you agree?
>
I personally would not expect to see much of a performance difference, no...
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|
|
|
|
|