Tag: XTP Configuration

Fix XTP Configuration Is Invalid Error In Always On

by Dev on

Hello, this article is going to be part2 of How To Fix: XTP Configuration Is Invalid where we have learnt about the SCOM alert, what is XTP in SQL Server and how to fix the same in stand alone SQL Instance.

Here we’ll learn about the steps to fix XTP Configuration is invalid error in Always On SQL Instance. Hence I would like you to first go through the first article.

Steps to Fix Configuration Is Invalid Error In Always On

  1. Calculate Resource Pool Memory
  2. Create a resource pool
  3. Bind the database to the pool
  4. Bring the database offline and then online
  5. Confirm the Binding

Now you may be thinking, well these are exactly the same steps for stand alone SQL Instance. Yes, you’re right, however in case of Always On you need to perform few extra steps for the point no. 4.

I have already explained how to calculate resource pool memory in the first article Link. Here I’ll start from point no.2

2. Create a resource pool

In case of Always On you have to create the resource pool in all the replicas.
Execute the below query on each replica.

 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. XTP Configuration is Invalid

3. Bind the database to the pool:

Execute the below query on all the Replicas

EXEC  sys.sp_xtp_bind_db_resource_pool YourDatabaseName, 'PoolName';

e.g.XTP Configuration is Invalid

4. Bring the database offline and then online:

This is a bit tricky in case of Always On setup. You need to remove the db from Availability Group before you can take the db offline.

Following are the steps:

  • Remove the database from Availability Group
  • Take the database Offline and then Online on Primary Replica
  • Readd the database back to Availability Group
  • Failover the Availability Group to each of the replicas and Failback to the original Primary Replica.
5. Confirm the Binding:

This also needs to be executed on all the Replicas

SELECT d.database_id, d.name, d.resource_pool_id   
FROM sys.databases d WHERE name='SQLGeeksPro' --Change database name
GO

Example Result Set:

XTP Configuration is Invalid in Always On

As you can observe there is an associated resource_pool_id for the example database “SQLGeeksPro”.

Hope this helps. Please feel free to leave a comment.

Tagged as: ,

{ Add a Comment }

How To Fix: XTP Configuration Is Invalid

by Dev on

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. XTP Configuration is Invalid

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.XTP Configuration is Invalid

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

Tagged as:

{ Add a Comment }