Say the first hardware information
Server 2 4 8 thread core processor, 128 gb of memory
Now data has three libraries, the database file is bigger, each data about 1 t, when online around 500 per second,
There are a lot of table data in 100 million - 200 million, but because the data structure is simple, basically stored procedure operation within about 200 milliseconds

Not regular appearances FlushCache prompt now, then the server will card for a while, may be a few minutes,
The log is as follows:
FlushCache: cleaned up 1808 bufs with 1153 writes in 235582 ms (257 new avoided dirty bufs) for db 20:0

Average throughput: 0.06 MB/SEC, I/O saturation: 7999, context switches 16454

The last target outstanding: 80, 15 avgWriteLatency

CodePudding user response:

Top, don't sink!

CodePudding user response:

Few people really looks like MSSQL use.

CodePudding user response:

Not with few people, it is you that this order of magnitude with MSSQL is not much

CodePudding user response:

reference wtujedp reply: 3/f

not with few people, are you the order of magnitude with MSSQL few

In fact most of the time or it's not a problem, I separation using MSSQL 2012 ALWAYSON, speaking, reading and writing.

Is the problem appear occasionally, feel strange,

CodePudding user response:

Can you tell me the solution? I also encountered this kind of problem?

CodePudding user response:

Attention, help top it

CodePudding user response:

Because your server is too "busy",
Have updated data cached in memory first, lag in hard disk, can improve the speed of updating,
But your server has been maintained "when online around 500 per second" busy, write cache is greater than the speed of the speed of hard disk, when the cache is full, can only be updated special can write hard to clear the cache space,
It is basically in addition to use SSD and other ways to speed up the hard disk write speed, there is no better way,

CodePudding user response:

Try
Configuration to interval server configuration options
https://technet.microsoft.com/zh-cn/library/ms191154.aspx

The database should be a complete model? Regular backup log, control the log file size

CodePudding user response:

My library 0-1 PM every night is also one of the conditions, awayson environment
The 2019-11-01 00:00:44. 40 spid53s This instance of SQL Server has had been using a process ID of 1788 since 2019/5/15 20:27:31 (local) 2019/5/15 12:27:31 (UTC). This is an informational message only. No user action is required.
The 2019-11-01 00:40:52. 08 spid64s FlushCache: cleaned up 256980 bufs with 189811 writes in 115853 ms (160 new avoided dirty bufs) for db away
The 2019-11-01 00:40:52. 08 spid64s business writes per second: 1638.38 writes/SEC
Average throughput: 17.32 MB/SEC, I/O saturation: 119867, context switches 201407
The 2019-11-01 00:40:52. 08 spid64s last target outstanding: 8400, avgWriteLatency 0
The 2019-11-01 00:43:37. 92 spid64s FlushCache: cleaned up 218858 bufs with 167945 writes in 116397 ms (avoided 55 new dirty bufs) for db away
The 2019-11-01 00:43:37. 92 spid64s business writes per second: 1442.86 writes/SEC
Average throughput: 14.68 MB/SEC, I/O saturation: 129786, context switches 233082
The 2019-11-01 00:43:37. 92 spid64s last target outstanding: 48, avgWriteLatency 47