PAGEIOLATCH_EX and Blocking

Question

PAGEIOLATCH_EX and Blocking

Robin35

SSCertifiable

Points: 6471
More actions
April 2, 2014 at 1:16 pm

#305907

Hello,
Last week one of my application team member complained saying that the application is behaving slow when he is running some reports which uses the database that I support.
I looked at the activity on the instance through our monitoring tool Idera SQL Dm and i can see a session doing bulk inserts is having PAGEIOLATCH_Ex waits and the wait time is varying by time...for example around 5 PM wait time is 6 ms, around 5:30 PM it is 9 ms...and i can also see frequent blocking sessions by same spid ( same spid is blocking itself ).
environment :
SQL Server 2008 R2 enterprise edition
windows 2008 R2 Enterprise
Memory : 48 GB
CPU : 24
Can anyone please help me on troubleshooting this issue ?
Thanks in advance

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

The reason that you are seeing the SPID blocking itself is because the process is running in parallel. The PAGEIOLATCH is waiting for a data file page to be read into memory from disk. This could be due to your I/O subsystem, memory pressure or CPU issue. I would start with your disk subsystem and rule that out first.

Look for latencies in your I/O subsystem with sys.dm_io_virtual_file_stats

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Robin35 SSCertifiable Points: 6471 More actions · Answer 2

Thanks Keith...

I looked at the IO subsystem and its suspicious....i have queried using sys.dm_io_virtual_file_stats and i can see avg. disk ms/read is high on D drive > 28.

Please let me know how to proceed with this ?

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 3

What kind of storage do you have i.e. SAN, locally attached, etc.?

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Robin35 SSCertifiable Points: 6471 More actions · Answer 4

We are using EMC VMAX storage. Also, i can high index fragmentation on some indexes...do you think that would be one of the cause ?

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 5

Do you have a storage admin to go to? and have you and the admin followied the EMC best practices? http://www.emc.com/collateral/white-papers/h12341-sqlserver-bp-wp.pdf

I would start with their recommendations (which are usually very good) and go from there.

As for the report are there indexes you can add to cover the report? This may help get the data back to the report user quicker and are you using Snapshot isolation (either RCSI or SI)? That could also help.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

Robin35 (4/3/2014)
We are using EMC VMAX storage. Also, i can high index fragmentation on some indexes...do you think that would be one of the cause ?

How frequently do you do index rebuilds and update stats ? Also check for any missing indexes that would speed up those reports.

--

SQLBuddy

Robin35 SSCertifiable Points: 6471 More actions · Answer 7

Our maintenance are scheduled to run every sunday around 5 AM

Thanks

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 8

Robin35 (4/3/2014)
Our maintenance are scheduled to run every sunday around 5 AM
Thanks

Is it possible to run index reorg & update stats on nightly basis and rebuilds during the weekend ? May be it can have some better performance improvement.

--

SQLBuddy

Robin35 SSCertifiable Points: 6471 More actions · Answer 9

sqlbuddy123 (4/3/2014)
Robin35 (4/3/2014)
Our maintenance are scheduled to run every sunday around 5 AM
Thanks
Is it possible to run index reorg & update stats on nightly basis and rebuilds during the weekend ? May be it can have some better performance improvement.
--
SQLBuddy

Thank you very much .....

We thought of doing that, but we recognized an index which was created a week back and it is the culprit. After we dropped the newly created index the performance is good.

I appreciate everyone for your help.

Thanks