SQL Server 2005 Performance

17 Dec 2008 6:12 PM

sjg

We're seeing a strange thing occur with a process that only reads the
database via views and stored procs. After a certain point in the process a
significant slow down occurs. We're not seeing anything sginificant in
profiler to indicate a problem but setting the log file to simple and back to
full either right before or while the process is running fixes the
performance (we stumbled upon this workaround accidently). Does anyone know
what happens with the database and/or engine when switching between full and
simple?

Thanks, sjg

18 Dec 2008 2:28 AM

Jonathan Kehayias

Are you taking frequent log backups in FULL Recovery? If not then you don't
need the database in FULL Recovery. Changing from FULL to SIMPLE truncates
the active portion of the log and makes it immediately available for reuse.
What is the value of the log_reuse_wait_desc for the database in question
when the problem occurs?

select name, recovery_model_desc, log_reuse_wait_desc

from sys.databases

--
Jonathan Kehayias
SQL Server MVP
http://jmkehayias.blogspot.com
http://www.sqlclr.net

Show quoteHide quote

"sjg" <s**@discussions.microsoft.com> wrote in message
news:AF89BA2C-2140-4166-9757-13502C1EBC13@microsoft.com...
> We're seeing a strange thing occur with a process that only reads the
> database via views and stored procs. After a certain point in the process
> a
> significant slow down occurs. We're not seeing anything sginificant in
> profiler to indicate a problem but setting the log file to simple and back
> to
> full either right before or while the process is running fixes the
> performance (we stumbled upon this workaround accidently). Does anyone
> know
> what happens with the database and/or engine when switching between full
> and
> simple?
>
> Thanks, sjg

18 Dec 2008 4:20 PM

sjg

Hi Jonathan,

Yes, we have a transactional system so we are taking log backups every 30 or
60 minutes. The database does need to be in FULL mode. However, we've seen
the problem when the database is set to SIMPLE mode and the problem goes away
when we set it back to FULL. It seems that making any change to the
recovery model is what speeds the process back up.

I will try and find out the log_reuse_wait_desc sometime today.

Thanks,

Sarah

Show quoteHide quote

"Jonathan Kehayias" wrote:

> Are you taking frequent log backups in FULL Recovery? If not then you don't
> need the database in FULL Recovery. Changing from FULL to SIMPLE truncates
> the active portion of the log and makes it immediately available for reuse.
> What is the value of the log_reuse_wait_desc for the database in question
> when the problem occurs?
>
> select name, recovery_model_desc, log_reuse_wait_desc
>
> from sys.databases
>
>
> --
> Jonathan Kehayias
> SQL Server MVP
> http://jmkehayias.blogspot.com
> http://www.sqlclr.net
>
>
> "sjg" <s**@discussions.microsoft.com> wrote in message
> news:AF89BA2C-2140-4166-9757-13502C1EBC13@microsoft.com...
> > We're seeing a strange thing occur with a process that only reads the
> > database via views and stored procs. After a certain point in the process
> > a
> > significant slow down occurs. We're not seeing anything sginificant in
> > profiler to indicate a problem but setting the log file to simple and back
> > to
> > full either right before or while the process is running fixes the
> > performance (we stumbled upon this workaround accidently). Does anyone
> > know
> > what happens with the database and/or engine when switching between full
> > and
> > simple?
> >
> > Thanks, sjg
>
>
>