1

Short version: Is the perfmon screenshot shown below enough evidence to say "something is wrong with your storage system"?

Long version: I am debugging a performance problem: Some SQL Server query is fast on our system (even with the customer's database), and slow on the customer's system. After ensuring that the problem is not related to the SQL (same query plan, approx. same number of logical/physical reads), and ensuring that most of the SQL execution time is spent on PAGEIOLATCH_EX, I started performance monitor and ran it while the query was executing:

perfmon

Data is read from a database on E and written to a database on C. As you can see (sorry for the German-language screenshot, it's the customer's system), when the query is executed, the "Avg. Disc sec/Write" for drive C rapidly increases up to 10 seconds. This seems wrong to me, since Internet sources claim that this value should not exceed 25ms.

I'm not the sysadmin, I'm the application developer who got the complaint that the query is too slow. Does this result indicate that something is wrong with the HDD (or NAS configuration or VM configuration -- I have no idea what the system physically looks like) and I can tell the sysadmin "It's your problem, fix it" (in a friendly way, of course) without making a fool of myself? Or is there some other measurement that I should make first?

Heinzi
  • 2,138
  • 5
  • 30
  • 51
  • Note: The drives show up as "XENSRC PVDISK SCSI Disk Device" in Device Manager, which indicates that the drive is virtualized. Does that invalidate my measurements? – Heinzi Apr 24 '15 at 14:05
  • Have you had a chance to check out this [question]( http://serverfault.com/questions/383182/very-active-database-with-high-disk-times?rq=1)? Basically, it talks about how reading from the Avg. Disc sec/Write can provide some very unreliable results. Also, (i know you were not going to say this verbatim- at least I hope not) telling their sysadmin "It's your problem, fix it" like that probably isn't going to help anything. ;) – Get-HomeByFiveOClock Apr 24 '15 at 14:13
  • @Get-HomeByFiveOClock: Thanks for the link, I'll check it out. And, of course, I'll definitely use a more diplomatic wording when handing over the problem. :-) – Heinzi Apr 24 '15 at 14:18
  • @Get-HomeByFiveOClock: As far as I can see, the linked question is about "% Disc read time". Does the same apply to "Adv. Disc sec/Write"? – Heinzi Apr 24 '15 at 14:28
  • My mistake, the aforementioned question also referenced this technet article https://technet.microsoft.com/en-us/library/cc938959.aspx. There, it states the same applies to the disc sec**/Write** as well as disk sec/read. I am not aware if this is the same as Avg. Disk Sec read/write to be perfectly honest.. – Get-HomeByFiveOClock Apr 24 '15 at 14:34

0 Answers0