5

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:

xtp nonsense

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

6

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

0
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.