Sql-server – Change bucket count for Primary Key Nonclustered Hash on existing In-Memory table

alter-tableindexmemory-optimized-tablessql serversql-server-2019

I'm on SQL Server 2019 Enterprise edition. I have a table with this definition

CREATE TABLE dbo.MyMemoryTable
(
    FirstId int NOT NULL, 
    SecondId int NOT NULL,
    MyCount int NOT NULL,
    CONSTRAINT PK_MyMemoryTable PRIMARY KEY NONCLUSTERED HASH (FirstId) WITH (BUCKET_COUNT = 16000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

It's prefilled with data that are frequently queried and refreshed. There are few schema bound native compiled procedures.

While the bucket count was ok for some time and was 2x the number of unique values, it's not enough now and I'd like to change it to 32 768. I would like this operation to be online.

In my test environment, I used the following ALTER to change the bucket count:

ALTER TABLE dbo.MyMemoryTable 
ALTER INDEX PK_MyMemoryTable REBUILD WITH(BUCKET_COUNT = 32768);

Which passes without errors, but scripting out the table still shows the previous 16k buckets
as well as DMV's

SELECT  
  QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [table],   
  i.name                   as [index],   
  h.total_bucket_count,  
  h.empty_bucket_count,  
    
  FLOOR((  
    CAST(h.empty_bucket_count as float) /  
      h.total_bucket_count) * 100)  
                            as [empty_bucket_percent],  
  h.avg_chain_length,   
  h.max_chain_length  
FROM  
        sys.dm_db_xtp_hash_index_stats  as h   
  JOIN sys.indexes                     as i  
          ON h.object_id = i.object_id  
          AND h.index_id  = i.index_id  
JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
JOIN sys.tables t on h.object_id=t.object_id
WHERE ia.type=1
ORDER BY [table], [index];

How to achieve this without downtime? I could drop and recreate the table, but I need to drop the schema bound procedures first and that would cause errors.

EDIT: Tested it on a newly created table and it worked.
I've added some data and it worked as well.
I've added more rows than the bucket count, still worked
I've added natively compiled procedure and it worked.

I've tested it again on my real table and it worked now. I have no idea what happened.

The only thing that is suspicious that when I ran the DMV query, it showed me MyMemoryTable but also something called TT___MemoryTable or something like that – maybe that prevented the update?

Best Answer

As Dan Guzman mentioned in the comments, the alter isn't fully online (though should be quick enough). But another alternative that probably has the most minimal downtime is to create a copy of the table with a different name. Then use the sp_rename system stored procedure to rename the old table to a different name, and rename the new table to your old table's original name. This operation is very quick since it's basically just updating a reference in meta data on the server.

Related Question