Jen Blog

Troubleshooting SQL Server Quirks

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

 

Legacy Comments


jay
2007-11-22
re: Troubleshooting SQL Server Quirks
I'm doing an Audit system. i wonder how can i log all DML operations into my table?
is it possible to track which user affect which data, and when they dit it In SQl Server 2005?
Do you have any suggestions?


Mladen
2007-11-22
re: Troubleshooting SQL Server Quirks
jay this might help you:
http://www.sqlteam.com/tag/service-broker

if you don't want it to be async you can just use normal triggers

Jen
2007-11-22
re: Troubleshooting SQL Server Quirks
Register at www.sqlteam.com and participate in the discussion with regards to audits. Lots of experts to help you out... http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52356

Although you need to assess how much changes you want to log, if it's a lot go for a log reader instead like apexsql

jay
2007-11-23
re: Troubleshooting SQL Server Quirks
I just want to log all about data changing.
Like who/when/what has been changed.

jen
2007-11-23
re: Troubleshooting SQL Server Quirks
take a look at those links provided, that will get you started