Esat Erkec
Monitoring table-valued parameter tempdb activity

Table-Valued Parameters in SQL Server

April 6, 2020 by

Table-Valued Parameters aka TVPs are commonly used to pass a table as a parameter into stored procedures or functions. They are helpful in a way, we can use a table as an input to these routines and we can get rid of dealing more complex steps to achieve this process.

Introduction

Most of us have heard this question from the software developers who are familiar with SQL Server:

“How can we pass a list of parameters to a stored procedure or a function?”

SQL Server table-valued parameter comic

Basically, in this article, we will try to find the answer to this question. However, the short answer to this question can be like this:

“When a data-driven application needs a list of parameters to any routines, we can overcome this issue by using the TVPs”. In the next sections of this article, we will learn using table-valued parameters with straightforward examples. This way, we will build a better understanding to answer this question too.

User-defined table types

User-defined table types are the predefined tables that the schema definition is created by the users and helps to store temporary data. User-defined table types support primary keys, unique constraints and default values, etc. However, the noticeable capability of the user-defined table type is that it is used as the referenced table type when we want to pass a TVP to stored procedures or user-defined functions.

User-defined table types definition syntax looks like as below:

When we execute the above query, we can see the created user-defined table type under the User-Defined Table Types folder in SQL Server Management Studio (SSMS).

Finding user-defined table type in SSMS

Using Table-Valued Parameters in Stored Procedures

TVPs reference their types from the user-defined table so they inherit the schema of the table. In this way, we can obtain a parameter that can transfer the multiple columns and rows into the stored procedure as input. In this manner, the multiple parameter values can pass as an input to the stored and then they can be handled by the stored procedure.

In this first example, we will insert multiple rows into a table with the help of the TVP. Firstly, we will create a table whose name is Lesson.

In the second step, we will create a stored procedure that can accept LessonType as a parameter. So, the stored procedure will gain the ability to take a table as a parameter. In this stored procedure, we will insert all rows of the @ParLessonType variable to the Lesson table.

Tip: The TVPs must be declared read-only therefore we have used the READONLY keyword in the definition of the parameter. The reason for this usage method is that we cannot make any manipulation (INSERT, UPDATE, DELETE) on the TVP in the routine body.

In this last step, we will declare a TVP that is referenced from the LessonType table type and will pass it to the stored procedure.

Finally, when we check the Lesson table, we can see that the multiple rows have been inserted into the table.

Table-valued parameter example

Using Memory-Optimized Table-Valued Parameters

In-memory OLTP, objects can provide an advantage to improve the performance of the queries. In this context, memory-optimized tables can enable us with more effective data access. Moving from this idea, it seems possible to create memory-optimized TVPs. The main advantage of using memory-optimized TVPs is minimizing tempdb activity.

In the following example, we will create a memory-optimized table type:

In this syntax, the MEMORY_OPTIMIZED =ON clause defines that this table type is memory-optimized. In addition to this, we have created a hash index because the memory-optimized table requires at least one index in order to manage the data.

Now, we will create a stored procedure that uses a memory-optimized table-type as an input.

Let’s try to execute the Usp_InsertLessonMemOpt procedure with memory-optimized table-valued parameter.

Memory-optimized table-valued parameter example

Monitoring tempdb activity of the Table-Valued Parameters

We mentioned that memory-optimized TVPs do not show activity on the tempdb database. In this part, we will prove this concept. At first, we will launch the performance monitor and clear all existing counters with the delete key.

Launching the performance monitor

In the second step, we will click the (+) sign in order to add a new counter. We will find out the SQL Server: Databases and then will add the Write Transaction/sec counter to the Added counters panel for the tempdb database.

Measuring the Write Transaction/sec

At first, we will execute the following query in a loop 100000 times which will use the disk-based TVP:

After completion of this query, we will execute the following query which will use the memory-optimized TVP.

When we analyze the performance monitor graph, it represents that the only disk-based TVP creates activity on tempdb.

Monitoring table-valued parameter tempdb activity

In a word, memory-optimized TVPs usage reduces the tempdb activity despite of that this usage type may increase memory consumption. On the other hand, disk-based TVP creates activity on the tempdb.

You can refer to “How to pass multiple parameters into an Inline table-valued function” part of this article, SQL Server inline table-valued functions to learn TVPs usage details with functions.

Conclusion

In this article, we learned about the Table-Valued Parameter usage details and we also mentioned memory-optimized TVPs performance issues. At this point, we will have to decide which TVP type provides maximum benefit according to the resource consumption and performance balance.

Esat Erkec
168 Views