SQL Server performance study with HammerDBfull course
- 1. SQL Server Performance Tuning study with HammerDB – Setup SQL Server
- 2. SQL Server Performance Tuning study with HammerDB – Setup HammerDB
- 3. SQL Server Performance Tuning study with HammerDB – Using 8PP – Part1
- 4. SQL Server Performance Tuning study with HammerDB – solving ACCESS_METHODS_DATASET_PARENT
- 5. SQL Server Performance Tuning study with HammerDB – TOP 10 most costly SQL statements
- 6. SQL Server Performance Tuning study with HammerDB – Database Engine Tuning Advisor
- 7. SQL Server Performance Tuning study with HammerDB – Flashsoft and PX600 unleash the full power
- 8. SQL Server Performance Tuning study with HammerDB – Solve PAGEIOLATCH latch contention
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
Learn SQL Server basic tuning based on the 8PP approach!
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.
I will make use of Testverse as the hardware platform.
IMPORTANT!!!!! The active CPU cores have been set to 2 instead of 4.
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 OS drive Samsung 840 Basic is replaced by OCZ Agility 2 OCZSSD2-2AGTE120G.
The test SSD/PCIe devices will be installed with FOB performance in the beginning.
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”.
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.
In this case I make use of the default instance.
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.