question

amfineandyou123 avatar image
amfineandyou123 asked

Performance question- Flush Cache

FlushCache: cleaned up 45476 bufs with 16533 writes in 83572 ms (avoided 2531 new dirty bufs) for db last target outstanding: 16, avgWriteLatency 1008 average writes per second: 205.08 writes/sec average throughput: 3.47 MB/sec, I/O saturation: 4774, context switches 8768 FlushCache: cleaned up 28432 bufs with 13127 writes in 64008 ms (avoided 9205 new dirty bufs) for db last target outstanding: 50, avgWriteLatency 46 average writes per second: 152.36 writes/sec average throughput: 3.22 MB/sec, I/O saturation: 6462, context switches 9622 I am going through one of the server performance issue and found the above messages a lot in the error log and mostly this is appearing in the business hours and most of the reports are getting failed.The database is recently migrated to SQL 2016 from SQL 2008 and application framework has been upgraded to support Sql 2016.I heard like when they were with SQL 2008, they had Performance issue like reports fail but not like this after they move to SQL 2016 where they seen most of the reports failed. During the business hours, I saw most of the sessions are in suspended state. Task manager shows SQL Server uses his maximum memory (90%). Tried the SP_Blitzcache from Brent and found that one of the SP ( the same SP I got from Dev team saying they want to rewrite the code as it uses Cursor) might cause the issue,the SP internally calls multiple stored procedure and the SP_Blitzcache shows me the warning as " Parameter Sniffing, Long Running Query, Low Cost High CPU". Can one help me to get what this error is in the log, is it a memory contention or IO issue? and how to start the troubleshoot in this scenario.?
sql serversql server 2012sql querysql server 2008 r2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.