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:
