I often do live demos during my sessions or with customers because I find there is nothing better than to see and interact with SharePoint when discovering it.
Recently, one of the attendees at SharePoint Fest NYC looked at my demo in awe as pages appeared instantly when I clicked on a link. He quickly came up to me and asked, “How many servers do you have to be running this quick?” I met him with a smirk. I told him that my entire SharePoint environment ran on a single virtual machine on this very laptop.
The performance of SharePoint is directly linked to SQL Server
Of course, SharePoint settings and configurations do impact your SharePoint’s performance speeds. However, SQL Server is the real engine behind SharePoint. Everything is stored in your databases, the faster information can be written, stored and accessed from it the faster SharePoint will be.
The Disks and the NTFS Allocation size
Before you even install SQL Server, you will need to make sure you have the right disks. I won’t pretend to be an expert on hardware and I will let you find the best for your scenario. My personal laptop is running on SSDs. However, there is something we can do to help the speed: change the NTFS Allocation size of your drives by formatting the disks. SQL reads and writes 64K at a time, but your disk allows only 4K by default. This change alone may show you up to 30% improvement in speed.
Modifying the Model Database to increase SharePoint performance
Every time a new Content Database is created by SharePoint to store your Web Application in along with anything else you do, SQL uses the information in the Model Database to create it. Unfortunately, it is not even close to being optimized for SharePoint.
Initial Size:
The initial size of the database when it is created should take into consideration what it is being used for. With the very small default size in the single digit MB, it doesn’t make much sense in a SharePoint scenario. We know SharePoint is going to be used to store a lot more; therefore we can automatically give a larger amount of space to the database when it is created. This will help performance, as SQL won’t have to constantly ask for more space to store data.
Auto Growth:
Eventually, the database will reach the initial size with data and will need to grow. This is where we can tell it by how much it should be growing once the size has been met. Make sure to grow the size of your SharePoint databases by chunks that make sense in your environment.
You should do the same changes to your TempDB SQL Database as SharePoint also uses it indirectly through the database server to store objects.
Instant File Initialization can help speed up SharePoint but be careful
Talking about increasing those SharePoint Database file sizes, how does it actual do it? Well to make sure it can be written to as it creates or grows the database, SQL will write zeroes to validate it can write to it and then claim it. This can be a very long process depending on the required task SharePoint asked of SQL Server. Luckily, there is something that allows us to skip this process called Instant File Initialization.
The amount of time saved for these operations is incredible! However, I can’t say I would recommend this in a production environment, but amazing for dev or demo machines.
**Note: It seems the use of it in production is still debatable, I’ve had some good points made on Twitter and I invite you to research more on the implications it has. It may be worth it for production as well. One thing is for sure, the performance gains are incredible.
Logs and Maintenance of your SharePoint
Without a log file to tell the Database where it is at inside that big file, it would be meaningless data. That’s why it is extremely important to have the right recovery model for your needs.
There are two models, one with less information that will take less space and require less maintenance and the other one offering the opposite. Regardless of the model that works for you, make sure it is properly maintained and the logs truncated to make sure you don’t end up with something larger than the database itself.
The famous SharePoint Continuous Crawl for Search
Though I have recently covered the Crawled Properties vs Managed Properties, it is not enough to understand the different SharePoint crawls. A crawl is required for Search in SharePoint to pick up these properties and it to work. Though Continuous Crawl is new and awesome, it does not mean it should be activated everywhere you go.
For testing and demo purposes, I have my Search running on no schedules and run a Full or Incremental when I need it. Obviously, in a production environment you will need to properly schedule these crawls for your different Content Sources. And if you are still using the default Local SharePoint Content Source, then you are doing it wrong.
A case of the SharePoint Service Applications
Service Applications provide new features for your SharePoint platform like Search. If they do not exist or are not connected to your Web Applications then you will not be able to benefit from them. However, it doesn’t mean you need all of the Service Applications.
I only have the ones I need created and in use, unless I’m thinking of using Access and Visio services for example I do not need them to exist in my farm. This goes for the services running as well, it is not unlikely to see my SharePoint farm with many services set to “stopped”.
SharePoint isn’t just database, it’s also a lot of IIS
Explore these settings to set what makes sense for you and your current needs. For example, I sometimes set the recycling of application pools at a specific time in the early morning to make sure I can schedule a warm script soon after that.
The concerns of migrating SharePoint with Database Attach Upgrade
I believe Microsoft did an amazing job for this migration method. Compared to our previous migrations from 2007 to 2010, we can now migrate to SharePoint 2013 and leave our databases in 2010 mode. With deferred Site Collection upgrade scenario we can even do this by Site Collection, it’s great.
However, using the Database Attach upgrade scenario will not let us benefit from the new SharePoint 2013 feature “Shredded Storage” on existing content. Shredded Storage will help us save space only on future content created with SharePoint, but not the existing content in it.
Obviously, tools like Sharegate will allow you to benefit fully from Shredded Storage during your SharePoint migration. I don’t want to do a sales pitch, use whatever you want to migrate to SharePoint 2013, but be aware of the impacts it will have on your performance.
I did not find all of this information by myself; this comes from years of working with this beast of a platform and the generous help and advice for experts in the field. Edwin Sarmiento showed me everything I needed to know for the SQL Server side and I highly recommend you check out his blogs and presentations when you can. Here is a video of his presentation and explanations for many concepts mentioned above.
