Agree with everything Bonnie mentions. SQL max memory should leave at least 4-6GB free for the system and other processes. If you don't do that, SQL will eventually grab almost all of it, and won't release it if the OS needs it for itself or other processes.
We went through a torturous time with SP 2007 at one point (and from what I understand, many issues in that release still apply with 2010, and 2013 as well). First, try to figure out what tier is creating the bottleneck. In our case it was the SQL Server instance. No real tools available, found out by rebooting one or the other, and seeing which fixed the problem. Once we knew that, it didn't initially appear to be processor bound, since there were plenty of processor cycles available on the SQL box. But I have it from one of the SQL Server architects that SharePoint forces most operations to single-threaded. I finally figured out the problem WAS a pegged out process on the SQL instance, but it was limited to a single core. Lots of processor cycles available, but it couldn't use them. Once I knew that, I went looking for the query causing the problem, grabbed it with SQL profiler, and traced it back to a particular site collection. Based on the nature of the query, I could tell it was analyzing security config on the sites. We were creating lots of sites in the collection with broken inheritance within each site. The query was trying to resolve the rights, it was an ungodly nested query, and it was taking forever. When that query ran, it tied up our entire SharePoint environment. We ended up breaking the site collection into multiple ones, since we had no other degree of freedom to do tuning (or rearchitect security, which would have been another kind of nightmare). Once the site collections were kept relatively small, the query didn't run to exhaustion. We also had no success with Microsoft support. Problem is, we didn't find anyone on the SharePoint team who really understood the SQL back end, or how to troubleshoot it. And the SQL team didn't get SharePoint. We opened cases with both, no joy. Frank From: [email protected] [mailto:[email protected]] On Behalf Of Miller Bonnie L. Sent: Friday, March 18, 2016 1:48 PM To: [email protected] Subject: [NTSysADM] RE: SharePoint assistance? I'm not an expert by far, but when we had issues with our SP 2010 farm several years back (similar sounding issue, slow page loads), we did a RAP with Microsoft to pinpoint the major problems. After starting the RAP, they were able to help remedy some of the issues almost immediately, including the slow page loads. We are a Premier customer, so I have no idea if this is even an option for someone who is not (not sure if you are), but you might take a look. The "obvious" stuff didn't seem to be the problem for us either (CPU, memory, etc). Just thinking back and looking at my notes, a few of the main takeaways that I remember were that we had to add another spindle to our virtual FE servers and split off the Blobcache, IIS logs, and indexing locations to the new drives. We also created multiple tempdbs for the SP SQL server (we have 8 now just for SP), and tempdbs have their own spindle set (physical server for us). Also, something about setting SQL autogrowth higher to 10% and checking the max memory is set appropriately for the server. I'm sure there's more, but maybe start searching on some of those items? -Bonnie From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Damien Solodow Sent: Friday, March 18, 2016 10:51 AM To: [email protected]<mailto:[email protected]> Subject: [NTSysADM] SharePoint assistance? Anyone know of (or happen to be) a good SharePoint engineer or one of the MVPs? We have a SharePoint 2013 farm that has some definite performance problems (front/home page takes several seconds to load), and are at our wits end. We even opened a support case with Microsoft which went exactly no-where; they gathered some logs and then just closed the case and refunded it without a word. DAMIEN SOLODOW Senior Systems Engineer 317.447.6033 (office) 317.447.6014 (fax) HARRISON COLLEGE 550 East Washington Street Indianapolis, IN 46204 www.harrison.edu<http://www.harrison.edu/> ________________________________ This communication is for the use of the intended recipient only. It may contain information that is privileged and confidential. If you are not the intended recipient of this communication, the disclosure, copying, distribution or use hereof is prohibited. If you have received this communication in error, please advise me by return e-mail or by telephone and then delete it immediately.
