Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

in sys processes "lastwaittype " showing PAGEIOLATCH_SH Expand / Collapse
Author
Message
Posted Thursday, June 19, 2008 12:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 24, 2008 1:43 AM
Points: 12, Visits: 82
as i had told u i m facing a problem of system getting slowed .....
now frm last one week i m observing the system processes and all the
days 90 % of all the processes is either "NETWORKIO" or
"PAGEIOLATCH_SH", when i searched on net regarding this i got the
suggestion to (a) add more memory so data is held in memory longer
therefore reducing physical i/o,
now can anyone tell me how i should do it,
we have 4gb physical ram.
and some peopele adviceed me to do the
changes in boot.ini and configure 3gb swithch,,, now u tell me what
exactly i should do ?
and how?

frends pls do share ur experince with me

mithun gite


Post #519600
Posted Thursday, June 19, 2008 12:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
Ignoring memory for now...

Run performance monitor for an hour or so while the problems are occuring. Add the following counters:

Physical Disk:Avg sec/read
Physical Disk:Avg sec/write
SQL Server Buffer manager:Buffer cache hit ratio
SQL Server Buffer manager:checkpoint pages/sec
SQL Server Buffer manager:lazy writes/sec
SQL Server Latches: Latch waits\sec
SQL Server Latches: average latch wait time
SQL Server Locks: Lock waits\sec
SQL Server Locks: average lock wait time
SQL Server Access methods:Full scans\sec

What kind of average values are you seeing over the hour?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #519615
Posted Thursday, June 19, 2008 10:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 24, 2008 1:43 AM
Points: 12, Visits: 82
thanks for sparing time and replying,,,,, the reading u asked as per below...

Physical Disk:Avg sec/read>>---29552.91
Physical Disk:Avg sec/write>>---9479.805
SQL Server Buffer manager:Buffer cache hit ratio>>----99.814
SQL Server Buffer manager:checkpoint pages/sec>>----0
SQL Server Buffer manager:lazy writes/sec>>----7.646
SQL Server Latches: Latch waits\sec>>----27.393
SQL Server Latches: average latch wait time>>----391.099
SQL Server Locks: Lock waits\sec>>----0.051
SQL Server Locks: average lock wait time>>----317.687
SQL Server Access methods:Full scans\sec>>-----56.988
SQL Server Buffer manager:PAGE LIFE EXPECTANCY >>----55

now pls suggest me what should i do first?
let me give u my server configuration
its windows 2003 enterprise 32 bit server ,
4 gb physical ram,,,,
90 gb harddisk,,,

waiting for ur reply, I HAVE ATTACHED THE READING.


thanks
mithun




  Post Attachments 
PEROFRMANCE.rar (16 views, 7.70 KB)
Post #520321
Posted Friday, June 20, 2008 12:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
OK. wanna check something first. Those values are for the sec/read, not the read/sec?

If so, You have major IO problems! The memory doesn't look bad, the cache hit ratio is high and the lazy writes are low.

A value of 29 seconds on average to do a read from disk is horrifying. The recomendation is 10-50 ms.

An average write duration of 9.4 seconds is also far, far above what it should be. Recomended here is also 10-50 ms

What's your drive config? How many physical drives do you have in the server? Where is tempDB, the database's data file, the database's log file?
Do you have a RAID array or single drives? If RAID, what level?

Is SQL the only thing on this server?

The full scans/sec look a little higher than one would like. It indicates that some of your queries are doing table scans to find their data. You may need to evaluate indexes.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #520382
Posted Friday, June 20, 2008 2:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 24, 2008 1:43 AM
Points: 12, Visits: 82
hi gail shaw,

thanks again ,,,

yes Those values are for the sec/read, fine i m giving u the disk configuration now,,,

it has a physical drive of 90 gb with a raid controller 5
with the logical partition c,d,e .
drive c - capacity 19.53 gb , free-12.55 gb
drive d - capacity 23.84 gb, free-10.18 gb
drive e - capacity 24.42 gb , free-7.02 gb


our tempdb is on c drive,
our database files and log files are on d drive

still i have not used index tunning of sql server 2000,
and still its using the default memory of 2gb.

should i need to put switch of 3gb to increase the memory?

i have provided the details u want ,,,

pls let me know if anything more is required
thannks
Post #520431
Posted Friday, June 20, 2008 2:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
mithun gite (6/20/2008)

should i need to put switch of 3gb to increase the memory?


Your memory is not the issue. Your drives are the issue right now. First thing is to fix the IO slowness as that appears to be the dominent bottleneck right now.

Can you check the disk fragmentation of each drive?
How do you have a Raid 5 config with only 1 physical drive? Raid 5 requires minmum 3 drves.

What's on the E drive? Backups?

What I would strongly suggest:

Get 3 more drives (physical drives, not logical partitions)
Put the data file on 1. Put the TempDB database on a 2nd. Put the log file on a third. That should ease the IO contention.

The problem is that you have 1 physical disk, so all the reads and writes to your user db, tempDB, the windows swap file and all the system files are condending with each other.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #520442
Posted Friday, June 20, 2008 5:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 24, 2008 1:43 AM
Points: 12, Visits: 82
dear gail shaw

thanks again

but it was my mistake and i m extremely sorry that i told u that its only one physical drive ,but actualy there are 3 physical drives of 36 gb each. but we get the 80gb only for use rest gets occupied with raid controller 5.

E drive is with practice databses and one full backup file everyday come on e drive in evening and gets zipped.

now my tempdb is on c drive but both log file i.e ldf file and data file i.e mdf files are on d drive.
and there is no other application except sql on this server.

please kindly suggest me the next!!!!

mithun
Post #520532
Posted Friday, June 20, 2008 5:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
Ok, let's see if I have this straight.

You have 3 disks in a raid 5 config. The one resultant raid array is then divided into 3 partitions, c:, d: and e:

Is that correct?

If so, you still need more disks. You have the OS, the swap file, tempDB and the user database and log sharing the same physical drives. That's not recommended, to put it mildly.

Optimal solution:

Move the OS and the windows swap file off the RAID 5 array. At best the OS needs raid 1. You can get away with a single disk if you're short of cash and willing to take the risk.

TempDB should be on its own physical drive. Not sharing physical disk with anything else. You can probably get away with a single disk here as TempDB is recreated every time SQL starts, though Raid 1 is better for redundency. Depends what the uptime requirements are for the system.

If you can, convery the 3 partitions on the RAID 5 into 2 partitions and leave the data file on there and the backup in the other partition.

The log should be on separate physical disks from the data file. RAID 1 is best here. RAID 5's a bad idea for log files

If you can't afford the disks for the optimal solution, at minimum get 2 extra drives. One for the windows swap file, one for TempDB



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #520555
Posted Friday, June 20, 2008 6:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 24, 2008 1:43 AM
Points: 12, Visits: 82
thanks again,
yes i have 3 disks in a raid 5 config. The one resultant raid array is then divided into 3 partitions, c:, d: and e:

u are correct.


thanks for the solution u gave me....
at very first solution i can apply is to change the location of log file from d drive to e drive. and all the data of e drive and backup i will move to NAS ,,, so OS & tempdb will be together on C and data file on D and log file on E drive.

would it this immidiate change will benefit me a bit or not ?

and should i use index tunning wizard ? and how abt that 3gb switch?

so all these things will give me few days for other raid 1 disk configuration in server.

pls suggest me the next

and also can u give me any check list for the performance counter whiich gives the should be ideal data for the processes ,so i can check or should set some alert on that those values...

thansk
mithun gite

Post #520629
Posted Friday, June 20, 2008 7:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
mithun gite (6/20/2008)
would it this immidiate change will benefit me a bit or not ?


No. They're still on the same physical drive. c, d and e are just logical splits of the raid 5 array you have. You need to get the data file, log files and tempDB onto seperate physical drives.


and should i use index tunning wizard ? and how abt that 3gb switch?


Wait on that until the drive configutration is better. It may not be necessary.


and also can u give me any check list for the performance counter whiich gives the should be ideal data for the processes ,so i can check or should set some alert on that those values...


sec/read and sec/write should be under 50 ms
buffer cache hit ratio should be above 90%

those are the two main ones I can think of.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #520661
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse