Thursday, May 26, 2016

SQL Server 2014 TempDB performance changes

By Steve Endow

This post is for hard core SQL Server geeks...

I was just searching for something, and as often happens, 5 clicks later I have completely forgotten what I was searching for, but I stumbled across this obscure gem.

https://blogs.msdn.microsoft.com/psssql/2014/04/09/sql-server-2014-tempdb-hidden-performance-gem/

It discusses a change to how SQL Server 2014 handles writes to the tempdb database.  Apparently this change can result in a dramatic performance improvement for certain short operations involving tempdb.

Here is the article where I found it, discussing a performance load test of SQL 2012 vs. SQL 2014 to measure how the feature affects performance.

https://www.brentozar.com/archive/2014/04/memory-ssd-tempdb-temp-table-sql-2014/

This blog post indicates that some of the performance improvements have been back ported to SQL 2012 SP1 CU10.

Enjoy!

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter



No comments: