SQL Server 2016 - High page writes, but normal/low everything else
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