Suddenly database IO(40 MB/sec) going very high

Question

Hello Everyone,

One of our organization SQL server  database IO(40 MB/sec) going very high without any changes in database, past few days everything working fine but now getting in problem and wait type getting IO_COMPLETION in every running task..

getting any problem if 2 network card bridging or timing at OS level...

its very tedious job to identify.

Plz suggest me to resolve my problem.....

thanks in advance

Answer 1

Hello,

Are these read or write operations or both? Have you checked the currently running SQL statements? What about other performance counter like Page Life Expectancy (PLE) Counter

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

check this blog - http://www.sqlskills.com/blogs/paul/causes-io_completion-write_completion-sql-server-wait-types/

IO_COMPLETION

• Reading log blocks from the transaction log (during any operation that causes the log to be read from disk – e.g. recovery)
• Reading allocation bitmaps from disk (e.g. GAM, SGAM, PFS pages) during many operations (e.g. recovery, DB startup, restore)
• Writing intermediate sort buffers to disk (these are called ‘Bobs’)
• Reading and writing merge results from/to disk during a merge join
• Reading and writing eager spools to disk
• Reading VLF headers from the transaction log

Do you have log files in seperate disks? Check the disks where log files are placed and check the log files properties. Check if any of the above operations are happening more recently.

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 3

hello Olaf,

both read and write operations.

everything is fine, but its happen suddenly..

PLE is more than 300..

but one counter increase instantly i.e. paging file is  7, paging file set according to server RAM...

how its possible to increase paging file... not operation perform through the network like copying and pasting a file on disk, than how it possible to increase a paging file...

Answer 4

PLE above 300 does not means its good value PLE of 400 on 40 G RAM could be very bad value. Plus if you have NUMA system avoid looking at PLE it would show incorrect value instaed look at PLE for each NUMA node.

IO_Completion can have couple of reasons my bet is queries are doing scans which ideally should have done seek and thus causing issue. You need to find out root cause. Ask storage team to check disk speed as well

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My Technet Wiki Article
MVP

Answer 5

PLE is 98000...

buffer cache hit ratio is 100....

What to do next if buffer cache hit ratio is high....

Answer 6

Brent Ozar has a great procedure to run exactly when it happens to see what is going on

http://www.brentozar.com/askbrent/

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence