Recently, a friend of mine was doing load test on their environment and encountered an odd scenario. For some inexplicable reason, the application was really slow, cpu and memory were extremely high and SQL Server barely responding. There were no waiting tasks and the scheduler is free.
PS informed them it's by design. And in this case, it makes sense. You'll understand why I said that after reading the article http://support.microsoft.com/default.aspx/kb/927396 . By the way, don't make the same mistake of applying the hotfix, it did nothing to fix the problem.
I've started monitoring our own servers' threshold and set a starting level until the baseline is established. The last procedure is a simple query to retrieve the delta between last and current values. Be sure to schedule the spGetSystemCacheInfo procedure.
Remember, this is just to give you a jump start.
use database_name
go
CREATE TABLE [dbo].[SystemCache](
datestamp datetime default getdate(),
[memory_clerk_address] [varbinary](8) NOT NULL,
[type] [nvarchar](60) NOT NULL,
[name] [nvarchar](256) NOT NULL,
[memory_node_id] [smallint] NOT NULL,
[single_pages_kb] [bigint] NOT NULL,
[multi_pages_kb] [bigint] NOT NULL,
[virtual_memory_reserved_kb] [bigint] NOT NULL,
[virtual_memory_committed_kb] [bigint] NOT NULL,
[awe_allocated_kb] [bigint] NOT NULL,
[shared_memory_reserved_kb] [bigint] NOT NULL,
[shared_memory_committed_kb] [bigint] NOT NULL,
[page_size_bytes] [bigint] NOT NULL,
[page_allocator_address] [varbinary](8) NOT NULL,
[host_address] [varbinary](8) NOT NULL
)
GO
create procedure getSystemCacheInfo
as
insert into systemcache([memory_clerk_address] ,
[type] ,
[name] ,
[memory_node_id] ,
[single_pages_kb],
[multi_pages_kb] ,
[virtual_memory_reserved_kb],
[virtual_memory_committed_kb] ,
[awe_allocated_kb],
[shared_memory_reserved_kb] ,
[shared_memory_committed_kb],
[page_size_bytes],
[page_allocator_address],
[host_address])
select [memory_clerk_address] ,
[type],
[name],
[memory_node_id] ,
[single_pages_kb],
[multi_pages_kb] ,
[virtual_memory_reserved_kb],
[virtual_memory_committed_kb] ,
[awe_allocated_kb],
[shared_memory_reserved_kb] ,
[shared_memory_committed_kb],
[page_size_bytes],
[page_allocator_address],
[host_address]
from sys.dm_os_memory_clerks
WHERE name = 'TokenAndPermUserStore'
go
create procedure spGetSystemCacheInfo
as
set nocount on
declare @current decimal(18,2)
,@previous decimal(18,2)
,@percentage decimal(18,2)
,@message nvarchar(2000)
,@status int
,@severity int
SELECT @current=SUM(single_pages_kb + multi_pages_kb)/1024
FROM sys.dm_os_memory_clerks
WHERE name = 'TokenAndPermUserStore'
SELECT @previous=coalesce(SUM(single_pages_kb + multi_pages_kb)/1024,0)
FROM SystemCache
where datestamp=(select max(datestamp) from systemcache)
if @previous>0
set @percentage=((@current-@previous)/@current)* 100
if @current>80
select @message='TokenAndPermUserStore has reached increased by ' + convert(varchar(3),@percentage )+ '%
and Total is ' + convert(varchar(100),@current) + ' MB
Run DBCC FREESYSTEMCACHE (''TokenAndPermUserStore'') to resolve the problem.',@severity=19,@status=1
if @percentage>30 and (@current>50 and @current<80)
select @message='TokenAndPermUserStore has reached increased by ' + convert(varchar(3),@percentage )+ '%
and Total is ' + convert(varchar(100),@current) + ' MB
This is an informational message only. ',@severity=18,@status=0
exec GetSystemCacheInfo
if @message is not null
RAISERROR (@message,@severity,@status) with log;
set nocount on
go