September 29, 2009 at 2:01 am
For some time now disk utilization is 98% for an hour or so during the working hours
total physical i/o 50,010,001,00
Sql server physical i/o 41,353,258,00
check pointing pages 41,171,00
lazy writes 48,534,00
page splits 29,003,00
page life expectancy 163,59
file system cache hit ratio 90.88
disk utilization 97,92
disk queue length 32.33
paging 3,488,00
Can someone explain to me how can i improve performances by adding more memory, faster disks....
September 29, 2009 at 6:11 am
September 30, 2009 at 12:12 am
DB size is 200GB. We have cluster server with 16GB of ram 14GB dedicated for sql and emc storage cx3-20F
September 30, 2009 at 12:15 am
irena.bulatovic (9/30/2009)
DB size is 200GB. We have cluster server with 16GB of ram 14GB dedicated for sql and emc storage cx3-20F
Okie in the first place you said disk utilization is around 98%. We need to know that what is the total size of that disk and apart of SQL files are you hosting anything else on that file like backups etc.
September 30, 2009 at 1:00 am
No it is only sql server. I have
one lun for installation files where msdb, master and one tempdb file is,
two luns for two data files
one for one transaction log file
3 for 3 temp db files
one lun for backups
September 30, 2009 at 1:06 am
irena.bulatovic (9/30/2009)
No it is only sql server. I haveone lun for installation files where msdb, master and one tempdb file is,
two luns for two data files
one for one transaction log file
3 for 3 temp db files
one lun for backups
so if its SQL Server file drive (drive which is hosting your mdfs and/or ldfs) I believe its because of the data growth and here you really need to expand the disk by adding more LUNs to this drive.
(Assuming that if its your log drive which is growing you must have a log backup in your database backup scheme?)
September 30, 2009 at 1:58 am
Of course i have log backup every 5 min and datafile disks are
install 60GB capacity 39GB free
datafile1 200GB 92GB free
datafile2 150 GB 55GB free
tlog 100GB 50GB free
tempdb1 15GB 3.2 free
tempdb2 15GB 3.2 free
tempdb3 15GB 3.2 free
backup 900GB 300 free
September 30, 2009 at 3:38 am
irena.bulatovic (9/30/2009)
Of course i have log backup every 5 min and datafile disks areinstall 60GB capacity 39GB free
datafile1 200GB 92GB free
datafile2 150 GB 55GB free
tlog 100GB 50GB free
tempdb1 15GB 3.2 free
tempdb2 15GB 3.2 free
tempdb3 15GB 3.2 free
backup 900GB 300 free
I hope and as mentioned by you these files are spread across multiple LUNs. In this case (since your data file are heavier) you can only have option to add more disks.
another thing you can do is add the secondary filegroup 9or move existing)on the another LUN so it will stop the current drive to feedup anymore.
Many people also try shrinking the database, but I really not suggest that because SQL data file are really need some free space as well. And shrinking databases are only advisable when you have proper environment knowledge.
September 30, 2009 at 3:49 am
I don't understand what i will get with adding more disks because i have enough free space but disks are busy all the time as i understand utilization of 98%
September 30, 2009 at 4:10 am
irena.bulatovic (9/30/2009)
DB size is 200GB. We have cluster server with 16GB of ram 14GB dedicated for sql[/] and emc storage cx3-20F
Hi, it may seem obvious but do you have AWE enabled on this server.
If you don't then that 14gb is not getting used and the server may be paging to disk a lot.
If it is not enabled you will need to enable it and then restart the sql service.
Seth
September 30, 2009 at 4:22 am
Of course it is. Server is working for some time now and at the beginning it was ok but as the time passes and our db is getting bigger performances are getting worst
September 30, 2009 at 4:29 am
Do you know what SQL Server is doing during the hour or so when disk utilization goes up high?
Have any other applications been installed on the server - eg anti virus tools installed by an infrastructure team?
Does it always happen at the same time each day?
September 30, 2009 at 4:48 am
irena.bulatovic (9/30/2009)
I don't understand what i will get with adding more disks because i have enough free space but disks are busy all the time as i understand utilization of 98%
Okay, I actually I thought that by utilization you are talking about disk free space.
anyway, did you check the throughput of your disk? I suggest run a counter to check the I/O, Queue length of that disk.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply