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_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
43 92481828 92421828 60000 1
44 92504781 92481828 22953 1
45 92564781 92504781 60000 993
46 92565281 92564781 500 993
47 92625281 92565281 60000 993
48 92685281 92625281 60000 993
49 92745281 92685281 60000 993
50 92805281 92745281 60000 993
(50 row(s) affected)