What are all the XTP_THREAD_POOL processes doing?

Question

I just ran sp_who on my SQL Server 2016 instance that uses MEMORY_OPTIMIZED tables, and I see several XTP_THREAD_POOL processes running:

Some other details about the output:

• There are exactly 6 XTP_THREAD_POOL rows in the resultset
• My system has two logical cores
  • Specifically, it's a VM with 2 sockets - each socket housing a 1-core processor
• All of these tasks are running as background (sa)

What are these processes doing? I'm not finding much on Google about this task name.

TIA

Answer 1

Information about these tasks is, indeed, hard to come by.

The docs (Creating and Managing Storage for In-Memory OLTP -> Scalability) say that

In SQL Server 2016 (13.x), there are multiple concurrent threads responsible to persist changes to memory-optimized tables.

And while the docs don't mention the "cmd" value that might be associated with these concurrent threads, there is a PowerPoint from Bob Ward that links to the doc page above and adds more details about this process:

You may see these as command = XTP_THREAD_POOL or XTP_PREEPMTIVE_TASK

So these tasks are keeping the data and delta files used by in-memory OLTP up-to-date. They run in the background all the time. While they are idle, they rack up DISPATCHER_QUEUE_SEMAPHORE waits.

---

The presentation also mentions that:

Pools should get no bigger than # logical CPUs...

So it's a little surprising to see 6 of these processes running on a 2-processor box. This might be a bug, or things could have changed since Bob's PowerPoint presentation. Hopefully someone with more Hekaton smarts than me will come by and weigh in.

HTH

Answer 2

I'm using SQL 2019 and notice these XTP_THREAD_POOL cmd threads show up when I use Studio Manager (SSMS) and use a user database.

select Hostname, program_name, db_name(dbid) AS 'Database', *
    FROM master..sysprocesses
        WHERE spid >50

Shows these new threads. I'm not using in-memory indexes or anything unless 2019 has this enabled.

SQL 2019 CU 08 shows the cmd threads.

SQL 2019 CU 05 does not.