How to improve your SharePoint performance

The speed at which SharePoint operates and its overall performance can be greatly improved by improving the underlying data engine, i.e. SQL Server.

Your SharePoint configuration and SQL Server

The overall performance of your SharePoint farm is dependent on its configuration and settings, although since SQL server is the underlying data engine upon which SharePoint is built, the  faster the information can be written, stored and accessed, the faster the performance of SharePoint.

The Disks and the NTFS Allocation size

In order to enhance the speed of SharePoint, especially when you have a traveling demo VM, you need the required disk size, although if your laptop’s disk size is smaller than the required specifications, it can still be modified to enhance SharePoint performance. This can mostly be achieved by firstly formatting the disk and then changing the NTFS allocation size on your drives. By default, your disk reads and writes 4K at a time, however, SQL reads and writes 64K, which is far greater. Changing the allocation size in NTFS on your drive alone will boost your performance by up to 30%.

Modifying the Model Database to increase SharePoint performance

The SQL utilizes the information in the model Database for creating new databases.  You would think that SharePoint would use the model database, but it actually does not.  As a result, when a new content database is created, by default, the auto grow settings of the database is set to 1 MB at a time and as any SQL DBA worth his salt will tell you, a SQL grow operation is by far the most expensive operation in SQL.  As a result, imagine your content database being full and someone uploading a 100 MB file.  That will require the content database to grow 100 times.  Not good for performance at all.  So always go and check your grow settings after new content databases have been added.

Initial Size

When a new database is created, it is very important to take into consideration its initial size and the purpose of its creation. A larger amount of space should be allotted to a new database when they are created, in order to prevent SQL from constantly asking for more spaces to store data. This will certainly help the overall performance of SharePoint i.e. avoid grow operations as explained in the previous section.

Auto Growth

In the long run, when the database is filled there will be the need for it to grow, you can specifically allocate the size you want it to grow into. Make sure to grow the size of your SharePoint databases by chunks that make sense in your environment. Don't forget the TempDB.  Remember, every write goes through the TempDB first before it goes to your DB!

Instant File Initialization can help speed up SharePoint but be careful

Instant file initiation allows SharePoint to skip certain long processes during the initiation of sizing and the auto growth processes. Talking about increasing those SharePoint Database file sizes, how does it actually 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. Performing the instant file initiation will help in saving time and increase the speed of operation. This is especially true for demo machines, but beware that the space must be available.  You do NOT want to run out of space!