SQL Server Code,Tips and Tricks, Performance Tuning

Saturday, May 17, 2025

Benefits of Indexes

This is day fifteen of the SQL Advent 2012 series of blog posts. Today we are going to look at the benefit of indexes

So how does an index work?

How does an index work, how does it help SQL Server finding stuff faster? Here is an simple non technology explanation. If I told you to grab a cookbook and give me all the recipes in that book for cod, what would you do? There are two things yuo can do, you can read through the whole book page by page until you get to the last page looking for any cod recipes. Or...........take a look at the picture below

See that, in one second I know exactly where to find cod recipes, it is on page 305 and 61. Which do you think is faster, looking it up in an index or scanning through the book?

SQL Server pretty much uses the same technique. There are two types of basic indexes in SQL Server, these are clustered and non clustered indexes. A clustered index will contain all the data from the table as well, a non clustered index will have a pointer to the table row if there is no clustered index on that table or to the clustered index position is there is a clustered index on the table.

A table with a clustered index is also called a clustered table. A table without a clustered index is also called a heap.

SQL Server also has XML, spatial, columnstore, filtered, full text indexes but we are just focusing on the basic indexes in this post.

Here is what books on line has to say about the storage of a clustered index

In SQL Server, indexes are organized as B-trees. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom level of nodes in the index is called the leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list.

A non clustered index is a little different since it doesn't store the whole data pages, here is what books on line has to say about the storage of a nonclustered index

Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:

The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.

The leaf layer of a nonclustered index is made up of index pages instead of data pages.

Nonclustered indexes can be defined on a table or view with a clustered index or a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value.
The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).

If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users. It is only added when required to make the clustered key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.

Cool, I will now just add indexes on every column. Not so fast, there are some things to consider here, for every update, insert and delete statement indexes have to be maintained. If you have a busy OLTP database, you have to find the right balance between your read and write IO. Not enough indexes and your retrieval queries will suffer, too many indexes and your inserts will be slower. Also keep in mind that indexes take up storage, the more you have the bigger your database will be.

Keep your clustered indexes narrow

Try to keep your clustered indexes as narrow as possible, if you can use something like an integer, this is only 4 bytes. The reason to keep your clustered indexes narrow is that when you have non clustered indexes, the row locator is the clustered index key for the row. In this case your non clustered index will become bigger as well and now you won't be able to store as much data on a page. To illustrate that let's take a look at some simple code

First let's create this table and populate it with 2048 rows

CREATE TABLE Test1(id int, somecol char(36), somecol2 char(36))
GO

INSERT Test1 
SELECT number,newid(),newid() 
FROM master..spt_values
WHERE type = 'P'

Add a clustered index

CREATE CLUSTERED INDEX cx on Test1(id)

Add these two non clustered indexes

CREATE NONCLUSTERED INDEX ix1 on Test1(somecol)
CREATE NONCLUSTERED INDEX ix2 on Test1(somecol2)

Let's check how much storage is required for the non clustered indexes

SELECT
DB_NAME(DATABASE_ID) AS [DatabaseName],
OBJECT_NAME(OBJECT_ID) AS TableName,
SI.NAME AS IndexName,
INDEX_TYPE_DESC AS IndexType,
PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS
INNER JOIN sysindexes SI
ON DPS.OBJECT_ID = SI.ID AND DPS.INDEX_ID = SI.INDID
AND OBJECT_NAME(OBJECT_ID) = 'Test1'
GO

Here is the output, as you can see the non clustered indexes take up 12 pages

DatabaseName TableName IndexName IndexType PageCounts
tempdb         Test1         cx       CLUSTERED INDEX 22
tempdb         Test1         ix1    NONCLUSTERED INDEX 12
tempdb         Test1         ix2    NONCLUSTERED INDEX 12

If we check the table size

EXEC sp_spaceused 'Test1'

name rows reserved data index_size unused
Test1 2048    472 KB        176 KB 240 KB         56 KB

We see that it is using 240 KB for the indexes

Let's recreate the clustered index with all 3 columns now.

