December 28, 2009 at 12:02 pm
Hello,
We have SQL Server 2005 32-bit developer edition with SP3 on Windows 2003 standard edition 32 bit with SP2 and its a Virtual Machine with 4GB of RAM.
I have scheduled Index rebuild maintenance task job (which takes 20 mins to complete)to run on weekly basis and whenever the Index rebuild maintenance task runs I'm getting the below error in Error log:
2009-12-26 21:15:44.86 spid2s SQL Server has encountered 1373 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [d:\Program Files\Microsoft SQL Server\MSSQL\Data\Auto.mdf] in database [Auto] (7). The OS file handle is 0x0000080C. The offset of the latest long I/O is: 0x00000077290000
I have set the Max memory to 3.5 GB.
Please advice me
December 28, 2009 at 1:07 pm
After looking up the information in the Internet 🙂 seems like the problem is due to some lack of performance in your disk system. One thing that you should check is to see if you're running any kind of virus scanner which is interfering with the IO.
Carlos
December 28, 2009 at 3:04 pm
thanks,
McAfee antivirus is running on the server. Please tell me how to know wheteher Antivirus is interfering with IO?
thanks
December 28, 2009 at 3:16 pm
I believe that McAffee might monitor file changes as they occur. Try disabling it and then see if the problem continues.
Carlos
December 28, 2009 at 3:27 pm
For more information about possible performance problems and McAffee virus scanner, take a look at the Criticism section of this article on Wikipedia: http://en.wikipedia.org/wiki/McAfee_VirusScan or this review:
http://www.antivirusware.com/mcafee/virusscan/
Carlos
December 28, 2009 at 9:08 pm
Don't jump into conclusion that the problem is caused by McAfee Anti Virus, it may be one of the reasons.
Check with your systems engineer if they have exceptions enabled on Anti Virus Scan and they have exempted SQL Server files, such as .MDF, .NDF and .LDF while performing automatic scanning.
We have this issue and I had gone through almost all the solutions that are suggested online, we did not find any clue why this is happening and we currently have a ticket open with Microsoft over the same issue.
Have you identified any trend when this error occurs in your system. In our case we have it only in off hours where there is no AntiVirus scan, no backups, no ETL Job and nothing.
I may be able to share the info here when I have something solved.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 30, 2009 at 5:34 am
Unfortunately this is one of the most difficult problems to track down. I have seen this twice and even Microsoft could never tell me exactly what the problem was.
December 30, 2009 at 5:48 am
It is actually a very easy problem to track down - the disk IO is too slow (says it in the message basically)! Finding the root cause - that is another matter. 😀
Index rebuilds hit the IO VERY hard, even more so if you have as I suspect everything on one VM file which means reading the index, creating the new pages in the data file and logging it to log file all occur on same virtual disk.
1) Add .mdf, .ndf and .ldf to the antivirus exclusion list. that will take mcafee out of the picture, at least for the files themselves
2) Since this is a VM, many more things could be at fault here including having oversubscribed IO subsystem (either local or SAN based), VM file fragmenation, not having VM optimizations installed, host operations running, etc
3) what else is running while the rebuild is ongoing? backups maybe?
4) 3.5GB is too high for 4GB memory system. I would back it down to 2.5 and see if that does any better. Check for OS paging inside the VM.
5) LOTS of other things to be checked here potentially. Get a performance tuning guru onboard for a day or two and I bet many things will be identified to help speed things up.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 30, 2009 at 6:02 am
I involved Microsoft with our issue, spent days and days on this and they pointed to the back end storage. Problem was that the back end storage performance stuff showed very little use. We excluded McAfee and even turned it off. The server is idle and running an Update stats on a 2 gig db gave us the IO issue... ours was a test server.
December 30, 2009 at 6:52 am
Hi,
As previous posters have mentioned, this is a warning about the performance of your Disk I/O subsystem and REBUILDING indexes is a very expensive operation.
Can you:
1. Tell us more about you Disk Hardware please.
2. Tell us more about your current Index Maintenance routine i.e. What does it do?
You should consider optimising your index maintenance routine. For example:
- Are you rebuilding your indexes online?
- Are you rebuilding ALL indexes? If so, limit maintenance to ONLY those indexes that actually need it.
- Consider using REORGANIZE rather than REBUILD, on indexes that have less fragmentation.
December 30, 2009 at 7:30 am
I just had this same error message on a similar setup.
3 quick things:
1. my primary issue is that there were packet drops on a storage switch.
You should contact someone in your SAN group to investigate possible issues with storage.
2. There is also an issue with cpu clock drift (especially on VMs) that cause this error to appear as a false positive. You can set a startup flag to disable the check for I/O taking longer than X seconds if you determine that it's just a false pos. You might want to determine if this error coincides with real performance issues, or if it's just a false message.
http://support.microsoft.com/kb/931279/en-us
3. This issue can cause massive snowball performance issues when it's the lazy writer, like I see yours is. http://sqlcat.com/technicalnotes/archive/2008/12/09/diagnosing-transaction-log-performance-issues-and-limits-of-the-log-manager.aspx
the section "Limits of the log manager" explains that under the right conditions SQL Server will cause a suspension in log I/O until ALL the log write acknowledgements are recieved...
Good luck!
Craig Outcalt
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply