SQL Server 2014 vs 2012 - performance test

sql2014 vs sql2012 perfWhat do you expect from a new version of software? New features? Enhanced usability? Defect fixes? Better security? Higher performance? All of them? For sure, SQL Server 2014 comes with some new features, mostly for database administrators. I was interested in how performance of the database engine has changed. I created a performance test scenario, executed it on SQL Server 2012 and 2014 and compared the results. If you would like to find out if Microsoft improved performance of SQL Server engine for simple INSERT, SELECT and UPDATE statements, do not hesitate and read this article.

 

If you currently use SQL Server 2012, probably you have already wondered, what you could gain by upgrading to SQL Server 2014. If you currently have performance issues, is there a chance that SQL Server 2014 can solve them just by upgrading the database? Does SQL engine performs better in the newest release? Lets find out.

 

Environment

First of all, I wanted to make a test following fair play rules. What does it mean? It means, I wanted to test how a new SQL engine will handle traffic from a system created for the old SQL engine. As a result, I decided that no new features are allowed to improve performance. For example, SQL 2014 has the Delayed Durability feature which does not exist in SQL 2012. Using it in the test would be discriminating the older release.

All the tests were executed on the same computer. Both SQL Server instances (2012 and 2014) had assigned the same amount of memory - 1 GB. Each of them had a database created with the same tables structure:

tables struct

There were two tables: accounts and transactions. Both had a clustered index on a numeric column. They were connected through a foreign key. Additionally, the transactions table had an index on trans_acc_id column.

All the SQL statements described later in this article were executed by JMeter tool in parallel by 5 thread groups. It simulated traffic generated by 5 users querying a database as fast as they could.

 

Simple INSERT test

The first test was to check performance of simple INSERT operations on both database engines. I used the following statement:

INSERT INTO accounts (acc_id, acc_name)
VALUES
(NEXT VALUE FOR seq_test, 'test test test, absolute test');

The command was executed on SQL Server 2012 by five threads in parallel so at any point in time there were five Java threads waiting for the INSERT statement success response from the database. When it finished, the same test was executed on SQL Server 2014. The statement was executed 250 000 times on SQL 2012 and the same on SQL 2014. The results can be found in the below table:

Name Executions Min. response time [ms] Max. response time [ms] Throughput [exec./s] 
insert account SQL2012 250000 0 195 4742.75
insert account SQL2014 250000 0 199 4770.99

The difference is response times is very small and can be deemed as meaningless.

2012 vs 2014 - 0:0 DRAW

 

INSERT with transaction, casting, rounding and aggregations

The second test was a little more complex INSERT statement. It consisted of a SELECT query with aggregate functions and a data type conversion. Two enhanced statements were encapsulated in a transaction:

BEGIN TRAN;

INSERT INTO transactions (trans_id, trans_acc_id, trans_value)
SELECT NEXT VALUE FOR seq_test, FLOOR(AVG(CAST(acc_id as float))), 45.56
FROM accounts;

INSERT INTO transactions (trans_id, trans_acc_id, trans_value)
SELECT NEXT VALUE FOR seq_test, MIN(acc_id), 5.19
FROM accounts;

COMMIT;

The whole sequence was executed 5 000 times on each of the databases. The results are in the table below:

Name Executions Min. response time [ms] Max. response time [ms] Throughput [exec./s] 
insert transactions SQL2012 5000 40 247 81.52
insert transactions SQL2014 5000 45 542 74.05

A difference in this case was not as small as in the previous test. Throughput was about 9% lower in case of SQL 2014. Which means the scripts took about 9% more time to execute on the newer version.

2012 vs 2014 - 1:0

 

UPDATE statement on many rows

The third test was a quite simple UPDATE statement on a range of rows queried using the clustered key:

UPDATE transactions
SET trans_value = trans_value + 345
WHERE trans_id >= 260000 AND trans_id <= 261000;

The statement was executed 25 000 times on each database. The below table presents the result:

Name Executions Min. response time [ms] Max. response time [ms] Throughput [exec./s] 
update transactions SQL2012 25000 2 162 482.02
update transactions SQL2014 25000 2 154 477.57

Again, SQL 2012 appeared to perform slightly better by 0.9%. In this case the difference was very small and I remember the test was not executed in a laboratory but on my laptop so lets deem it as a draw.

2012 vs 2014 - 1:0

 

SELECT query

I could not have skipped a test of a sample SELECT query. This is the one I used:

SELECT acc_name, trans_value
FROM accounts
JOIN transactions ON acc_id = trans_acc_id
WHERE acc_id IN (
SELECT trans_acc_id
FROM transactions
GROUP BY trans_acc_id
HAVING SUM(trans_value) > 5180000000)

Actually, that query covered many features of SQL: joining, subqueries, grouping, filtering on grouping, aggregate functions. Yes, I know there are other options but it was impossible to test everything. The query was executed 25 000 times on each database. See the result table below:

Name Executions Min. response time [ms] Max. response time [ms] Throughput [exec./s] 
select account transactions SQL2012 25000 23 144 109.44
select account transactions SQL2014 25000 22 190 106.85

Hmm .. SQL 2014 was 2.3% slower. Is it a small enough difference to ignore it? Perhaps, but the UPDATE test had 0.9% which is more than twice smaller difference so I decided to give a point to SQL 2012 in this test.

2012 vs 2014 - 2:0

 

Conclusions

By looking at the score - 2:0 for SQL 2012, a winner is obvious. I could write that SQL 2014 disappointed me but I am not sure about such a strong statement. All the time I keep in my mind that the tests I ran, were not perfect. There were many factors that could affect the results. Of course I executed the tests more than once and the results were constant but still ... there was a chance that the physical location of the database files had an impact on the score.

I will not say SQL Server 2014 performs worse than SQL Server 2012 but if you count on a performance improvement after an upgrade, please do not. It will not happen by itself.