June 19, 2008 at 12:11 am
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
June 19, 2008 at 12:39 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2008 at 10:33 pm
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
June 20, 2008 at 12:45 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2008 at 2:09 am
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
June 20, 2008 at 2:33 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2008 at 5:12 am
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
June 20, 2008 at 5:37 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2008 at 6:54 am
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
June 20, 2008 at 7:28 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2008 at 8:13 am
The server is obviously underpowered for what it is being asked to do. Period.
Having said that, I would go ahead and try the /3GB switch IF you are using Enterprise Edition (which I am pretty sure you aren't since if you could afford that you would probably have a beefier server in the first place). Std edition SQL 2000 can't use more than 2GB of RAM.
If you COULD allocate more RAM to SQL server that would help with the IO problem some because more stuff could stay in RAM. Since you can't do that (and even if you could) it is time to start looking at ways to reduce your IO load. You need to start tuning your queries and looking for indexing opportunities. It sounds like you are not an experienced DBA and I would bet you do not have one on staff. Therefore my strong recommendation is to have a professional help you with tuning your database application and server while mentoring you (or the appropriate person) on how to be better in the future in your interactions with SQL Server.
Re the networkio issues, are you on gigabit networking? If not, get there. That is a cheap and easy low-risk implementation to address that problem. Also this could again be caused by poor data structures and poor code, which brings me back to getting pro help.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 20, 2008 at 10:19 pm
hi Gail Shaw,
fine then i will stregithway upgrade the physical disk with extra two raid one right, and then again i will take those datas and will get back to u.....
thanks a lot ,,,, it was really nice of uuuuuu
thanks
mithun
June 21, 2008 at 12:06 am
hello mr sql guru,,
u r pretty right that i m not much experienced dba but that does not mean that i can not write or share my problems with u people , everyone is fresher when they start anything new , sharing ur ideas and experience is never a bad idea.
and yeah u are asking me to go to some specialist , thats what i m doing by putting my problem here i had read all these in books but i didnt applied it straight way , i wanted experts opinion and i consider u people the most expert.
>> my server is of standard edition so u saying cant i put 3gb switch in that?
>> and also teach me that how to be better in the future in my interactions with SQL Server?
thanks
mithun
June 21, 2008 at 8:51 am
Rerun Performance Monitor with the same counters that Gail listed, plus these addiitonal:
Memory\Available MBytes
Memory\Page Faults/sec
Memory\Page Reads/sec
Memory\Page Writes/sec
The following should have "*all instances" set:
PhysicallDisk(*)\% Idle Time
PhysicallDisk(*)\Disk Reads/sec
PhysicallDisk(*)\Disk Writes/sec
PhysicallDisk(*)\Avg Disk Queue Length
Processor(*)\% Processor Time
As most folks cannot read a *.RAR file, I would suggest using *.ZIP instead.
[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 21, 2008 at 9:49 am
mithun gite (6/20/2008)
fine then i will stregithway upgrade the physical disk with extra two raid one right, and then again i will take those datas and will get back to u.....
Great.
Once you've got the new array in place and the files moved, run all the perfmon counters again, including the ones that RBarryYoung asked for, and let's see what improvement we have.
my server is of standard edition so u saying cant i put 3gb switch in that?
You can use /3gb, but it will have no effect on the memory available to SQL. If you're using SQL 2000 standard edition you're limited to 2 GB memory. It's a limitation in the version and no usage of awe or /3gb will help you increase that.
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
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply