Posts Tagged ‘IO’

SQL Server Database Performance Tuning: PAGEIOLATCH_XX is evil

November 18, 2012

Ok well is it probably not nice to pass a moral judgment on something that, through no fault of it’s own, behaves exactly the way that it was designed to behave. But with that said PAGEIOLATCH waits can be a symptom of a disk I/O subsystem issue. What this means is that SQL Server gets your wonderfully written query and first looks in memory to see  if all the data that it needs is in memory. If all the data that SQL Server needs for the query is not in memory then it has to get it from disk.

SQL Server then  reserves some space in memory for the data that it needs from the disk. It sends out a request for that data to the storage engine and waits for the data to come back from disk.

If SQL Server has to wait along time on the data then that means that it is trying to suck a lot of data through a tiny “straw” (the connection between SQL server and it precious data). In other words there is too little throughput  see Why is my DW Query so SLLLOW?!

Scenario:

The CIO walks into your cubical and says that while he was gulfing with the CEO yesterday ..err I mean in an “important meeting” yesterday. The CEO said that his business analyst are complaining that the data warehouse is  preforming  so poorly that the analyst could not deliver the reports that CEO needs for his very “important meetings”.

Using SSMS you discover that there are significant PAGEIOLATCH waits occurring on the server.

select top 1000 * from  sysprocesses
where lastwaittype like ‘PAGEIOLATCH%’

select * from SYS.DM_OS_WAIT_STATS where wait_type like ‘PAGEIO%’

image

To verify what you have discovered you open up Profiler and locate the longest running query that you can find to see if it is waiting Disk IO

image

Executing the query with:

DBCC dropcleanbuffers;
SET STATISTICS IO ON; SET STATISTICS TIME ON;

yields the following results:

Table ‘100millionFact’. Scan count 9, logical reads 2040817, physical reads 7, read-ahead reads 2040416, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 68845 mselapsed time = 154302 ms.

2040817 * 8 / 1024 = 15,943.88 MB

Total query execution time is about 2.5 minutes but the CPU is only working for about 1 minute. The rest of the time (about 1.5 minutes) SQL Server is waiting for the data from disk. It can also be derived from the results that the data warehouse is only getting about 100 MB/sec in throughput.

Another way to view the IO problem is with Performance Monitor using Avg. Disk Bytes/Read (black line)

image

Notice that while Avg. Disk Bytes/Read is high my CPU is getting bored waiting for the data from disk!!!

Conclusion:

If the CEO wants to have the data warehouse performance increased then the throughput capabilities will have to be increased.

Advertisements