Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a a SQL Server 2012 (SP1) #cu5 (X64) instance used as SharePoint 2010 back end and for the most part use only 10 - 30% CPU but there is a high CPU usage when running FAST search crawl(both incremental and full ).

Following Microsoft recommendation for SharePoint SQL instances Auto Create and Auto Update Statistics are turned off and SharePoint is supposed to take care of statistics and index maintenance using health analyzer rules and timer jobs. For the most part it does ok job but up on investigating the queries creating high CPU usage I found out there are missing statistics but i cant create them since modifying SharePoint databases will make them unsupported.

Please let me know if you have been in a similar situation and any helpful advice will be appriciated. Also let me know if you think http://sharepoint.stackexchange.com/ is the best place to post this instead of DBA SE.

SQL Server Environment: HP ProLiant DL385p Gen8, Microsoft Windows Server 2008 R2 Enterprise SP1, AMD Opteron(tm) Processor 6204(single Socket, 2 Core, HT enabled) Microsoft SQL Server 2012 (SP1) #cu5 (X64)

SharePoint Environment(I don't have much detail on this right now): SP 2010 SP2(2 app server, 2 Web server, 1 FAST search server)

share|improve this question
1  
You've correctly identified both the problem (missing stats) and the forbidden fix. I've had this discussion with SharePoint teams also. The best thing you can do is open a case. I've observed MS will make all kinds of tweaks to SharePoint if they're the ones doing the tweaking. –  Eric Higgins Dec 12 '13 at 6:27
    
Thanks Eric. Will do but before I joined here I think they opened a ticket with MS for the same issue and they weren't helpful at all. –  DaniSQL Dec 12 '13 at 15:15
    
Was this issue ever resolved, and if so - what was the fix and the root cause? –  user34760 Feb 25 at 10:24
    
Rich - yes it is resolved. We have done multiple things in parallel so its hard to tell what exactly fixed it. We applied latest service pack and CU on all servers including SharePoint and SQL, started maintaining indexes and updating stats manually in addition to SharePoint. Then run the full crawl couple times and problem went away. –  DaniSQL Feb 28 at 19:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.