Hi There! In this article we are going to discuss about the SCOM Alert “MSSQL On Windows: XTP Configuration Is Invalid”. At first let’s see the alert description:
“XTP configuration for database “DatabaseName” on SQL Server instance “SQLInstance Name”, computer “ServerName” is not set according to the best practices. Database with memory-optimized tables should be bound to a dedicated resource pool.”
This alert points to issues with the configuration of In-Memory OLTP (XTP) features in SQL Server, which can impact database performance and reliability. As per the alert description, the database has memory-optimized tables and there is no dedicated resource pool. That is why you are not going to find out any error in SQL Error log related to this.
What is XTP in SQL Server?
XTP stands for Extreme Transaction Processing, an informal name to describe the In-Memory OLTP feature that powers memory-optimized tables. It is to boost transaction throughput by minimizing disk I/O and maximizing data access speed.
When you enable and create memory-optimized tables, SQL Server requires specific settings and prerequisites to ensure the XTP engine runs smoothly.
What is the Impact on SQL Server?
- SQL Engine will allocate memory to memory-optimized tables from the default pool. This leads to uncontrolled memory consumption, which can cause overall memory pressure.
- Without proper resource pools, SQL Server cannot isolate memory for In-Memory OLTP.
- Result: potential server performance degradation, query slowdowns, or even crashes causing downtime.
How to Fix the XTP Configuration is Invalid Error?
- Calculate Resource Pool Memory
- Create a resource pool
- Bind the database to the pool
- Bring the database offline and then online
Well it is not that simple as it sounds. You need to calculate the memory percentage to allocate to the resource pool.
First of all you need to know the amount of memory needed for the memory optimized tables in the database. You can go through the Microsoft documentation Estimate Memory Requirements for Memory-Optimized Tables for the same.
Ideally this number should be provided by the app team to production DBA to do the rest of the configuration in SQL Server. However in some cases your app team may not be able to help with the information. Then you need to proceed as per the size in pre prod environment by monitoring the growth for couple of weeks.
Calculate Resource Pool Memory:
percentNeeded = memoryNeeded / memoryCommitted * availablePercent
- memoryNeeded: Amount of memory needed for memory optimized tables
- memoryCommitted: SQL Server Max Memory
- availablePercent: Refer the below table:
| Target Committed Memory | Percent available for in-memory tables |
|---|---|
| <= 8 GB | 70% |
| <= 16 GB | 75% |
| <= 32 GB | 80% |
| <= 96 GB | 85% |
| >96 GB | 90% |
For example, If
- memoryNeeded = 16 GB
- memoryCommitted = 32 GB
- then availablePercent (considering the table) = 80%
Hence by plugging real numbers
percentNeeded = 16 / (32 * .8) = 16/25.6 =.625
Converting the result to percentage .625 * 100 = 62.5
percentNeeded = 62.5% rounding of to 63%
Create a resource pool:
Execute the following script to create a resource pool:
CREATE RESOURCE POOL PoolName --Provide the pool name maintaining Organization's naming convention WITH ( MIN_MEMORY_PERCENT = 63, --Adjust as needed/calculated MAX_MEMORY_PERCENT = 63 --Often set to the same value as MIN_MEMORY_PERCENT for Predictability ) GO ALTER RESOURCE GOVERNOR RECONFIGURE;
e.g. 
Just to clarify, resource pool is created at SQL Instance level and not on database.
Bind the database to the pool:
EXEC sys.sp_xtp_bind_db_resource_pool YourDatabaseName, 'PoolName';
e.g.
Next and last step is to take the database offline and than bring it online as can be seen in the messages once you bind the database to the resource pool.
Hope this helps. For Always ON SQL Instance there are few extra steps needs to be followed which we’ll discuss in the next blog Fix XTP Configuration Is Invalid Error In Always On
You may also like to go through the below:
Query to List SQL Database Role Owner Across All Databases
Query To List SQL Database Role Owner
SQL Server FORMAT Function Quick Guide
