Improving your SQL Server’s performance – Part 3

 

In Part 1 of this blog series we talked about the need of create a good starting point for performance tuning on your SQL Server is by establishing a baseline for your server’s current performance.

In Part 2 we talked about enabling Instant File Initialization and adding additional TempDB files to your TempDB database.

As a conclusion to those posts, 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. Below are the specs of the server:

image

For testing I create 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 restore the company 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 to your server!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s