MS SQL Server - SQL 2005 LazyWriter performance problem

Author

SQL 2005 LazyWriter performance problem

David Beaver

2006-10-24, 6:33 pm

I'm posting this so maybe it will help others.

Our SQL 2005 server, within 18-24 hours after reboot, would start to slow
down. Queries that normally took 1 second would take 3 or 4. Task manager
showed CPU usage at 30% even when no users were active.

On investigation:
- The third and fourth of our four processors were busy all the time
- One of the LAZYWRITER processes in SQL Server was running all the time...
in Activity Monitor it showed cumulative CPU in the hundreds of millions
(milliseconds).
- This problem only got worse over time; after weeks, the Resource Monitor
process would start to "run amok" just like Lazywriter.

MS Support analyzed logs and discovered that the problems were related to
the "NUMA" feature of SQL/Windows/AMD processors.

We simply tweaked the BIOS to disable NUMA and the problem went away.
LazyWriter CPU usage is now less than 300ms/day.

NUMA is (to us) pretty obscure... it's an technology that improves memory
access for machines with a large number of processors. Unknown to us, our
configuration (Two dual-core Opterons, Windows 2003 Enterprise, 16 GB RAM)
was sufficient for SQL 2005 to go into NUMA mode on startup. We clearly
don't need the power of NUMA, so turning it off was no big deal.

Greg Linwood

2006-10-24, 6:33 pm

Hi David

Thanks for sharing this - it's very interesting information.

Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood

"David Beaver" < DavidBeaver@discussi
ons.microsoft.com> wrote in message
news:7EDABB38-C582-4550-A08A- 060A0B2BF5E9@microso
ft.com...
> I'm posting this so maybe it will help others.
>
> Our SQL 2005 server, within 18-24 hours after reboot, would start to slow
> down. Queries that normally took 1 second would take 3 or 4. Task
> manager
> showed CPU usage at 30% even when no users were active.
>
> On investigation:
> - The third and fourth of our four processors were busy all the time
> - One of the LAZYWRITER processes in SQL Server was running all the
> time...
> in Activity Monitor it showed cumulative CPU in the hundreds of millions
> (milliseconds).
> - This problem only got worse over time; after weeks, the Resource Monitor
> process would start to "run amok" just like Lazywriter.
>
> MS Support analyzed logs and discovered that the problems were related to
> the "NUMA" feature of SQL/Windows/AMD processors.
>
> We simply tweaked the BIOS to disable NUMA and the problem went away.
> LazyWriter CPU usage is now less than 300ms/day.
>
> NUMA is (to us) pretty obscure... it's an technology that improves memory
> access for machines with a large number of processors. Unknown to us, our
> configuration (Two dual-core Opterons, Windows 2003 Enterprise, 16 GB RAM)
> was sufficient for SQL 2005 to go into NUMA mode on startup. We clearly
> don't need the power of NUMA, so turning it off was no big deal.

Sponsored Links