The LAZYWRITER_SLEEP wait type is a background wait related to the SQL Server internal lazywriter process. This wait type shows some characteristics with the checkpoint process where the dirty pages are written to database data files from the buffer cache. As a database admin, it is good to understand the reason and occurrence of the LAZTWRITER_SLEEP wait type.
What is the LAZYWRITER_SLEEP Wait Type?
The LAZYWRITER_SLEEP
wait type is a background wait like CHECKPOINT_QUEUE waiting for work in your
SQL Server instance. But the role of this wait type is slightly different from what
is done by the CHECKPOINT_QUEUE wait type. The LAZYWRITER_SLEEP checks and
scans the size of the buffer cache and determines if there is always a certain amount
of space to fit the incoming new pages. The process checks to see the buffer
cache space for free pages instead of swapping with the old ones.
In case
there is not enough buffer cache space to write new pages, the lazywriter
process will detect, between checkpoint, to see if there are any dirty pages in
the buffer cache that were not accessed for a while. Such pages will be written
to the database data file and removed from the buffer cache. In case your
server is busy, then the lazywriter process will take some time to free up space
in the buffer cache.
Performance Metrics
In most cases, the LAZYWRITER_SLEEP can be safely ignored. However, if you notice a constant movement of dirty pages to the database data files from the buffer cache, it might indicate a possible performance issue. Your SQL Server instance could be facing memory pressure.
sys.dm_os_wait_stats
select * from sys.dm_os_wait_stats
where wait_type='LAZYWRITER_SLEEP'
The wait type can be checked in the sys.dm_os_wait_stats DMV. The wait_time_ms indicates the amount of time the lazywriter process spent being inactive. Though they signal a high value, they can be ignored. However, the DBAs can know how long the lazywriter process is spent inactive in the SQL Server instance.
Bottomline
The LAZYWRITER_SLEEP wait type does not wake up or stay asleep until there is a signal to check for the buffer cache. This wait type is a background wait and can be safely ignored.
0 comments:
Post a Comment