Help: SQL Server

In the previous blog of A-Z Series, we started learning about garbage collection; we have learned theory about garbage collection of stale rows in SQL Server In-Memory OLTP tables. If you have not read that, I would like you to start from part 1. There are below DMVs available to check the work done by garbage collection system.

• sys.dm_xtp_gc_stats
• sys.dm_xtp_gc_queue_stats
• sys.dm_db_xtp_gc_cycle_stats – Database Level

Before we move any forward, there is another important concept which would help in reading DMV data which is called as “generation”. We can think of generation as queue. There are 16 queues (generations) available in SQL Server 2014 CTP2. Generation queue holds the transaction which have generated versions. As per Kalen’s whitepaper, this number (16) can be changed later. The details of generation queue can be seen by DMV sys.dm_db_xtp_gc_cycle_stats. We would also see another queue which is equal to number of schedulers/cores on the machine. This GC worker queue contains the actual work which is queued by garbage collection process and statistics can be seen by DMV sys.dm_xtp_gc_queue_stats.

On periodic basis, the transactions which have generated version is moved to the generation queue. GC threads picks up transaction from generation queues and keep them in GC worker queue. The frequency of checking completed transaction is 1 minute, which can be more aggressive if more transactions are waiting to be processed. Once the work is queued, the user threads can co-operate with main GC thread and clean the garbage which is kept in worker queue.

Let’s see this in action. For demo purpose, I have created a wide-column table with around 30K rows. Here is the script which can be used.

-- Create database with IMO Filegroup, If exists drop it.
Use Master
go
If db_id('SQLSeverHelp_IMO') is NOT NULL
drop database SQLSeverHelp_IMO
GO
CREATE DATABASE SQLSeverHelp_IMO 
    ON PRIMARY (
    NAME = [SQLSeverHelp_IMO_data]
    ,FILENAME = 'C:\IMO_Database\SQLSeverHelp_IMO.mdf'
    )
    
    ,FILEGROUP [SQLSeverHelp_IMO_FG] CONTAINS MEMORY_OPTIMIZED_DATA ( 
    NAME = [SQLSeverHelp_IMO_dir]
    ,FILENAME = 'C:\IMO_Database\SQLSeverHelp_IMO_dir'
    ) 
    
    LOG ON (
    NAME = [SQLSeverHelp_IMO_log]
    ,Filename = 'C:\IMO_Database\SQLSeverHelp_IMO_log.ldf'
    ) 
    COLLATE Latin1_General_100_BIN2 
GO


-- Create table in database
-- use the database which is already created
Use SQLSeverHelp_IMO
GO
CREATE TABLE MyFirstMemporyOptimizedTable
(
iID INT NOT NULL,
vFName CHAR(900) NOT NULL,
vSite CHAR(7000) NOT NULL,
CONSTRAINT imo_pk_iID primary key NONCLUSTERED HASH (iID) WITH (BUCKET_COUNT = 1048576),
index imo_idx_vFname  NONCLUSTERED HASH (vFName) WITH (BUCKET_COUNT = 8)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO


Use SQLSeverHelp_IMO 
GO
CREATE PROCEDURE [dbo].[InsertName] 
  WITH 
    NATIVE_COMPILATION, 
    SCHEMABINDING, 
    EXECUTE AS OWNER
AS 
BEGIN ATOMIC 
  WITH 
(TRANSACTION ISOLATION LEVEL = SNAPSHOT,
 LANGUAGE = 'us_english')
 
DECLARE @counter INT 
SET @counter = 1

WHILE @counter <= 30000
  BEGIN 
      INSERT INTO dbo.MyFirstMemporyOptimizedTable 
      VALUES      (@counter, 
                   'SQLServer-Help.com', 
                   'Balmukund Lakhani');
      INSERT INTO dbo.MyFirstMemporyOptimizedTable 
      VALUES      (@counter + 1, 
                   'ExtremeExperts.com', 
                   'Vinod Kumar M');
      INSERT INTO dbo.MyFirstMemporyOptimizedTable 
      VALUES      (@counter + 2, 
                   'SQLAuthority.Com', 
                   'Pinal Dave') 

      SET @counter = @counter + 3 
  END
END
GO

exec InsertName
go

After creating the objects and pumping the data, I have recycled SQL Services to clear the data in DMVs. It’s worth mentioning that this should not be done with production server. Post restart, I waited for some time so that sys.dm_db_xtp_gc_cycle_stats has some rows. Recall that GC thread starts at approximately 1 min. I have not done any transaction and that’s why “base_generation” stays as 1. We can also see 16 generations as column in each row.

WITH tbldifference 
     AS (SELECT Row_number() 
                  OVER ( 
                    ORDER BY ticks_at_cycle_start) AS RowNumber, 
                cycle_id, 
                ticks_at_cycle_start, 
                base_generation 
         FROM   sys.dm_db_xtp_gc_cycle_stats)
-- CTE 
SELECT Cur.cycle_id, 
       Cur.ticks_at_cycle_start                            AS CurrentTickstValue , 
       Prv.ticks_at_cycle_start                            AS PreviousTicksValue, 
       Cur.ticks_at_cycle_start - Prv.ticks_at_cycle_start AS Difference, 
       Cur.base_generation 
FROM   tbldifference Cur 
       LEFT OUTER JOIN tbldifference Prv 
                    ON Cur.RowNumber = Prv.RowNumber + 1 
ORDER  BY Cur.ticks_at_cycle_start  

USE SQLSeverHelp_IMO
go
select count(*) from sys.dm_db_xtp_gc_cycle_stats
go

SELECT Datediff(mi, last_startup_time, Getutcdate()) 
       'Minutes Since Last restart' 
FROM   sys.dm_server_services 
WHERE  servicename = 'SQL Server (MSSQLSERVER)' 

DECLARE @i INT 
SET @i = 1 
WHILE ( @i <= 1000 ) 
  BEGIN 
      DELETE FROM MyFirstMemporyOptimizedTable 
      WHERE  vFName = 'SQLServer-Help.com' 
             AND iID <= @i * 3 
      SET @i = @i + 1 
  END   

cycle_id             CurrentTickstValue   PreviousTicksValue   Difference           base_generation

——————– ——————– ——————– ——————– ——————–

…. Truncated for clarity….

41                   92361828             92301828             60000                1

42                   92421828             92361828             60000                1

50                   92805281             92745281             60000                993

(50 row(s) affected)