In Part 1 of this blog series, we outlined the importance of creating a good starting point for performance tuning on your SQL Server by establishing a baseline for your server’s current performance.
In Part 2, we covered how to enable Instant File Initialization and adding additional TempDB files to your TempDB database.
As a conclusion to those articles, we will look at the effects of those changes by running a simple GP2010 Sales Order processing batch posting. To run the test, I create a virtual server with SQL 2012 using the default setting and GP2010 SP2. I made one change to the default to limit SQL server to only use a max of 12GB of RAM since I was running GP2010 and SQL on the same image.
For testing, I created one Sales Ordering Processing batch with 10,524 Invoices containing 31,323 lines of transactions.
In test 1 – No SQL optional settings were changed
- Instant File Initialization is disabled
- One Tempdb
The posting of the batch to the General Ledger took 7 hours to complete.
For test 2, I restored the company’s database back to its original state before test 1 and then made the following SQL optional settings changes:
- Instant File Initialization is enabled
- Tempdb data file change to have 1 data file per CPU core (4 files)
The posting of the batch to the General Ledger took 6 hours to complete.
This was a very simple test on a virtual server with only one hard drive. Imagine the system performance gains when you apply all of the ideal SQL performance tuning recommendations!