CREATE CLUSTERED INDEX cx on Test1(id,somecol,somecol2)
WITH DROP_EXISTING

Recreating the clustered index also recreated the non clustered indexes. Let's check now how many pages a non clustered index is

SELECT
DB_NAME(DATABASE_ID) AS [DatabaseName],
OBJECT_NAME(OBJECT_ID) AS TableName,
SI.NAME AS IndexName,
INDEX_TYPE_DESC AS IndexType,
PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS
INNER JOIN sysindexes SI
ON DPS.OBJECT_ID = SI.ID AND DPS.INDEX_ID = SI.INDID
AND OBJECT_NAME(OBJECT_ID) = 'Test1'
GO

Here are the results

DatabaseName TableName IndexName IndexType PageCounts
tempdb          Test1         cx         CLUSTERED INDEX 22
tempdb          Test1         ix1      NONCLUSTERED INDEX 21
tempdb   Test1         ix2      NONCLUSTERED INDEX 21

As you can see the non clustered indexes went from 12 to 21 pages

The index size changed, if you run this

EXEC sp_spaceused 'Test1'

Here is the result

name rows reserved data index_size unused
Test1 2048    600 KB  176 KB 384 KB         40 KB

So we went from 240 KB to 384 KB for the index storage.

So why does this matter you ask? SQL Server will use indexes for all kind of things, if you run a COUNT(*) it will use an index, if you do a JOIN it will use an index, it will use indexes in GROUP By queries and many more things.

Let's look at a simple example, when you do a COUNT(*), the optimizer will pick a non clustered index if there is one since it usually has less columns than the clustered index

SET SHOWPLAN_TEXT ON
GO

SELECT count(*) FROM Test1
GO

SET SHOWPLAN_TEXT OFF
GO

Here is the plan

|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
|--Index Scan(OBJECT:([ReportServer].[dbo].[Test1].[ix2]))

Basically it had to scan through all the index pages to get the count, if your index was now still 12 pages instead of 21, SQL Server would take less time to accomplish this.

I barely scratched the surface on indexing, it is a big topic and I recommend you start by navigating to the topic in Books On Line: http://msdn.microsoft.com/en-us/library/ms175049.aspx

You can also take a look at the following posts written about indexing right here on this site

Index REBUILD and REORGANIZE
Indexes with Included Columns
Filtered Indexes
Is an index seek always better or faster than an index scan?
Finding Fragmentation Of An Index And Fixing It
How to get the selectivity of an index
Adding nonclustered index on primary keys
Index Seek on LOB Columns
Row Overflow Pages - Index Tuning
Columnstore Index Basics
Columnstore Index – Index Statistics

That is all for day fifteen of the SQL Advent 2012 series, come back tomorrow for the next one, you can also check out all the posts from last year here: SQL Advent 2011 Recap

Monday, August 02, 2021

How to connect to a SQL Server instance on a different domain and a non default port number?

We have several domains at my job, we have a dev domain, a prod domain and then several other domains. Recently we had some domain changes where we would connect to a different domain when logging in from the laptops.

When opening up SSMS and connecting to the SQL Server instances, we now need to use a fully qualified domain name (FQDN) and also specify the port number and the instance name. This could seem a little confusing if you have never done this

Here is what it looks like if for example your instance is listening on port 8000, the instance name is SQL01, the server name is DevSQL01 and the domain name is SQLRules.net

You would put the following in server name

DevSQL01.SQLRules.net,8000\SQL01

To make is easier to see... here it is again with different colors for the different parts

DevSQL01.SQLRules.net,8000\SQL01

The server name is DevSQL01

The domain name is SQLRules.net

The port number is 8000

The instance name in SQL01

The fully qualified domain name (FQDN) in this case is of course DevSQL01.SQLRules.net

Hopefully this helps someone in the future

Monday, March 15, 2021

After 20+ years in IT .. I finally discovered this useful command

Very similar to my After 20+ years in IT .. I finally discovered this... post, I discovered yet another command I should have know about

Originally I was not going to write this post, but after I found out that several other people didn't know this, I figured what the heck, why not, maybe someone will think this is cool as well

