Onion Ring Buffer?

Ever think to yourself, “hmm, I wish I could generate me some RING BUFFER_OOM errors”?  This request happened to come up tonight.

Of course, the person asking for this was myself, but an answer was found nonetheless.  Below are a couple scripts that can be used to generate RING_BUFFER_OOM errors in sys.dm_os_ring_buffers.

Setup For RING_BUFFER_OOM Errors

A good way to cause RING_BUFFER_OOM errors to occur is to use resource governor and restrict the memory in the SQL Server instance.  The script below can be used to setup this scenario:

USE AdventureWorks
GO

DROP USER [MemoryError]
GO

USE [master]
GO

IF EXISTS (SELECT * FROM sys.sql_logins WHERE name = 'MemoryError')
    DROP LOGIN [MemoryError]
GO

CREATE LOGIN [MemoryError] WITH PASSWORD=N'p@$$w0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO

EXEC sys.sp_configure N'max server memory (MB)', N'50'
GO

RECONFIGURE WITH OVERRIDE
GO

CREATE FUNCTION [dbo].[rgMemoryErrorClassifier]()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @resource_pool sysname

    IF SUSER_SNAME() = 'MemoryError'
        SET @resource_pool = N'MemoryError'
    ELSE
        SET @resource_pool = 'default'

    RETURN @resource_pool
END
GO

CREATE RESOURCE POOL [MemoryError] WITH(min_cpu_percent=0,
max_cpu_percent=100,
min_memory_percent=0,
max_memory_percent=1)
GO

CREATE WORKLOAD GROUP [MemoryError] WITH(group_max_requests=0,
importance=Medium,
request_max_cpu_time_sec=0,
request_max_memory_grant_percent=25,
request_memory_grant_timeout_sec=0,
max_dop=0) USING [MemoryError]
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[rgMemoryErrorClassifier]);
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

USE AdventureWorks
GO

CREATE USER [MemoryError] FOR LOGIN [MemoryError]
GO

EXEC sp_addrolemember N'db_ddladmin', N'MemoryError'

Cause RING_BUFFER_OOM Errors

Next open a new query window in SQL Server Management Studio.  Then logon with the [MemoryError] account created in the first script.  The script below will then

USE AdventureWorks
GO

IF OBJECT_ID('dbo.BigTable') IS NOT NULL
    DROP TABLE dbo.BigTable;
GO

WITH
    l0 AS (select 0 AS c union all select 0),
    l1 AS (select 0 AS c from l0 as a cross join l0 as b),
    l2 AS (select 0 AS c from l1 as a cross join l1 as b),
    l3 AS (select 0 AS c from l2 as a cross join l2 as b),
    l4 AS (select 0 AS c from l3 as a cross join l3 as b),
    l5 AS (select 0 AS c from l4 as a cross join l4 as b),
    nums as (select row_number() over(order by (select null)) as n from l5)
SELECT TOP (100000000) *, NEWID() AS ID, GETDATE() AS Today, n % 100000 AS SampleColumn
INTO BigTable
FROM nums
ORDER BY n
GO

Nearly immediately, the following error should occur:

Cleanup RING_BUFFER_OOM Errors

Once you have the errors generated, this will need to be cleaned up.  The script below will clean all of this up and switch everything back:

IF EXISTS(SELECT * FROM sys.resource_governor_workload_groups WHERE name = 'MemoryError')
    DROP WORKLOAD GROUP [MemoryError]
GO

IF EXISTS(SELECT * FROM sys.resource_governor_resource_pools WHERE name = 'MemoryError')
    DROP RESOURCE POOL [MemoryError]
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

ALTER RESOURCE GOVERNOR DISABLE;
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[rgMemoryErrorClassifier]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[rgMemoryErrorClassifier]
GO

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO

EXEC sys.sp_configure N'max server memory (MB)', N'2048'
GO

RECONFIGURE WITH OVERRIDE
GO

Related posts:

1. Resource Governor Whitepaper
2. Index Those Foreign Keys
3. Can You Dig It? – Parallelism in the Plan Cache