July 1, 2019 at 10:32 am
Hi All,
Need some advice on this performance issues which is related to I/O.
App team contact dba team that all queries are running very slow. Checked if there is any blocking, there were no blockings.
Ran sp_whoisactive and saw some spids are waiting for pageiolatch. checked with windows team to see if there are running anothing like snapshot backups or anything of that sort which is causing disk issues.
They confirmed that there is nothing from windows side. Then looked at sql server errorlog , see a lot messages now n then.
Messages from ERRORLOG
SQL Server has encountered 652 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\SQLDATA\crm_dat.mdf] in database [CRM] (22). The OS file handle is 0x0000000000002028. The offset of the latest long I/O is: 0x0002357db00000
FlushCache: cleaned up 13837 bufs with 5246 writes in 208574 ms (avoided 1512 new dirty bufs) for db 22:0
average throughput: 0.52 MB/sec, I/O saturation: 10089, context switches 26883
last target outstanding: 2, avgWriteLatency 340
FlushCache: cleaned up 5550 bufs with 325 writes in 94474 ms (avoided 436 new dirty bufs) for db 22:0
average throughput: 0.46 MB/sec, I/O saturation: 2618, context switches 7299
last target outstanding: 2, avgWriteLatency 390
FlushCache: cleaned up 3388 bufs with 203 writes in 114283 ms (avoided 912 new dirty bufs) for db 22:0
average throughput: 0.23 MB/sec, I/O saturation: 3374, context switches 8752
last target outstanding: 2, avgWriteLatency 3982
FlushCache: cleaned up 5080 bufs with 3055 writes in 375298 ms (avoided 2132 new dirty bufs) for db 22:0
average throughput: 0.11 MB/sec, I/O saturation: 14774, context switches 32316
last target outstanding: 2, avgWriteLatency 325
Some important observations
==============================
Its a windows server 2012 VM
Installed memory : 64gb
max server memory : 56GB
top wait on the instance : PAGEIOLATCH_SH 70%
From the task mgr very evident it is sqlservr.exe is driving IO under disk tab in resource monitor.
on F: drive , all the CRM database and log file is placed.
Other imp thing is, sum of all database size = 14 TB
only 56 gb is set for sql server max server memory.
Questions:
What are the possible solutions for this?
How can i tell this much memory is required for this much size of databases ? any guidance around this ?
Attaching sp_whoisactive, perfmon n waitstats screenshots.
Please provide suggestions.
Thanks,
Sam
July 1, 2019 at 10:37 am
Few questions: You say that the instance is running on a VM. Are any other VMs running on the VM host? If so, are they using the same storage pool(s) as the SQL VM or different ones? If the same, are any other VMs also IO intensive and could there be contention?Is the slowness on Read or Write requests or both, and is the storage in a RAID and if so what?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 1, 2019 at 12:00 pm
First of all I would read up on PAGEIOLATCH_SH then you should come to the conclusion that PAGEIOLATCH_SH is a soft lock on a certain DB page.
Combined with your sp_whoisactive output you should realize that some of the suspended (by the way: if it's suspended it cannot cause a blocking(if it wasn't running before it got suspended), thus you don't see any blocking on your table, yet consider that access to UAT4_ORS:3(*) and UAT4_ORS:1(*) only happens if the engine see's no lock on the table before the transaction starts, your screenshot clearly proves that there's up to 1346ms of waiting for access already happening.
There is a wide variety of possible solutions for your issue, from moving DB Files and Log Files to different (and eventually faster) volumes, increasing Memory (which we cannot say anything about it as with provided info it's a guess no more or less, have a look at Page Life Expectancy), looking at execution plans to be able to eventually have a missing index added or existing ones optimized.
How much Memory your DB needs is not that simple but I would start looking around like this:
How much of the current Buffer Pool is used by the DB? What is your biggest Index in size, does it fit within the currently available Buffer Pool for that DB? If not, I'd have a look what the desired Memory Grant for your in this regard most expensive query is and go for the smaller value of those 2 (Index Size, MG) as what your Buffer Pool should be able to provide for that DB.
Last but not least in this regard: There is no general rule how much a DB Server should have, it's highly dependent on how efficient your queries are in regards to being able to request just the right amount of memory (so no SELECT MAX(LEN(ColA)) from tblA = 5 while the column is defined as varchar(max) and things like that) for the data to be processed, therefore you can get quite different ratios.
July 2, 2019 at 11:23 am
In addition to the infrastructure suggestions, tune the queries. Ensure appropriate indexes. Keep statistics up to date. Ensure an appropriate data infrastructure. These are very common issues in and around performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply