Recovery Model

Hi - can anyone tell me which is faster for a datawarehouse ETL process? -
Bulk Logged or Simple recovery.
Data recovery isn't importat to me as the source data isn't deleted until
the warehouse is loaded & backed up.
I've been using simple recovery but I wondered if Bulk Logged would make my
SSIS table inserts any faster.
Thanks
MikeMike,
Prior to SQL Server 2000, if I remember correctly, you would receive a
benefit by setting the database option "select into/bulkcopy" for a narrow
set of circumstances. If you could not hit those circumstances the bulk
insert would still be fully logged.
However, from 2000 forward, Bulk Logged should not make things any faster
than Simple. Bulk Logged is essentially a somewhat faster logging model
than Full. Simple mode is the way to go if you depend on a complete backup
being made at the end of the process.
RLF
"Michael Knee" <mike__k@.hotmail.com> wrote in message
news:%23A3cQLYFIHA.1056@.TK2MSFTNGP03.phx.gbl...
> Hi - can anyone tell me which is faster for a datawarehouse ETL process? -
> Bulk Logged or Simple recovery.
> Data recovery isn't importat to me as the source data isn't deleted until
> the warehouse is loaded & backed up.
> I've been using simple recovery but I wondered if Bulk Logged would make
> my SSIS table inserts any faster.
> Thanks
> Mike
>|||Excellent - Thank You.
I'll stick with Simple then.
Mike
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ePHiRkYFIHA.4628@.TK2MSFTNGP02.phx.gbl...
> Mike,
> Prior to SQL Server 2000, if I remember correctly, you would receive a
> benefit by setting the database option "select into/bulkcopy" for a narrow
> set of circumstances. If you could not hit those circumstances the bulk
> insert would still be fully logged.
> However, from 2000 forward, Bulk Logged should not make things any faster
> than Simple. Bulk Logged is essentially a somewhat faster logging model
> than Full. Simple mode is the way to go if you depend on a complete
> backup being made at the end of the process.
> RLF
>
> "Michael Knee" <mike__k@.hotmail.com> wrote in message
> news:%23A3cQLYFIHA.1056@.TK2MSFTNGP03.phx.gbl...
>> Hi - can anyone tell me which is faster for a datawarehouse ETL
>> process? - Bulk Logged or Simple recovery.
>> Data recovery isn't importat to me as the source data isn't deleted until
>> the warehouse is loaded & backed up.
>> I've been using simple recovery but I wondered if Bulk Logged would make
>> my SSIS table inserts any faster.
>> Thanks
>> Mike
>|||Curious. I would have said bulk logged was faster due to significantly less
stuff being logged.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ePHiRkYFIHA.4628@.TK2MSFTNGP02.phx.gbl...
> Mike,
> Prior to SQL Server 2000, if I remember correctly, you would receive a
> benefit by setting the database option "select into/bulkcopy" for a narrow
> set of circumstances. If you could not hit those circumstances the bulk
> insert would still be fully logged.
> However, from 2000 forward, Bulk Logged should not make things any faster
> than Simple. Bulk Logged is essentially a somewhat faster logging model
> than Full. Simple mode is the way to go if you depend on a complete
> backup being made at the end of the process.
> RLF
>
> "Michael Knee" <mike__k@.hotmail.com> wrote in message
> news:%23A3cQLYFIHA.1056@.TK2MSFTNGP03.phx.gbl...
>> Hi - can anyone tell me which is faster for a datawarehouse ETL
>> process? - Bulk Logged or Simple recovery.
>> Data recovery isn't importat to me as the source data isn't deleted until
>> the warehouse is loaded & backed up.
>> I've been using simple recovery but I wondered if Bulk Logged would make
>> my SSIS table inserts any faster.
>> Thanks
>> Mike
>|||Kevin,
I did some looking to see if that was defined, because I believe that it was
once true. But I could not find an indication that it is still true. In
fact, http://msdn2.microsoft.com/en-us/library/ms190421.aspx makes the
following comment: "The simple recovery model minimally logs most bulk
operations.
I have not tried to do my own bench marks, but maybe someone has personal
experience with this.
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13hunbvi9h6k958@.corp.supernews.com...
> Curious. I would have said bulk logged was faster due to significantly
> less stuff being logged.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:ePHiRkYFIHA.4628@.TK2MSFTNGP02.phx.gbl...
>> Mike,
>> Prior to SQL Server 2000, if I remember correctly, you would receive a
>> benefit by setting the database option "select into/bulkcopy" for a
>> narrow set of circumstances. If you could not hit those circumstances
>> the bulk insert would still be fully logged.
>> However, from 2000 forward, Bulk Logged should not make things any faster
>> than Simple. Bulk Logged is essentially a somewhat faster logging model
>> than Full. Simple mode is the way to go if you depend on a complete
>> backup being made at the end of the process.
>> RLF
>>
>> "Michael Knee" <mike__k@.hotmail.com> wrote in message
>> news:%23A3cQLYFIHA.1056@.TK2MSFTNGP03.phx.gbl...
>> Hi - can anyone tell me which is faster for a datawarehouse ETL
>> process? - Bulk Logged or Simple recovery.
>> Data recovery isn't importat to me as the source data isn't deleted
>> until the warehouse is loaded & backed up.
>> I've been using simple recovery but I wondered if Bulk Logged would
>> make my SSIS table inserts any faster.
>> Thanks
>> Mike
>>
>|||In 2005 there is no difference in what gets logged between simple and Bulk
Logged. If the operation is such that it meets the criteria for a minimally
logged load it will be do the same amount of logging for simple as bulk
logged. The real differences are for recovery purposes between the two not
the amount logged.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eQHUdemFIHA.4196@.TK2MSFTNGP04.phx.gbl...
> Kevin,
> I did some looking to see if that was defined, because I believe that it
> was once true. But I could not find an indication that it is still true.
> In fact, http://msdn2.microsoft.com/en-us/library/ms190421.aspx makes the
> following comment: "The simple recovery model minimally logs most bulk
> operations.
> I have not tried to do my own bench marks, but maybe someone has personal
> experience with this.
> RLF
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13hunbvi9h6k958@.corp.supernews.com...
>> Curious. I would have said bulk logged was faster due to significantly
>> less stuff being logged.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:ePHiRkYFIHA.4628@.TK2MSFTNGP02.phx.gbl...
>> Mike,
>> Prior to SQL Server 2000, if I remember correctly, you would receive a
>> benefit by setting the database option "select into/bulkcopy" for a
>> narrow set of circumstances. If you could not hit those circumstances
>> the bulk insert would still be fully logged.
>> However, from 2000 forward, Bulk Logged should not make things any
>> faster than Simple. Bulk Logged is essentially a somewhat faster
>> logging model than Full. Simple mode is the way to go if you depend on
>> a complete backup being made at the end of the process.
>> RLF
>>
>> "Michael Knee" <mike__k@.hotmail.com> wrote in message
>> news:%23A3cQLYFIHA.1056@.TK2MSFTNGP03.phx.gbl...
>> Hi - can anyone tell me which is faster for a datawarehouse ETL
>> process? - Bulk Logged or Simple recovery.
>> Data recovery isn't importat to me as the source data isn't deleted
>> until the warehouse is loaded & backed up.
>> I've been using simple recovery but I wondered if Bulk Logged would
>> make my SSIS table inserts any faster.
>> Thanks
>> Mike
>>
>>
>