Open up a command prompt or powershell command window, , navigate to a folder, type in tree... hit enter

Here is what I see

I was watching a Pluralsight course and the person typed in the tree command.. and I was like whoaaaa.. How do I not know this? Perhaps maybe because I don't use the command window all that much? Anyway I thought that this was pretty cool

As you can see tree list all the directories and sub directories in a tree like structure. This is great to quickly see all the directories in one shot

The same command will work in Powershell

Finally, here is an image of the command window output as well as the explorer navigation pane side by side

Hopefully this will be useful to someone

Friday, May 22, 2020

PostgreSQL adds FETCH FIRST WITH TIES.. just like TOP n WITH TIES in SQL Server

PostgreSQL 13 Beta 1 was released yesterday, you can read the release notes here

https://www.postgresql.org/about/news/2040/

One thing that caught my eye was this statement in the release notes

PostgreSQL 13 brings more convenience to writing queries with features like FETCH FIRST WITH TIES, which returns any additional rows that match the last row.

This is I guess exactly like TOP WITH TIES in SQL Server. I believe this has been around in SQL Server since at least version 7. How many times have I used it in code that was deployed in the last 20 years? I believe I have used WITH TIES only once. It does make for great interview questions and SQL puzzles :-)

So let's take a quick look at how TOP WITH TIES works in SQL Server. The first thing we will do is look at what Books On Line says about TOP

WITH TIES Returns two or more rows that tie for last place in the limited results set. You must use this argument with the ORDER BY clause. WITH TIES might cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but two additional rows match the values of the ORDER BY columns in row 5, the result set will contain seven rows.You can specify the TOP clause with the WITH TIES argument only in SELECT statements, and only if you've also specified the ORDER BY clause. The returned order of tying records is arbitrary. ORDER BY doesn't affect this rule.

Time to get started and write some code to see this in action

First create this table of students and insert some data

CREATE TABLE #TopExample(GradeAverage int, Student varchar(100))
INSERT #TopExample VALUES(99.00,'Plato'),
      (98,'Socrates'),
      (95,'Diogenes the Cynic'),
      (94,'Antisthenes'),
      (94,'Demetrius'),
      (50,'Denis')

As you can see, I am not a very good student :-(

If you do a regular TOP 4 query like this

SELECT TOP 4 GradeAverage, Student 
FROM #TopExample  
ORDER BY GradeAverage DESC

You will get back these results

GradeAverage Student
99          Plato
98          Socrates
95          Diogenes the Cynic
94          Demetrius

As you can see we are missing another student with a grade of 94, this is Antisthenes

This is easily fixed by adding WITH TIES to the query

SELECT TOP 4 WITH TIES GradeAverage, Student 
FROM #TopExample 
ORDER BY GradeAverage DESC

Now, you will get back these results, as you can see, you now have 5 rows and both rows with a grade average of 94 are included

GradeAverage Student
99          Plato
98          Socrates
95          Diogenes the Cynic
94          Demetrius
94          Antisthenes

Another way to do the same as WITH TIES is by using DENSE_RANK. That query looks like this

;WITH c AS (SELECT DENSE_RANK() OVER (ORDER BY GradeAverage DESC) AS dens, 
 GradeAverage,Student 
 FROM #TopExample)

SELECT GradeAverage, Student 
FROM c WHERE dens <=4
ORDER BY GradeAverage DESC

You will get back these same results again, you now have 5 rows and both rows with a grade average of 94 are included as well

GradeAverage Student
99          Plato
98          Socrates
95          Diogenes the Cynic
94          Demetrius
94          Antisthenes

Using DENSE_RANK is bit more code, but if portability is a concern, it might be a better choice

There you go a post about a feature you will never use :-)

If you want to run all the queries in one shot here is all the code

CREATE TABLE #TopExample(GradeAverage int, Student varchar(100))
INSERT #TopExample VALUES(99.00,'Plato'),
      (98.00,'Socrates'),
      (95.00,'Diogenes the Cynic'),
      (94.00,'Antisthenes'),
      (94.00,'Demetrius'),
      (50.00,'Denis')

SELECT TOP 4 GradeAverage, Student 
FROM #TopExample  
ORDER BY GradeAverage DESC

SELECT TOP 4 WITH TIES GradeAverage, Student 
FROM #TopExample 
ORDER BY GradeAverage DESC

;WITH c AS (SELECT DENSE_RANK() OVER (ORDER BY GradeAverage DESC) AS dens, 
 GradeAverage,Student 
 FROM #TopExample)

SELECT GradeAverage, Student 
FROM c WHERE dens <=4
ORDER BY GradeAverage DESC 


DROP TABLE #TopExample

And here is what it all looks like in SSMS, code and output

Wednesday, May 06, 2020

You know about waitfor delay but did you know there is a waitfor time?

I was looking at some code I wrote the other day and noticed the WAITFOR command.. This got me thinking. How many times have I used WAITFOR in code, probably as much as I have used NTILE :-)

I looked at the documentation for WAITFOR and notice there is TIME in addition to DELAY. Oh that is handy, I always rolled my own ghetto-style version by calculating how long it would be until a specific time and then I would use that in the WAITFOR DELAY command

Why would you use the WAITFOR command?

The WAITFOR command can be used to delay the execution of command for a specific duration or until a specific time occurs. From Books On Line, the description is as follows:

Blocks the execution of a batch, stored procedure, or transaction until either a specified time or time interval elapses, or a specified statement modifies or returns at least one row.

WAITFOR
{
DELAY 'time_to_pass'
| TIME 'time_to_execute'
| [ ( receive_statement ) | ( get_conversation_group_statement ) ]
[ , TIMEOUT timeout ]
}

Arguments
DELAY
Is the specified period of time that must pass, up to a maximum of 24 hours, before execution of a batch, stored procedure, or transaction proceeds.

'time_to_pass'
Is the period of time to wait. time_to_pass can be specified either in a datetime data format, or as a local variable. Dates can't be specified, so the date part of the datetime value isn't allowed. time_to_pass is formatted as hh:mm[[:ss].mss].

TIME
Is the specified time when the batch, stored procedure, or transaction runs.

'time_to_execute'
Is the time at which the WAITFOR statement finishes. time_to_execute can be specified in a datetime data format, or it can be specified as a local variable. Dates can't be specified, so the date part of the datetime value isn't allowed. time_to_execute is formatted as hh:mm[[:ss].mss] and can optionally include the date of 1900-01-01.

WAITFOR with a receive_statement or get_conversation_group_statement is applicable only to Service Broker messages. I will not cover those in this post

I must admit that I only use these commands a couple of times a year when running something ad-hoc. In code, I will use WAITFOR DELAY when doing a back fill of data, and the table is replicated. In that case I will batch the data and after each batch is completed I will pause for a second or so. The reason I am doing this is because I don't want to increase replication latency, after all, I am a nice guy

WAITFOR TIME

Let's take a look how you would use the WAITFOR command. I will start with WAITFOR TIME

The command is very easy.. if you want the print command to run at 09:57:16, you would do the following

WAITFOR TIME '09:57:16'
PRINT 'DONE  '

The seconds are optional, if you want it to run at 9 hours and 57 minutes, you can do the following

WAITFOR TIME '09:57'
PRINT 'DONE  '

One thing to know is that you can't grab the output from a time data type and use that in your WAITFOR TIME command. The following will blow up

SELECT CONVERT(time,  getdate()) --'09:57:16.9600000'

WAITFOR TIME '09:57:16.9600000'

Msg 148, Level 15 , State 1, Line 32
Incorrect time syntax in time string '09:57:16.9600000' used with WAITFOR.

What you need to do is strip everything after the dot.

We need the command to be the following

WAITFOR TIME '09:57:16'

There are two ways to accomplish this... first way is by using PARSENAME, I blogged about that function several times, the first time here: Ten SQL Server Functions That You Hardly Use But Should

All you have to tell SQL Server which part you want, if you use PARSENAME,1 you will get everything after the dot, if you use PARSENAME,2 you will get everything before the dot.

1 2	SELECT PARSENAME('09:57:16.9600000',2), PARSENAME('09:57:16.9600000',1)

This returns the following

09:57:16 9600000

The easiest way would have been to just use time(0) instead


1
2
SELECT CONVERT(time,  getdate()) ,--'09:57:16.9600000'
CONVERT(time(0),  getdate())  --'09:57:16

Below is a complete example that will wait for 10 seconds to run the PRINT statement on line 12 if you run the whole code block in 1 shot.

Also notice that I use a variable with the WAITFOR TIME command on line 9. The caveat with that is that the variable can't be a time datatype. This is why I use a varchar datatype and store the value of the time data type in it. The reason I use the time datatype in my procs is so that I don't have to do a lot of validations when someone is calling the proc. If they pass in a string that can't be converted.. the proc won't even run... it will fail right at the proc call itself

DECLARE @DelayTime time(0)= GETDATE()
PRINT @DelayTime

SELECT @DelayTime =DATEADD(second,10,@DelayTime)

PRINT @DelayTime

DECLARE @d varchar(100) = @DelayTime -- you have to use varchar in command
WAITFOR TIME @d 

-- Run your command here
PRINT 'DONE  ' + CONVERT(varchar(100),CONVERT(time(0),  getdate()))

What is printed is the following

10:49:48
10:49:58
DONE 10:49:58

Now when would you really use WAITFOR TIME? You can accomplish the same with a scheduled job, the only time I use WAITFOR TIME is if I want a quick count of want to run something at a specific time but I know I won't be at my desk and I can't create a job without a ticket

But you also have to be aware that if your connection gets lost to the SQL Server instance, your command won't execute

WAITFOR DELAY

The WAITFOR DELAY command is similar to the WAITFOR TIME command, instead of waiting for a time, the command pauses for a specific period

Like I said before, I use WAITFOR DELAY as well as a batch size in my back fill procs. Both can be passed in, if you do a load during a weekday, your delay would be longer than on a weekend.

Sometimes I need to see how many rows are getting inserted every minute.. or something similar
I will then combine WAITFOR DELAY and the batch terminator with a count number to execute the batch of statements more than once

Here is such an example, it will run the INSERT statement 20 times, it will pause 1 minute between each execution

INSERT #temp(SomeCol, SomeTimeStamp)
SELECT COUNT(*), GETDATE() FROM sometable 
WAITFOR DELAY '00:01:00'
GO 20

That's all for this post.

Do you use the WAITFOR command, if so, what do you use it for?

Monday, January 20, 2020

TVPs vs Memory Optimized TVPs

The other day I was thinking about the blog post Faster temp table and table variable by using memory optimization I read a while back. Since you can't believe anything on the internets (no disrespect to whoever wrote that post) , I decided to take this for a test

In this post I will be creating 2 databases, one is a plain vanilla database and the other, a database that also has a file group that contains memory optimized data

I will also be creating a table type in each database, a plain one and a memory optimized one in the memory optimized database

So lets get started, first I will create the regular database and the regular table type

CREATE DATABASE TempTVP
GO

USE TempTVP
GO

CREATE TYPE dbo.DataProcessingType AS TABLE(
 SomeDate datetime NOT NULL,
 SomeSymbol varchar(40) NOT NULL,
 SomeValue numeric(24, 10) NOT NULL,
 SomeDescription varchar(100),
 index tvp_temp (SomeDate, SomeSymbol))
GO

Now I will create the memory optimized database and the memory optimized table type
In order for the database to be able to use memory optimized code, you need to add a filegroup and tell SQL Server it contains memory optimized data, after that is created, you add a file to that file group.

The table type syntax is identical except for the line (WITH (MEMORY_OPTIMIZED = ON);) at the end

Here is what the script looks like

CREATE DATABASE TempTVPHekaton
GO

USE TempTVPHekaton
GO


ALTER DATABASE [TempTVPHekaton] ADD FILEGROUP [Hekaton_Data] 
CONTAINS MEMORY_OPTIMIZED_DATA 
GO


ALTER DATABASE [TempTVPHekaton] ADD FILE (NAME='Hekaton_Data_file',
 FILENAME='C:\Data\ekaton_Data_file.mdf') TO FILEGROUP Hekaton_Data;
GO

CREATE TYPE dbo.DataProcessingType AS TABLE(
 SomeDate datetime NOT NULL,
 SomeSymbol varchar(40) NOT NULL,
 SomeValue numeric(24, 10) NOT NULL,
 SomeDescription varchar(100),
 index tvp_temp (SomeDate, SomeSymbol))
  WITH   (MEMORY_OPTIMIZED = ON); 
GO

Now that we have our two database, lets create a very simple stored proc in each database, all it does is store the row count from the table valued parameter passed in into a variable

CREATE PROCEDURE prTestTVP @tvp DataProcessingType readonly

AS

SET NOCOUNT ON

DECLARE @Count int

SELECT @Count = COUNT(*) FROM @tvp
GO

Now it is time to generate the test script

The text script will call the stored procedure 1000 times passing in a table valued parameter
The test script will populate the table type with 1000 rows, the data looks like this

That data is pushed into the table valued parameter, the proc is called, the table type is cleared out and every 100 iterations the current iteration will be printed

SET NOCOUNT ON
DECLARE @LoopID int = 1

WHILE @LoopID <= 1000
BEGIN
 DECLARE @tvp DataProcessingType
 INSERT @tvp -- add some values
  SELECT DATEADD(d,number,'20200101') as SomeDate,
  'X' + STR(number) + STR(@LoopID) as SomeSymbol,
   number * @LoopID * 1.11 as SomeValue,
   LEFT(REPLICATE(number,100),100) as SomeDescription
 FROM master..spt_values
  WHERE type = 'p' -- only numbers
  and number < 1000
 ORDER BY NEWID() --pseudo-random sort


 EXEC prTestTVP @tvp -- CALL proc with 1000 rows
 
 DELETE @tvp -- delete the data since it will be populated again

  if @LoopID %100 = 0 -- print every 100 iterations
  PRINT STR(@LoopID)
 SET @LoopID += 1 -- add 1 to counter

END

What I did now is take the code, I then pasted the code in 2 different SSMS windows and connected to the TempTVP database, I then executed the code in both windows and let it run. Once it was finished, I noted down how long it took and then changed the connections to the database TempTVPHekaton which is memory optimized and ran the code in both windows as well. I played around with loops of 100, 1000, 2000, I played around as well by populating the table with rows between 1000 and 2048

Here are some of the results

DB Storage	Iterations * rows	Percentage of time
Disk	1000 * 1000	85.37%
Memory	1000 * 1000	14.63%

Disk	1500 * 1000	76.36%
Memory	1500 * 1000	23.64%

Disk	5000 * 100	92.31%
Memory	5000 * 100	7.69%

So it looks like it is at least 4 times faster, if the table is smaller and you have more iterations, it gets even faster

I did run into an issue while testing, if I made it execute 5000 times with a 2000 rows table.. I was greeted by the following error

Msg 701, Level 17, State 154, Procedure prTestTVP, Line 7 [Batch Start Line 0]
There is insufficient system memory in resource pool 'default' to run this query.

This code was running on a laptop where I had 40 tabs open in chrome so there was not a lot of free memory, I also didn't create a resource pool, everything was a default setup

If you look at the code you will see that I clear out the table after each iteration.

However the table variable doesn't get out of scope until the loop is finished. In my real time scenario, I don't have this issue, my procs are called by many processes but not in a loop

To read more about this error start here

Be aware of 701 error if you use memory optimized table variable in a loop

This is actually by-design behavior documented in “Memory-Optimized Table Variables”). Here is what is state “Unlike memory-optimized tables, the memory consumed (including deleted rows) by table variables is freed when the table variable goes out of scope)”. With a loop like above, all deleted rows will be kept and consume memory until end of the loop.

There you go.. if you are using table types, switching them to in memory table types might help your application perform better. But of course as I said before... since you can't believe anything on the internets, test for yourself