PAGES WRITES PER SECONDS ARE VERY HIGH

Question

I have a sharepoint database size of 200 GB and memory 64 GB, my pages writes/sec are more than 250. I tried every thing but still can't find a way to reduce, pages read/sec and page life expectancy are normal

Answer 1

Did you check index fragmentation? High index fragmentation can lead to increased write activity. You can maintain the indexes in the database using scripts to rebuild or reorganize fragmented indexes.

ALTER INDEX...REORGANIZE

Answer 2

If you have lots of page writes, that suggests that your system is doing a lot of work. Your users like Sharepoint and work with it all day long! Or is there some process somewhere that has got stuck in a loop and is updating the same stuff again and again?

In any cases, have you tracked down which database(s) that are begin hammered? Particularly, you need to understand if it is user database or tempdb.

Use the function sys.dm_io_virtual_files_stats. This function returns cumulative data, so call it twice, 5-60 minutes apart and compute the deltas, so you can see which files that are being written to.

Depending on the findings, you would then decide on your next steps.

Answer 3

Hi @Mohammed A. Tabrez

Referring from this tech doc: Optimize tempdb performance in SQL Server

Starting with SQL Server 2016 (13.x), tempdb performance is further optimized in the following ways:

• Temporary tables and table variables are cached. Caching allows operations that drop and create the temporary objects to run very quickly. Caching also reduces page allocation and metadata contention.
• The allocation page latching protocol is improved to reduce the number of UP (update) latches that are used.
• Logging overhead for tempdb is reduced to reduce disk I/O bandwidth consumption on the tempdb log file.
• Setup adds multiple tempdb data files during a new instance installation. You can accomplish this task by using the new UI input control in the Database Engine Configuration section and the command-line parameter /SQLTEMPDBFILECOUNT. By default, setup adds as many tempdb data files as the logical processor count or eight, whichever is lower.
• When there are multiple tempdb data files, all files autogrow at the same time and by the same amount, depending on growth settings. Trace flag 1117 is no longer required.
• All allocations in tempdb use uniform extents. Trace flag 1118 is no longer required.
• For the primary filegroup, the AUTOGROW_ALL_FILES property is turned on and the property can't be modified.

For more information on performance improvements in tempdb, see the blog article TEMPDB - Files and Trace Flags and Updates, Oh My!

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.