June 23, 2008 at 3:10 am
thanks lot all sirs,
first i will update my server as per mr.gails instruction and then i will rerun all the counters mentioned by all of you and lets hope then my problem will get solved and my application would run much faster then the now......
thanks lot to all you guiding me thanks.
mithun
June 26, 2008 at 12:36 am
Respected Mr. Gail shaw & RBarryYoung,
After doing the up gradation to the server as suggested by mr gail,
Here I m again giving u the avg performance counters again. And you wont believe the application has become damn fast now and users are congratulating me for it…..now users are very happy with application’s performance and even my boss is happy too…
My heartily and special thanks to mr gail as he supported me thru the end….. and also thanks to mr rbarry and the sql guru also.
Now my database file,logfile,os and tempdb all are on diff disk and the performance has improved by 80% and for me its like and achievement.
I have attached the zip file of performance counter. Pls see it and suggest me more settings to be done…
Memory\Available MBytes
Memory\Page Faults/sec
Memory\Page Reads/sec
Memory\Page Writes/sec
PhysicallDisk(*)\% Idle Time
PhysicallDisk(*)\Disk Reads/sec
PhysicallDisk(*)\Disk Writes/sec
PhysicallDisk(*)\Avg Disk Queue Length
Processor(*)\% Processor Time
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
And sir,,, I have one more req. ,, can u give me the the best avg figures for all above listed counters so I can monitor all the counters day by day and can compare the counters with the ideal one.
That’s it frm my side , will wait for u reply ,,,
Thanks a lottt
mithun
June 26, 2008 at 7:48 am
Glad performance improved. It is simply amazing how often IO is the cause of poor database performance.
My quickie review of your perfmon reveals the following:
1) Avg CPU is pretty high. Not a limiting factor yet but could be soon.
2) Page Life expectancy is too low. Need more RAM.
3) Lots of scans occuring - perform index analysis. This could help with PLE (#2) and latch waits (#4) as well.
4) Latch waits pretty high. Usually issue with too little RAM or too slow disk. Probably RAM here since disk sec/reads are 0.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 26, 2008 at 8:34 am
mithun gite (6/26/2008)
Respected Mr. Gail shaw & RBarryYoung, ...
Gail is probably too polite to point it out, but I think that you meant to say "Ms. Gail Shaw".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 26, 2008 at 1:42 pm
rbarryyoung (6/26/2008)
mithun gite (6/26/2008)
Respected Mr. Gail shaw & RBarryYoung, ...Gail is probably too polite to point it out, but I think that you meant to say "Ms. Gail Shaw".
I'm used to it. It doesn't bother me any more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2008 at 1:55 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2008 at 3:53 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 26, 2008 at 4:06 pm
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 on googles mail service
June 27, 2008 at 1:12 am
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
July 2, 2014 at 12:39 pm
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?
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply