Sql-server – SQL Server 2016 – High page writes, but normal/low everything else

performancesql serversql-server-2016

I am a sysadmin and I am not very well versed in SQL Server metrics. I have been having issues with one of our DB's and the DBA doesn't seem too knowledgeable about measuring performance, so I'm trying to determine why we are having these issues.

This is a DB with 134 GB data file and 25 GB log file currently. The DB is encrypted at rest (TDE) and uses a key stored in Azure KeyVault (same region as the server). It is running in replication mode and replicating to a single SQL Server instance. The main instance runs on an Azure VM (8 vCPU, 56 GB RAM) and replicates to a backup instance in Azure SQL.

The main issue we are having is Page Writes/sec. It is currently always around 250 writes/sec or above, and rarely drops below 200. I have read that 90 writes/sec is a normal threshold. Sometimes, stored procedure executions can increase from a few milliseconds to half a second or more, severely affecting our app performance.

Meanwhile, the PLE is 60,766 secs, the Buffer Cache Hit Ratio is 100%, and Page Reads/sec are at 0.2/sec. So this is most likely not a memory issue, right?

So now what is the best way to determine what is causing the largest number of page writes on an ongoing basis? How can we know if it's simply poor design, code needing optimization, or some other issue?

Best Answer

So now what is the best way to determine what is causing the largest number of page writes on an ongoing basis?

Great question. Page writes are caused by changing your database. Full stop. Changes are written to the log file before a commit, but database pages are changed in memory, but not written on commit.

So first of all, page writes are performed in the background when no user is waiting. If you concurrently have page reads, this can be an issue, but you seem to have most of the relevant pages cached. So page writes are not necessarily causing any problems.

That being said, how do you determine what's causing the writes? It's not super-easy.

There's a DMV that will allow you to query over all the cached pages, and see which ones are dirty. This DMV can be expensive to run on systems with lots of memory, however. Something like:

select schema_name(o.schema_id) schema_name, object_name(p.object_id) table_name,  i.type_desc index_type, i.name index_name, bd.page_type, count(*) page_count
from  sys.dm_os_buffer_descriptors bd
join sys.allocation_units au
  on bd.allocation_unit_id = au.allocation_unit_id
join sys.partitions p
  on (p.hobt_id = au.container_id and au.type in (1,3))
   or(p.partition_id = au.container_id and au.type = 2)
join sys.indexes i
  on p.index_id = i.index_id
  and p.object_id = i.object_id 
join sys.objects o
  on o.object_id = i.object_id
where database_id = db_id()
and is_modified = 1
group by o.schema_id, p.object_id, i.name, i.type_desc, bd.page_type
order by page_count desc
Related Question