How to Reduce the WRITE_COMPLETION wait type

19 June,2014 by Jack Vamvas

SQL Server Books online defines WRITE_COMPLETION as “Occurs when a write operation is progress”. This definition  doesn’t give a deep insight into the activity types which trigger the WRITE_COMPLETIONB wait type

The definition is fairly generic and requires some analysis to identify when it occurs and if it’s causing a problem.

Some examples of when WRITE_COMPLETION occurs:

1) Copying pages from the model database into a database during the CREATE DATABASE process

2) Creating Global Allocation Map (GAM) pages

3) Create and mark Page Free Space (PFS) pages byte-map

4) Expanding a Database  data file grow command will initiate page allocations

 

Typically , WRITE_COMPLETION doesn’t reflect a  problem . If you’re tracking a single action – WRITE_COMPLETION appears   alongside  other wait types .  I rarely see WRITE_COMPLETION at the top of the wait types.

If there are persistent  write operations resulting in higher than normal levels of  WRITE_COMPLETION than try to analyse the root cause.  For example , is there high PFS page latch contention?

Find sql queries matching wait types

Read More

SQL WAIT TYPE – RESOURCE SEMAPHORE ... - SQL Server DBA

SQL WAIT TYPE – OLEDB and how to reduce it

SQL Server – LCK_M_IS and how to reduce it - sql wait type


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to Reduce the WRITE_COMPLETION wait type


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer