Based on a project with Thomas Kejser last year I started a new blog project to showcase a simple SQL Server performance tuning study. The target is to show some basic tuning options you could use to improve the SQL Server performance. I will make use of the 8PP to follow a scientific approach of tuning. But a database system like SQL Server 2014 SP 1 is a complex environment and I will cover only a few topics of the full monitor and performance tuning SQL Server provides.

The Use Case

Because I don’t have a good real world workload to tune I will use the tool HammerDB instead to generate a TPC-C workload. The focus of this study is the tuning itself so it’s not important which workload I use. I agree that the bottlenecks I will encounter may not represent real world workload ones.

The Setup

The reason for this is that I believe there will be a point in time where the CPU will be a bottleneck. Then I will have a chance to show what influence more CPU power will have.

All tests will run on this machine so no network or other systems should be involved. Testverse is connected to the LAN and I will use RDP while running the tests. I keep an eye on the network part and consider this has little to no influence to the testing.

The test SSD/PCIe devices will be installed with FOB performance in the beginning.

Operating System:

Windows 2012 R2 DataCenter Edition with all actual patches are installed (24.08.2015). Automatic updates will be deactivated for the test period. The Samsung EcoGreen F4 HDD is formatted with NTFS v3.1 with 512 bytes per Sector with 64k NTFS allocation unit size.

SQL Server 2014 SP1:

The SQL Server will be installed right now. The instance root is set to the Samsung EcoGreen F4 HDD (D:\}. One Requirement is .Net Framework 3.5 SP1 which can be installed via the “Add Roles and Features”.

Feature selection

I select only the features which are really needed for this study. So basically its the “Database Engine Service” and the “Management Tools”. Remember I changed the instance root to the D:\ drive.

Instance Configuration

In this case I make use of the default instance.

Server Configuration

I enable all SQL Server services and set the “Startup Type” to Automatic. The agent and browser services maybe used later.

Database Engine Configuration

I decided to use the Mixed Mode. Last time I used HammerDB it has been difficult to go with Windows authentication mode. The local administrator group will be SQL Server administrators as well. This is not best practices but will work.

Let’s check with the SQL Server Management Studio if we are able to connect and run a simple select.

Okay. SQL Server is up and running with the default configuration. I did not make changes to the default settings of SQL Server 2014 SP1 to make sure this test can be easily reproduced.