 | |
#121
 
| |||
| |||
|
RE: High disk read activity on sql server -
11-16-2008
, 10:39 PM
|
Hi I was right in my guess in previous post: your server does huge amounts of disk accesses. Avg. disk queue length should be no more than 2 / per disk in array. 92 is a killer!. Because it can't find all required data in a cache - 92 page expectency - it moves too much data to and from disk. Just follow recommendations i gave you. your queries are pretty bad and need tuning. Try to record their text and start from frequent executed * 'reads'. Record their text and try to use DTA. It's not very efficent but what the heck to try. anyway, if you decide to extend memory, better way is to use 64 bit. 32 bit has it's own limitations and soon become obsolete. At the end of your measures, avg queue lenght should be no more than 2 per disk and life expectancy at least 350. Better even 3500. good luck. "bijupg (AT) hotmail (DOT) com" wrote: I am using sql 2005 enterprise 32 bit on failover cluster with 4 GB RAM (AWE NOT Enabled)and single raid 10 for datafiles and log files. db size 10 GB.OLTP APPLICATION and not intensive on tempdb. following are the average performance counter values Memory: Pages/sec 0.172 Memory: Available Bytes 1210 mb Physical Disk: % Disk time 1456 Physical Disk: Avg. Disk Queue Length 92 Processor: % Processor Time 5.6 System: Processor Queue Length 0 SQL Server Buffer: Buffer Cache Hit Ratio 97.2 page life expectancy=1 as page life expectancy is very low what about increasing RAM and enable AWE Option?but buffer hit ratio is 97.2 there are queries with more than 10000 reads . on average in in 30 minutes 90 queries with 10,000-15,000 reads and 6 queries with 95000 reads. out of 4GB 1.2 memory is available. pls advice "Gene." wrote: Hi As guys mentioned before, your disk access is pretty intensive for given amount of data, memory you have. There are different ways to address it and each of them will contribute to improvement: 1. where is your tempdb? is that on the same raid or not? 2. Memory is a way too small for that kind of operations. Specially if you use 64 bit, it's going to be even less efficent than 32-bit with small ram. You could still have good performance if your queries were tuned, appartently they are not. While I can advise many things, i will mention them in order of expected effect. 1. increase memory to at least 12G. Your buffer cache hit ratio should in 98 and buffer cache expected page life should be beyond 300. Untill you won't reach that, keep increasing memory. But 12 should do. 2. Check if your queries use extensively tempdb. I bet they are. So get independent phisical drive and use it for tempdb. There is a procedure in BOL on how to migrate tempb from default location. 3. I disagree with Andrew regarding cpu / disk io relation. From my experience, as soon as you start seeing high io, your cpu is going up. I am sure it is your case too. 4. Start tunning queries either manually with SQL performance tuner or with DB Tunnning advisor(DTA). Watch for 'reads' greater than 10000. In your case, i expect some to be more than 100000. But, if does not produce recommendations, do not expect that they are tuned. It only means that dta does not know how to improve it. 5. make sure that your log, data devices, tempdb - all use different physical resources - not just drive letters and use only raid 1 or raid 10. Please let me know if it helped. "bijupg (AT) hotmail (DOT) com" wrote: mY SQL SERVER 2005 is very slow especially for seelct queries and i checked the perfmon counters the values rea as follows %disk time =4000 buffer cache hit ratio =90 avg disk sec/transfer .25avg disk secs\read =0.1 current disk que lenth =140 mem pages/sec =very low disk is raid 1o with 6 disks memory 4 gb sql 2205 enterprise sp2 HP DL 380 G5 Pls advise |
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.