Our CEO and co-founder, Brad Geldenhuys, has shared some tips and tricks for optimizing SQL. We love when Brad lets loose with his technical chops, and so will you!

=======

I work with SharePoint for a living, we have a number of clients ranging across the board from mining to professional services, retail to automotive and everything in between.  In my earlier years as a SharePoint consultant, the solution was the most important part of requirement for me and over time I have come to realize that while the solution is the end goal. If the performance of that solution is not acceptable the solution will fail.  This concept applies to all clients big and small and to everyone involved in the solution from SharePoint consultant to client.

In my experience there are 4 key items which lay the foundation of SharePoint Performance.

1. Infrastructure
2. Operating System
3. SQL
4. SharePoint Architecture

Any of the above key fundamentals are missing SharePoint will either not perform at all or very slowly, either of which is not desirable.

Today we are going to unpack SQL tricks to improve SharePoint performance, and not only that, we are going to measure the performance increase with Umlindi to get real-time results.

The Benchmark

I am using a free download of Umlindi SharePoint Protector to monitor my SharePoint environment and the current score is 40 with a page load of 281 milliseconds.

We need to get that down to as close to 0 as possible.

Parallelism

Parallelism is important and explained very well in this video https://www.youtube.com/watch?v=QkymlR4ZdIE By Brent Ozar

First things first, let’s Open the Advanced Tab in SQL Studio Manager and check to see what those defaults are set to and see if we can optimize there.

To cut a long story short we are going to up Cost Threshold for Parallelism to 50 and Max Degree of Parallelism to 4 because we have 4 cores on our DB server.

The default value for Cost Threshold is too low and the default value for Max Degree is too high so we tweak them to be just right.

Fill Factor

We are also going to change the Default index fill factor to 80 to reduce overall fragmentation’s of our databases.  We set this to 80.

I know this is a very sticky area and there are many blogs and opinions out there as to what to do for this area. However from our testing and a number of expert opinions this is great settings for SharePoint.

Benjemin Matthews on Fill Factor – http://www.benjaminathawes.com/2012/01/23/sharepoint-sql-server-fill-factor-and-index-rebuilds-a-correction/

Add backup compression

In the words of Brent Ozar  https://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/

“Free compression! If you’re using SQL Server starting with SQL Server 2008R2, you can flip a check box and get yourself some backup compression. This is a no brainer, really – backups are smaller, they take less time, and the restores even take less time. What’s not to love?”

Instant File Initialization

You won’t find a setting for Instant File Initialization in Studio Manager

Instead SQL detects whether or not the service account it’s running under has the Perform Volume Maintenance Tasks permission in the Windows Security Policy. You can find and edit this policy by running secpol.msc (Local Security Policy) in Windows. Then:

1. Expand the Local Policies Folder
2. Click on User Rights Assignment
3. Go down to the “Perform Volume Maintenance Tasks” option and double click it
4. Add your SQL Server Service account, and click OK out of the dialog.

More from Brent Ozar the SQL Master, but bottom line here is shaving off bits of performance where possible. https://www.brentozar.com/blitz/instant-file-initialization/

Now after all those changes we reboot the SQL box and lets see what happens to the SharePoint Umlindi Score if it has made any difference.

The Result

Excellent our Umlindi scores is now down to just 20 and on 89 millisecond page load time.  That’s a huge saving and an overall gain to our users on the SharePoint environment.

I have already made significant improvements to this environment with Infrastructure, OS and SharePoint tips and tricks, I will post those findings soon for you guys so stay tuned.

Get your free download of Umlindi SharePoint Protector and start improving monitoring and improving your SharePoint environment today.

=======

Want more info on the benefits of Umlindi? Check out this video!