Sqlservr.exe Using too much disk time
We have a client with a SBS 2008 server that is getting the following SNMP event:
Monitor Set: high.driveperformance.coun
Type: Counter
Log Object Name: Logical Disk Drive C % Disk Time
Agent Alarm Time: 12:46:27 pm 17-Oct-13 UTC
Event Time: 12:46:27 pm 17-Oct-13 UTC
Log Value: 2358.000244
Alarm Operator: Over
Alarm Threshold: 85 % Disk Time
Alarm Duration: 30 Minute(s)
When I run HDTune to watch the disk usage, there are 3 instances of sqlservr.exe all using constant disk time. I have already tried to limit the memory usage in all the instances of SQL (using the management console) to 200-500MB. After doing so, the memory usage went down but the disk time did not. the instances of SQL are SBSMONITORING, The Active directory (MICROSOFT##SSEE) an the other i can't identify directly. Its the first two in that list that are taking all the disk time.
Any Suggestions?
Monitor Set: high.driveperformance.coun
Type: Counter
Log Object Name: Logical Disk Drive C % Disk Time
Agent Alarm Time: 12:46:27 pm 17-Oct-13 UTC
Event Time: 12:46:27 pm 17-Oct-13 UTC
Log Value: 2358.000244
Alarm Operator: Over
Alarm Threshold: 85 % Disk Time
Alarm Duration: 30 Minute(s)
When I run HDTune to watch the disk usage, there are 3 instances of sqlservr.exe all using constant disk time. I have already tried to limit the memory usage in all the instances of SQL (using the management console) to 200-500MB. After doing so, the memory usage went down but the disk time did not. the instances of SQL are SBSMONITORING, The Active directory (MICROSOFT##SSEE) an the other i can't identify directly. Its the first two in that list that are taking all the disk time.
Any Suggestions?
In my opinion is not much you can do about it in particular considering "the instances of SQL are SBSMONITORING, The Active directory (MICROSOFT##SSEE) " are using the disk(s) because the constantly read/write therefor disk IO. AD SYSVOL directory share is used to distribute scripts and files that support Group Policy to client computers on the network.
As far as indexes going...I would 100% agree if these would be "user" server/databases but as ther are related (backend) for SBS and AD I don't think there are much indexes that can help reduce the IO.
As far as indexes going...I would 100% agree if these would be "user" server/databases but as ther are related (backend) for SBS and AD I don't think there are much indexes that can help reduce the IO.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I Used the pest practices analyzer to the problem down to the SBS monitoring service using all the disk time. I ended up using HD tune to look at the disk usage graph as I shrunk the DB, limited its memory then gave it more memory. turns out that the SSEE DB had a 32 GB log file. I followed the steps at http://www.satheesh.net/2011/04/05/how-to-shrink-sql-sharepoint-database-log-files/ to shrink both the Sharepoint DB and the SBSmonitoring DB. the log file of the SSEE (the one that had sharepoint in it) shrink to 50 MB and saved some disk time and C:\ space. the sbsmonitoring however, was not that easy. I tried rebuilding it according to http://blog.korteksolutions.com/the-sbsmonitoring-database-is-nearing-maximum-size-on-sbs-2008/ but would not work. I then found the i can just stop the SBSmonitoring service as we use kaseya anyway. When I did that, HD Tune showed me that the Disks where being Zerg Rushed with data from sbsmonitoring. the Server is now at least a million times faster! :)
Hi @dbzmaxx
I am glad that you got that nailed!! :)
and thanks for sharing the steps you did to resolve it really appreciate it!
I am glad that you got that nailed!! :)
and thanks for sharing the steps you did to resolve it really appreciate it!
Database servers should generally be given more memory and not less - that helps them cache in memory and raise performance without hitting the disk so hard.
By limiting memory, you're effectively forcing it to swap to disk and that's going to do exactly what you're describing.
Another way to boost performance is to add indexes that play into the kind of queries you're doing. Indexes use disk space, but saving time on table scans is always good.