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