April 20, 2009 at 1:50 pm
Hi,
Could you please tell me for what operations in sql server 2005, High Disk Queue length will occur
Iam getting High disk queue length when
1) Full Backup is running
2) Anti virus is running
3)rebuild indexes
4)DBCC CHECKDB
Thanks
April 20, 2009 at 2:08 pm
Hi,
It may help if you could qualify what you consider to be a high disk queue and how you are measuring it.
Some details regarding your disk I/O subsystem would also be good.
Many Thanks,
John
April 20, 2009 at 2:38 pm
Many things can cause high DQL; thing you have to consider why? For example I had a system that caused high disk queue length the problem was not in SQL Server but rather in SCSI controller for the RAID array. The control was set to 90% read and 10% write cache so the Write Disk Queue Length was always through the roof. After we got that changed to 10% read and 90% write; the DQL improved dramatically.
You have to consider the raid and other counters in relation to DQL to determine if you have an issue.
THanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 20, 2009 at 3:33 pm
Thanks Mohit,
It might be SAN issue.
You have to consider the raid and other counters in relation to DQL to determine if you have an issue.
Could you please tell me the perfmon counters need to monitor to know High Disk queue lenght is related to SAN?
We have share point databases in SQL Server 2005. All the drives are sitting on SAN. What will be the Best Controller settings for Read cache and write cache?
Please advice me.
April 20, 2009 at 4:38 pm
The Cache setting are subjective but in general I think more on write then read is recommended. I am not SAN expert ... But I would start with 50/50 (depending on where it is right now) and adjust from there.
You will also want to look at the secs for the SAN from your vendor; knowing the read/write response time is import. For example in my raid the response time for reads/writes was 5ms; but I was avg 15ms. In addition I noticed my % Disk Idle Time was almost 0; indicating the disk was continuously writing/reading; indicating it is behind. It could be behind because the buffer is never empty (if it small); or it could keep moving because the disk I/O can't keep up with the through put of the database in question.
You want to look at following counters ...
Physical Disk:
% Disk Idle Time
Avg. Disk Read Queue Length
Avg. Disk Write Queue Length
Bytes Read/sec
Bytes Written/Sec
Avg. Disk Reads Sec
Avg. Disk Write Sec
Ref Articles:
http://www.grumpyolddba.co.uk/monitoring/Performance%20Counter%20Guidance%20-%20Windows%20Server.htm
http://blogs.msdn.com/sqlcat/archive/2005/11/17/493944.aspx
http://www.sql-server-performance.com/articles/per/2000io_config_sannas_p1.aspx
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 20, 2009 at 9:27 pm
Hi,
Actually We have 3 node cluster setup, having sql server 2005 EE. On node, 1 instance and node2 has 3 instances. Node1 instance has sharepoint databases and node2 has Biztalk databases.
All the drives (Including OS) are on SAN. I went through this link http://searchsqlserver.techtarget.com/video/0,297151,sid87_gci1347899,00.html#, which is saying DO NOT USE PERFMON for having drives on SAN.
We are using SQL Spotlight for monitoring, it giving alarms for High Disk Queue length on Data, Log, and Backup drive. and the alarm 'Physical disk #0 C: containing paging file(s) 'C:\pagefile.sys' is experiencing a high rate of I/O (100% busy) unrelated to paging'.
We are using Xiotech for SAN storage. Could you please suggest me what tools we can use to diagnose the IO problem other than PERFMON
Many thanks
April 21, 2009 at 2:53 am
sqlio.exe
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
April 22, 2009 at 10:32 am
hi,
Iam posting here some sample data that I got from perfmon for Avg Disk Queue length for D drive, which contains datafiles. I Selected Avg. Disk Queue Length for each drive in the server(while setting up the counters in perfmon)and the perfmon giving the Avg. Disk Queue Length value for each selected drive. Did I follow correct method? What should be the Accepted value for Avg Disk Queue Length?
eg:Avg Disk Queue length for D
0.055891196
0.021053199
0.016879892
0.006526625
0.02975981
0.015993231
0.022826521
0.018866546
0.026413164
0.02337985
April 22, 2009 at 11:06 am
I am getting High disk queue length when
1) Full Backup is running
2) Anti virus is running
3)rebuild indexes
4)DBCC CHECKDB
High disk queue lengths are to be expected as these SQL Server operations will use as much resources as available until a bottleneck is reached, which is usually disk thruput. Only if the actual execution time of these operations exceeds the service level time window allowed, should the disk queue length be of concern.
You should have the anti virus configured to exclude SQL Server database files.
A few configurations to check:
Ensure multiple HBAs are teamed (not configured for failover) Teamed HBA cards will provide twice the bandwidth, but if configured for failover, one HBA is not being used and is a hot standby.
Enable SAN Write Caching if SAN controllers have a battery backup (which most have), ensure that database is not waiting for disk commits, but only cache commits. If possible allocate almost all cache for writes. For some early SANs, if any cache was allocated to reads, database corruption would be reported.
Set the “Queue Depth” (or “Execution Throttle”) on the HBA somewhere between 64 to 128 and insure that HBAs on other servers using the same SAN are also adjusted or one machine may dominate the SAN.
Database backup and log backup files should be on a different SAN than the database files because if the SAN housing the database files is lost and the backup files are on the same SAN, you will have data loss.
SQL = Scarcely Qualifies as a Language
April 22, 2009 at 12:24 pm
One point that immediately comes to mind is have you configured your antivirus software to not scan your SQL Server data files?
April 22, 2009 at 1:34 pm
thanks,
I asked the Network team to exclude data and log (mdf n ldf files) files from Antivirus scan. But they are telling the Anti virus is running From a separate dedicated server, which will scan whole all production servers and its tedious task to exclude data and log files.
If it is OK for DBAs, they are ready to uninsatll the Anti virus?
please advice me
April 22, 2009 at 1:48 pm
It is quite common to not have antivirus software running on your sql server box provided the box itself is secured adequately within a securely configured network.
This requires that your sql server box must not be directly accessible via the internet and is behind a Firewall that is subsequently behind a DMZ. Access could be via web servers for example, that would be located on seperate subdomains to the DB servers and possibly behind an alternative Firewall.
Whether or not it is appropriate to remove antivirus software from your sql server box will be dependent on the network architecture of your entire platform.
Another plausible alternative could be to disable the network level antivirus software from including your sql server box in its scan and to then install separate antivirus software directly on the sql server box. This way it can be configured appropriately
April 22, 2009 at 3:45 pm
You should tell the Network team to configure the antivirus properly for all the servers, for example, for SQL Servers It must not scan the database files, but for Domain Controllers for example, they need to exclude other folders and so on.
In my case, every server has an antivirus installed, but all servers depends on one server to send the updates, scan schedules, etc, and in the "admin server" for the antivirus we can configure the folders to exclude in all servers and schedule different scan times for every server, to scan only on the weekends.
Regards,
Jose Oyervides.
April 22, 2009 at 8:12 pm
madhu.arda (4/22/2009)
thanks,I asked the Network team to exclude data and log (mdf n ldf files) files from Antivirus scan. But they are telling the Anti virus is running From a separate dedicated server, which will scan whole all production servers and its tedious task to exclude data and log files.
If it is OK for DBAs, they are ready to uninsatll the Anti virus?
please advice me
Wouldn't be a good idea to scan your database files, you should be very cautious to allow any third party applications scanning your database files, there could be possibility that it might mess up your files, just ask them to exclude the database files and that is the way to go.
April 22, 2009 at 8:40 pm
Regarding antivirus, there are a lot of files and directories that needed to be excluded when running a cluster.
http://support.microsoft.com/kb/822158 and http://support.microsoft.com/kb/250355
Antivirus software that is not cluster-aware may cause unexpected problems on a server that is running Cluster Services. For example, you may experience resource failures or problems when you try to move a group to a different node. I have also heard that theremay be SQL Server start up failures because the anti-virus software has the database files open.
Excluding these files from the antivirus scan is necessary for smooth operations.
SQL = Scarcely Qualifies as a Language
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply