Wait For Service Pack 1

Conventional wisdom tells us that when Microsoft releases a new version of any server product, we should wait until Service Pack 1 before deploying it to production.

This hasn’t been true for a while now, since Microsoft recommended that Cumulative Updates for SQL Server carry the same confidence:

SQL Server CUs are certified to the same levels as Service Packs, and should be installed at the same level of confidence.

However, Service Pack 1 itself has been mired in some controversy. Microsoft didn’t make things any easier for us with SQL Server 2012, or 2014. Both versions had issues with their respective Service Pack 1.

Fortunately, SQL Server 2016 has broken the cycle, and along with all of the fixes in Cumulative Updates 1–3, and a security fix, we get better feature parity between Enterprise Edition and lower editions, including Standard, Web, Express and LocalDB.

There are some restrictions, of course, but the idea is that developers and vendors can write T-SQL for features that now appear across the board.

SQL Server 2016 Service Pack 1 now includes the following features for Enterprise, Standard, and Web Edition:

  • In-Memory OLTP
  • In-Memory Columnstore
  • Always Encrypted
  • Partitioning
  • Data Compression
  • Change data capture
  • And more!

If you want to take advantage of these features, but you use an older version of SQL Server, you will need to upgrade to SQL Server 2016 with Service Pack 1, but I think this is a no-brainer.

The good news is licences have cost the same since SQL Server 2012, and Standard Edition is almost a quarter of the price of Enterprise Edition.

I maintain that this is the most exciting release of SQL Server since 2005. If you want to upgrade, contact us and we’ll help. We will even upgrade you from SQL Server 6.5.

Updated Max Server Memory Script

Earlier this year I released a free T-SQL script that will calculate the correct amount of RAM you should allocate to SQL Server, assuming it is a standalone instance.

After attending the PASS Summit in Seattle in October, I visited the SQL Server Tiger team’s GitHub repository and discovered something similar, but not quite the same, in the Maintenance Solution folder.

I have taken the best ideas from their Database Server Options script and merged them into my Max Server Memory Calculator script.

New Features

The SQL Server thread stack is now taken into account. This value depends on the CPU architecture (32-bit, or x64 / IA64) and the maximum worker threads configured for the SQL Server instance.

On my 64-bit laptop with 16GB RAM, the new recommended amount for Max Server Memory has dropped from 11,264 MB to 10,112 MB (1,125 MB of RAM is now reserved for the thread stack).


By default, the generated script will enable show advanced options before trying to set the max server memory (MB) value.

The @ProductVersion parameter uses a new method to calculate the major SQL Server version.  Previously it was a hack based on the string returned by the @@VERSION function, but now it uses the @@MICROSOFTVERSION function.

This code is also from the Tiger team’s repository, and I’m sharing it here because I think it’s pretty clever how it works.

-- Get SQL Server Major Version

I have also added a note on the Max Server Memory Matrix page to note that the script now accounts for the thread stack.

I hope you enjoy this new version of the script. If you have any comments or suggestions, please contact me on Twitter at @bornsql .

Look, Ma, No Surprises

Last week I demonstrated at least 30% performance improvement by switching to memory optimised table-valued parameters on SQL Server 2016.

This week I will demonstrate the same test using Azure SQL Database, on the Premium tier, where In-Memory OLTP is supported.

My test harness is the same. I will create a temp table and a table variable:

-- Temp table creation
CREATE TABLE #temptable

-- Table variable creation
DECLARE @tablevariable AS TABLE

Now I will create a standard table-valued parameter:


DECLARE @TempTVPStandard AS dbo.TVPStandard;

Finally, I will create a memory-optimized table-valued parameter (there is no requirement to separately enable In-Memory OLTP in Azure SQL Database):


DECLARE @TempTVPMemory AS dbo.TVPInMemory;

So far, nothing is different from last week’s scenario. We now have the following structures at our disposal:

  • #temptable (a temp table)
  • @tablevariable (a table variable)
  • @TempTVPStandard (a standard TVP)
  • @TempTVPMemory (a memory-optimized TVP)

I’m going to use the same WHILE loop again, but instead of a million runs, I’ll do 1000, 10,000 and 100,000, because I’m paying for this instance of Azure SQL Database (I picked a Premium P1, with 125 DTUs) and I’m a cheapskate. I doubt the 125 DTUs is even enough to run a million times for the fourth option.



WHILE @i < <number of executions>
INSERT INTO <object name>
SELECT @i = @i + 1;




Like last week, at low row counts all four data structures performed around the same speed. Tempdb is shared with other Azure SQL Database customers, so I expected to see that slower.

I ran each series three times and took the lowest value from each run.

Data Structure 1,000 10,000 100,000 1,000,000
Temp Table (Clustered) 94 ms 453 ms 4,266 ms 44,955 ms
Table Variable (Clustered) 93 ms 344 ms 3,484 ms 34,673 ms
Standard TVP (Clustered) 94 ms 343 ms 3,500 ms 34,610 ms
Memory-Optimized TVP
78 ms 203 ms 1,797 ms No Time

Unsurprisingly, because Azure SQL Databases share tempdb with other customers, the IO-bound figures are higher than those on my dedicated laptop, no matter how old it is.

The big winner here, again, is the memory-optimized table-valued parameter. More than twice as fast as a temp table, and almost twice as fast as a table variable or standard TVP.

Note, however, that because my Azure SQL Database was resource-constrained, I was unable to run the one-million step WHILE loop.

This is an excellent example of how In-Memory OLTP is a trade-off, no matter whether you’re running on premises or in the cloud. While you do consistently get much better performance, it is not reliable for large data structures.

If you have any In-Memory OLTP tweaks you’d like to share, find me on Twitter at @bornsql.

How to make your T-SQL thirty percent faster

Last week, I mentioned a feature in SQL Server, and now in Azure SQL Database, called Memory-Optimized Table-Valued Parameters, which makes use of In-Memory OLTP structures to work around a dependency on tempdb, and thereby drastically improve performance.

Short version: We can see more than double speed improvement by switching from temp tables to Memory-Optimized TVPs, and a 30% speed improvement by switching from standard TVPs.

This week, I will compare the performance characteristics of the following three data structures:

  • temp tables (and table variables),
  • standard table-valued parameters, and
  • memory-optimised table-valued parameters.

These tests will be performed on my venerable Asus laptop, which is around four years old now, running SQL Server 2016 Developer Edition. It has 16GB of RAM, a 2.4GHz quad-core CPU, and an Intel 530-series SSD.

(Edit: With SQL Server 2016 Service Pack 1, you can now create Memory-Optimized objects in all editions of SQL Server.)

The test is very simple and makes use of a technique that one of my customers uses extensively: writing some rows to a temp table, so that another process can reuse those values as parameters, and perform an action, after which it destroys the temp table.

Unfortunately, the method my customer uses to populate a temp table, does not work on Azure SQL Database, because they explicitly refer to the temp tables with three-part naming (tempdb..#temptable), which is not permitted.

For the sake of the exercise, I will have a process that writes to a data structure and compare the times.

What is a Table-Valued Parameter anyway?

At its heart, a Table-Valued Parameter is a way to pass more than one row of data around in T-SQL. Instead of having many individual parameters in a stored procedure, or passing around values in temp tables, you could simply pass in the TVP, which is defined as a table type.

Warning sounds should already be going off in your head, because, yes, TVPs are just table variables. This means that they don’t have associated statistics and cannot be indexed (apart from a primary key). In other words, you do not want to use TVPs to pass in large amounts of data. Performance is going to suck.

So what do we know this far? Table variables, temp tables, and standard table-valued parameters are pretty much equivalent, in that they are backed by tempdb, which is persisted on the hard drive, which means we will be bound by I/O.

Memory-Optimized TVPs, by definition, are in-memory structures. By making use of the In-Memory OLTP engine, we know that they use optimistic concurrency and have no locks, only lightweight latches. This makes them fast. Because they reside in memory, they are compiled into native code, which means they’re even faster.

Create the test objects!

Let’s start with a temp table. For your amusement, I will be creating both a temp table and a table variable.

I’m lumping these together, because they’re almost the same thing under the covers for this scenario. The table is allocated on tempdb, data is inserted into it—which has to be fully logged (I/O bound)—and if for any reason the query spills to disk (and there are a lot of reasons, including sorts), it will incur an I/O penalty in the data file too.

I was not going to use any indexes or primary keys here, to try and keep the performance comparable, but memory-optimized data structures require indexing, so I’ve added a primary key. As we know, adding a clustered index, common on temp tables, will make it slower because it has to sort the table on the clustering key.

To make this even simpler, my sample data will only comprise one column. I’ve picked a bad example, because developers love GUIDs, but SQL Server does not.

-- Temp table creation
CREATE TABLE #temptable

-- Table variable creation
DECLARE @tablevariable AS TABLE

Now we have two tables backed by tempdb. One of them has statistics, and the other (depending on the version) has 1 or 100 rows, according to SQL Server. Fantastic.

Let’s create a standard table-valued parameter with the same structure:


DECLARE @TempTVPStandard AS dbo.TVPStandard;

Now let’s create a memory-optimized table-valued parameter. Note that the major difference here is that we specify the MEMORY_OPTIMIZED state (there are some additional requirements for setting up In-Memory OLTP, which are outside of the scope of this post).


Note: I had to cheat here. You’ll notice that my previous data structures default to a clustered index on a primary key. Memory-Optimized Table-Valued Parameters cannot use a clustered index, but instead require a non-clustered index.

DECLARE @TempTVPMemory AS dbo.TVPInMemory;

We now have the following structures at our disposal:

  • #temptable (a temp table)
  • @tablevariable (a table variable)
  • @TempTVPStandard (a standard TVP)
  • @TempTVPMemory (a memory-optimized TVP)

Test the Performance!

For repeatable tests, I was going to use STATISTICS IO and STATISTICS TIME, and insert 100, then 1000, then 10,000 rows into each data structure.

I ran into a minor problem at first: I can’t use the GO <n> batch command to make things execute a lot of times, because table variables and TVPs are local only to the current batch. I had to rely on a WHILE loop.

After going through this a few times, I also couldn’t actually measure performance with STATISTICS TIME, because even my old laptop was too quick. It’s a common problem we see in performance tuning: small data structures are fast.

Instead, I used a start and end time, using SYSUTCDATETIME, and a WHILE loop, inserting ten thousand, then a hundred thousand, and then a million rows into each structure. A contrived example, sure, but one necessary to demonstrate how performance differs with larger rowsets.



WHILE @i < <number of executions>
INSERT INTO <object name>
SELECT @i = @i + 1;




At low row counts, all four data structures performed around the same speed. Provided tempdb’s data and log files are correctly sized and don’t have to grow, it averages out.

Things got more interesting at 10,000 rows. I ran each series three times, and took the lowest value from each run.

Data Structure 10,000 100,000 1,000,000
Temp Table (Clustered) 291 ms 2,872 ms 29,110 ms
Table Variable (Clustered) 173 ms 1,708 ms 17,665 ms
Standard TVP (Clustered) 184 ms 1,694 ms 17,764 ms
Memory-Optimized TVP (Non-Clustered) 120 ms 1,174 ms 12,541 ms

Because of the type of query I’m running, the performance profile is linear. A million rows took 100x more time than ten thousand.

Unsurprisingly, the standard TVP and table variable performed the same, since they’re effectively the same data structure. Why the temp table is slower, is an exercise for a later blog post.

(When all three disk-bound structures used Non-Clustered Primary Keys, they were significantly slower than with clustered indexes. Performance with clustered indexes, versus no primary key at all, was the same.)

Keep in mind that these are row-by-row operations. If we were populating these structures from a set-based operation, it would be a lot faster. However, this demonstrates clearly how much more efficient a memory-optimized table-valued parameter is compared to the other data structures.

Caveat: memory-optimized TVPs reside exclusively in memory. We have to remember that it’s possible to run out of memory when using these structures. The last thing we want to see is the following error message:

The statement has been terminated.
Msg 701, Level 17, State 109, Line 92
There is insufficient system memory in resource pool 'default' to run this query.

Memory-Optimized Table-Valued Parameters are at least 30% faster than any of the other disk-bound data structures and more than twice as fast as temp tables specifically, but we need to ensure they are used correctly.

For more reading, check out the MSDN article “Faster temp table and table variable by using memory optimization”.

Next week, I will present the findings for this same test, but performed on an Azure SQL Database on the Premium Tier (because that’s where In-Memory OLTP lives).

If you have any performance tricks you would like to share, please find me on Twitter at @bornsql .

Memory-Optimized Table-Valued Parameters

November brings me back to my regular weekly posting schedule.

I attended PASS Summit 2016 last week, and saw a lot of great sessions. Aside from the new stuff, what I also noticed is how people are making use of existing technology to solve performance problems.

I have a customer that is targeting their vendor app to run in Azure SQL Database. They’ve run the Advisor and have discovered a number of places where they make use of temp tables. While this is not in itself a problem, how they check for their existence is a problem.

Azure SQL Database does not allow you to refer to tempdb by name. Any T-SQL construct referring to a temp table using the form tempdb..#table is explicitly forbidden.

One of the workarounds I recommended to them was to replace their use of temp tables with TVPs (table-valued parameters). There is no performance benefit to doing so, but it works.

Then I remembered that Azure SQL Database Premium Tier now supports In-Memory OLTP.

If you recall in a previous post, I suggested that Standard Tier and Premium Tiers are functionally equivalent to Standard Edition and Enterprise Edition respectively. This is no exception. You can create Memory Optimized TVPs in the Premium Tier of Azure SQL Database.

(Edit: With SQL Server 2016 Service Pack 1, you can now create Memory-Optimized objects in all editions of SQL Server.)

In other words, for these short-lived temp tables, there’s not only an added benefit of no longer worrying about referring to tempdb..#table, but you also get a massive performance improvement as well.

There are some legitimate concerns, though. Any Memory Optimized structures naturally require dedicated memory, so if you are already memory-bound on your Azure SQL Database (see the DTU post I wrote last month), this is going to hurt you. On the other hand, by not using tempdb at all, the performance improvement (as much as 100% in what I saw in the sessions last week) may offset that enough to warrant its use.

Next week, I will compare the performance characteristics of temp tables, standard TVPs, and Memory Optimised TVPs, both on-premises (using SQL Server 2016 Developer Edition, which is the same as Enterprise Edition) and Azure SQL Database (using a Premium Tier database).

If you have any other quick performance wins using new features of SQL Server 2016 or Azure SQL Database, find me on Twitter at @bornsql .