Uncover Sql2014 new features-tempdb performance improvements

All along, in high-load, complex production environments, the pressure of tempdb is one of the most important factors for the entire instance bottleneck. Microsoft engineers are also constantly optimizing its use in various editions. to the SQL SERVER2014 also has a new feature that makes the performance of tempdb a certain improvement. Here I will show you the changes in the implementation of tempdb in the new version through an example.

We all know that the tempdb log does not need to advance the disk is one of the important reasons for its fast, the log does not need to disk, then how to operate the data? Here is an important concept: active writing (Eager write)

Active write: When the SQL Server engine faces the scenario of minimizing log operations (Blukinsert,select into,etc) writes data pages in bulk (typically 32 pages/times) in an active-write manner to reduce lazy writes (lazy write ), the disk pressure caused by the checkpoint (checkpoint) operation.

DBAs or developers familiar with SQL Server development should know that there are a number of minimized log operations when using tempdb, such as the SELECT * into #xx, while SQL Because of the nature of the engine's active write, the server's optimizations for even bulk writes make the whole disk IO much more stressful due to the frequent write of tempdb. Fortunately, Microsoft engineers have noticed the situation, In Sql2014 tempdb, the engine relaxes the necessity of actively writing in tempdb, where a data page can reside in memory without actively swiping into the disk (SQL Server considers a short window of time), reducing the load on the disk, This also makes the performance of tempdb more efficient. Here I show you through a simple example.

Note: In order to demonstrate the features of different versions, we need to turn on the trace flag (TF 3917) to capture the active write behavior.

Let's first look at the situation under SQL2008R2 SP2 1-1

Select @ @VERSION--Microsoft SQL Server R2 (SP2)-10.50.4000.0DBCCTraceON (3604,3917,-1)----catch Eager WriteSelect *  intoAaa fromdbo.bigproductDBCCTraceoff (3604,3917,-1)-----Remember turn off the tf!

Figure 1-1

Next the same script we do in sql2014 1-2

Code Sql2014 tempdb does no eager write

Select @ @VERSION--Microsoft SQL Server 2014-12.0.2000.8 (X64)DBCCTraceON (3604,3917,-1)----catch Eager WriteSelect *  into#ttt fromdbo.bigproductDBCCTraceoff (3604,3917,-1)-----Remember turn off the tf!

Figure 1-2

We can see from the example that in sql2014, the operation in tempdb did not trigger the active write because of the select INTO, which prevented the disk write operation.

Note: This feature is only useful in tempdb, and there is still an active write implementation feature in the new version of the user database. Interested friends can test themselves

Perhaps some friends think that bulk write is already an optimization behavior, this small change in tempdb is insufficient to alleviate its bottleneck problem, here we use a simple example to illustrate the next.

SQL2008R2 SP2 Code

Note: Please restart the SQL Server instance before testing to make the tempdb count as accurate as possible

Test results 1-3

 UseAdventureWorksGoCreate procp_tempdbtest asSelect *  into#ttt fromdbo.bigproductDeclare @tDateTime2=sysutcdatetime ()Declare @i intSet @i=1 while(@i< -)beginexecp_tempdbtestSelect @i=@i+1EndSelect [Extime]=DATEDIFFS@t, Sysutcdatetime ())SELECTD.name asdatabase_name, F.name as [file_name], F.physical_name, F.type_desc, Vf.num_of_reads, Vf.num_of_writes fromSys.dm_io_virtual_file_stats (NULL,NULL) asVFINNER JOINsys.databases asD ond.database_id=vf.database_idINNER JOINSys.master_files asF onF.file_id =Vf.file_id    andf.database_id=vf.database_idwheref.database_id= db_id('tempdb')

View Code

Figure 1-3

SQ2014 code executes scripts in SQL2008R2 SP2

Note: Please restart the SQL Server instance before testing to make the tempdb count as accurate as possible

Test results 1-4

Figure 1-4

As the author of the test of the two server test machine are 4 R5 15K disk, you can consider the test process disk IO environment is the same. We can see from the test that this evasion of tempdb in the new version can make tempdb in many cases (select INTO, Create Index,etc) writes a great deal of stress relief, increasing the efficiency of a single operation while also making overall performance higher.

Note: The CSS team of SQL Server claims that this functionality may be implemented in subsequent patches in the SQL 2012 release.

When SQL Server is facing significant memory pressure, lazy write also brushes the data page into the disk.

Non-temporary objects in tempdb still need to be actively written. (Friends of interest can test themselves)

Conclusion: the development of relational database so far, the individual think that the idea is difficult to make a big breakthrough, The details of the product to achieve better to reflect the quality of the product. Microsoft has implemented different implementations of specific libraries based on the scenarios they use, which makes me feel that SQL Server is still improving. Although a lot is not good, but here I like for Microsoft.

Uncover Sql2014 new features-tempdb performance improvements