Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

A-Z of In-Memory OLTP : Garbage Collection (Part 2)

Posted by blakhani on February 11, 2014


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.

USE    SQLSeverHelp_IMO
go
select    * 
from    sys.dm_db_xtp_gc_cycle_stats

image

Each row corresponds to one cycle when GC has scanned committed transactions. If we try to see the difference between start of two rows, its 60000 ticks which is 60 seconds. I have used CTE in below query to find the same.
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  
 
image
 
If we check server startup time in minutes and number of cycles, they would be same.
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)' 
 
image
 
Based on above, its clear that cycle is running every minute. We have not performed any update or delete to generate stale rows so far. Here is the memory used by table. Query available here.
image
 
Now, let’s generate garbage rows and check the DMVs again. We are going to delete 1000 rows.
 
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   

 
As soon as rows are deleted, we would start seeing activity on garbage collection. If we can same query to find cycles again, here is the output
 

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)

 
As we can see above that cycle have become more aggressive (less than 60 seconds) as we did a lot of transaction. Also notice that base generation moves in multiple of 16 (explained earlier) 993-1 = 992 = 62*16. I have to do minimum 16 transactions to increase the base generation.
 
Now, let’s look at output of DMV sys.dm_xtp_gc_queue_stats
 
image
We can see that work was enqueued, got dequeued and current_queue_length is zero. This means there is no garbage data in the queue. Let’s check memory usage of table.
image
This means garbage collection has done it’s work and free’ed up memory used by table as unused memory has increased.
 
We would continue to explore more on garbage collection in next blog as well.
 
 
  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: