What 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:

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:

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:

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:

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:

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.