Users in our SharePoint 2010 environment have had intermittent problems when uploading docs. Usually it sits trying to process until after about 5-10 minutes it goes through, and after that it's fine. The SQL admin said that the tables were being locked for that amount of time, so when I looked in SharePoint I noticed that the "Workflow" timer job was stuck trying to process and at the same time a incremental crawl was being performed. It looks like our crawls usually don't take more than a few minutes but during the time that users complain it's taking up to 25. I tried resetting the search indexing and running a full crawl but that does not seem to have helped. For the moment I have paused the crawl and that has seemed to alleviate the problems. I'm a fairly new SP admin and I haven't had much experience troubleshooting SharePoint search. Any thoughts?
It sounds like you just have too much happening at once for your SQL server to handle. It would probably be worth it to dig a bit further on your issues ( Perfmon counters, SQLIO, and maybe some profiler thrown in there).
There are a few things you can try, I'm quite sure after a couple of days you will get a few more suggestions as well.
reschedule your crawls. Monitor resources and try playing around with how and when they are running. I typically run a Full crawl weekly unless business rules dictate it more frequently, than incrementals are based on how "fresh" content needs to be. I also typically limit the times those incrementals run during the day so that they are not running say, when a backup is kicking off. If you know there are heavy "upload' times where users are actually noticing these things, then you can schedule around those too.
Do you have more than one content source? If so, ensure the crawls are not overlapping, scheduling them sequentially so that you don't have multiple crawls running at the same time.
you can lower the priority of the search crawler, this will take longer to crawl content but will use less resources.
you can configure content access rules, which allow you to define resource utilization with a bit more granularity than just the High/Low crawler setting.
Change your SQL database layout, move the search databases to a different SQL instance, Have disks added to your RAID to increase I/O, etc. Depending on the issue you may be able to increase Memory and decrease the amount of work SQL must do with the disks.
If I/O is the issue, and SQL is virtualized have the VM administrator check the resources on the host, and monitor utilization for the SQL VM. You'll have to dig deeper than just the surface monitoring and look at the logs, and Hyper-V has some perfmon counters you can use.
Make sure you don't have things like index rebuilding or other operations that impact SQL running during business hours. Check your maintenance plans (or ask your DBA) to be sure those are all done after hours. There is a SharePoint time job for rebuilding indexes, update that to ensure it only fires during off peak times for you or if you do this on the SQL side you can disable this timer job.