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 26, 2008 1:55 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
TheSQLGuru (6/26/2008)

2) Page Life expectancy is too low. Need more RAM.

Except that he's on 2000 Standard edition and has 2GB allocated to SQL already.


4) Latch waits pretty high. Usually issue with too little RAM or too slow disk. Probably RAM here since disk sec/reads are 0.


Min 0, max 0.185s. Still way too high for my liking.

Some of my comments on the counter values

PhysicalDisk(_Total)\% Idle Time
Minimum - 0
Maximum - 100.177759869457
Average - 20.2911832218398

Too low for my liking. Your disks still look too busy. Disk idle time should be higher than 20% average

PhysicalDisk(_Total)\Avg. Disk sec/Read
Minimum" - "0"
Maximum" - "0.1845"
Average" - "5.62941538461538E-02"

Still too high. 50 milliseconds is a maximum recommended value, not what you want to see as an average. sec/write looks much the same.

PhysicalDisk(_Total)\Disk Reads/sec
Minimum" - "0"
Maximum" - "226.96853530398
Average" - "22.9789532722732

You're not doing a large number of reads per second, so I'm not sure why the sec/read and the idle time is so poor.

Can you please run another trace, with just the above disk counters, but separate for the different drives?

What drives do you have? SATA? What speed, what size?
Hardware RAID? Software Raid?



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 #524538
Posted Thursday, June 26, 2008 3:53 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
This server is still pegged, just not as badly as before. If you look at the charts you will see that it swings from being CPU-saturated, to being Disk-saturated and back again. To go any further in this line, I would need to see the Disk stats for each instance as I originally indicated.

Either way, I suggest you make sure that nothing else is running on this server to compete with SQL Server. Tehn start to look at the sotred procedures, queries and Jobs being executed on this system: identify the biggest ones and see if they can be improved in any way.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #524603
Posted Thursday, June 26, 2008 4:06 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
I still submit that the most cost effective thing you can do is hire a decent perf consultant for a limited tuning analysis engagement.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #524608
Posted Friday, June 27, 2008 1:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 24, 2008 1:43 AM
Points: 12, Visits: 82
oh i m very sorry ms gail as i was just not aware abt it i m extremely sorry and again thanking u .... thansk lot mam

i will be giving ur counters in few hours thanks for guiding meeee

mithun
Post #524795
Posted Wednesday, July 2, 2014 12:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 1, Visits: 41
Average IO stall in milliseconds. I have database have average 20 milliseconds on avg_io_stall. Does anyone know what is the time considered normal?
Post #1588599
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse