Otak-otak IT: Optimizing and Improving SharePoint Performance from SQL Server

So, there is some ways to optimize and improve SharePoint (any edition) from SQL Server. There’s some way to improve this SharePoint things, and from SQL Server is one another way to do that. The important key is, where is your MDFs (MDF is the file extension for SQL Server Data File) and LDFs (LDF is the file extension for SQL Server Log File) located? What happens if I lost my MDFs? Or what happens when I lost my LDFs? What path should I take to make SharePoint much faster?

We’re going to do some experiment to make SharePoint performance much faster. Actually I remake this scenarios from other source, and write on my best words. I have 3 databases, and 2 different disks (C Drive and R Drive). One disk is much faster than the others (C is faster than R). Those 3 databases are in different MDF and LDF file placement, but the same configuration. One database, MDF and LDF both in a faster drive, named “TestFASTFASTDefault”. Another one, MDF is in a faster drive and LDF is in a slower drive, named “TestFASTSLOWDefault”. Another one, MDF is in a slower drive, and LDF is in a faster drive, named “TestSLOWFASTDefault”. Which one will be the winner in query? Let’s find out.

This is the Harddisk benchmark using CrystalDiskMark, it’s free, you can download it. See how much is the difference.

Then I create a table for testing, in every database of 3, using this script, just default with one row.

USE [TestFASTFASTDefault]
--change this to another DBName, there are TestFASTFASTDefault,
--TestFASTSLOWDefault, TestSLOWFASTDefault.
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_DUMMY](
[DUMMY] [nvarchar](max) NULL
) ON [PRIMARY]
GO

And after that, insert some dummy data to test how much the time is.

USE TestFASTFASTDefault
--change this to another DBName, there are TestFASTFASTDefault,
--TestFASTSLOWDefault, TestSLOWFASTDefault.
GO
DECLARE @TEXT AS NVARCHAR(MAX)
SET @TEXT = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
SET @TEXT += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

DECLARE @I AS INT
SET @I = -1

--DELETE [dbo].[TBL_DUMMY]

WHILE (@I <= 10000)
BEGIN
INSERT INTO [dbo].[TBL_DUMMY]
([DUMMY])
VALUES
(@TEXT);
SET @I += 1;
END
GO

And these are the result for inserting to those 3 databases,

Scenario	DBName	MDF Loc.	LDF Loc.	Result (sec.)
1	TestFASTFASTDefault	C	C	7s
2	TestFASTSLOWDefault	C	R	11s
3	TestSLOWFASTDefault	R	C	6s

And another scenarios is selecting the records, and below is the query scripts,

USE TestFASTFASTDefault
GO
--change this to another DBName, there are TestFASTFASTDefault,
--TestFASTSLOWDefault, TestSLOWFASTDefault.

SELECT SUBSTRING(DUMMY, 3, 10) + SUBSTRING(DUMMY, 50, 10)
FROM TBL_DUMMY
ORDER BY DUMMY
GO

And these are the result for selecting from those 3 databases,

Scenario	DBName	MDF Loc.	LDF Loc.	Result (sec.)	Result after cached (sec.)
4	TestFASTFASTDefault	C	C	7s	2s
5	TestFASTSLOWDefault	C	R	7s	2s
6	TestSLOWFASTDefault	R	C	7s	2s

So, you can see how much the difference to put the MDF and LDF in a different hard disk. You can try it yourself and find the best options for your server environment. I’m not pushing you to put the LDF on a faster drive and MDF on a slower drive, but it’s recommended. PUT THE DRIVE ON THE RIGHT PLACE, SEPARATE THE MDF AND LDF LOCATION, PRIORITIZE THE LDF ON THE FASTER DRIVE. Why? SQL reads data from LDF to MDF sequentially while MDF is not. The frontliner of data is LDF, so when you first initialize a query to the SQL Database, first time will reads LDF, and then read the data sequentially and put it on the LDF for much faster result next query time.

Another tips is that you can change the settings of your database to the most optimal configuration. Bigger “File Growth” and “Initial Size” is always better. Imagine that if you are the teacher, and you are opening the class for SharePoint, and your teaching room is small, maybe just 10 chairs. First time, you won’t get much students, let’s say just 7. And next week, your students growing a lot by number, let’s say 5 more students are in. So you must prepare and (maybe) relocate your teaching room to a bigger capacity. Next week, more and more students are in, and you maybe starts frustate yourself for relocation. Cost much time and headache right?

But, if you could use a bigger room, and much chairs in the room, maybe about 100 chairs, you don’t have to relocate as soon as possible. Maybe next time about once a month, or once per 2 months? That’s better right, and save time to prepare the room.

So, the second tips are, CHANGE YOUR MDF AND LDF FILEGROWTH BIGGER THAN USUAL, if you are dealing with the big database like in SharePoint, maybe you would like to set all of the SharePoint database to a better performance. My guess is, if your company or you, will using SharePoint to put a big file in it, my suggest is 100MB or 200MB filegrowth.