Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are experiencing high volume of PageIOLatch_SH waits on our database (row counts in the Billions). However it seems that our drive Idle time Percentage hovers around 50-60 percent. CPU usage is nill. The Database Tuning Advisor gives no suggestions for optimization. The query plan (actual) from the single stored procedure used on the database puts the majority of the expense on index seek (yeah I know these should be optimial) operations.

Anyone have suggestions of how to increase throughput?

share|improve this question
1  
Can you give us more details on the IO subsystem, DAS, SAN, iSCSI etc. Bandwidth to it, disk queue lengths. –  Andrew Nov 15 '11 at 17:23
    
Are the estimated row counts lower than the actual row counts? –  Gonsalu Nov 15 '11 at 18:38
    
Any SQL or Windows error log messages? What is the full plan too please? Last index rebuild/statistics update? –  gbn Nov 15 '11 at 19:15
    
I believe the read queue seemed a little high on the non idle drives when the idle spikes down to 0 (short term). No error messages in windows –  Marty Trenouth Nov 15 '11 at 19:43
    
not much in the way I can give you about Subsystem besides DAS. –  Marty Trenouth Nov 15 '11 at 20:13
add comment

migrated from stackoverflow.com Nov 15 '11 at 18:03

This question came from our site for professional and enthusiast programmers.

1 Answer

Is there memory pressure on your server? The buffer pool may be filling up causing a lot of paging out to disk.

SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE [object_name] = N'SQLServer:Buffer Manager'
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);

-- Is this a NUMA system?
SELECT
  CASE COUNT(DISTINCT parent_node_id)
     WHEN 1
         THEN 'NUMA disabled'
         ELSE 'NUMA enabled'
  END
FROM sys.dm_os_schedulers
WHERE parent_node_id NOT IN (32, 64);

Also, check the number of rows returned and the hierarchy in your where statements. You may be returning more rows than you require.

share|improve this answer
    
Memory seems more than sufficient (124GB). –  Marty Trenouth Nov 15 '11 at 19:39
    
@MartyTrenouth I've edited a query in to StanleyJohns answer to check PLE and just in case, to confirm if this is a NUMA system. Can you add the output to your question please. –  Mark Storey-Smith Nov 15 '11 at 19:45
    
Page Life 717, NUMA disabled –  Marty Trenouth Nov 15 '11 at 19:45
1  
That's low, very low for a 124GB buffer pool. –  Mark Storey-Smith Nov 15 '11 at 19:48
    
Agree 717 is low PLE by anyone's standards –  gbn Nov 15 '11 at 20:00
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.