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

 

Print | posted on Monday, November 19, 2007 7:30 PM

Feedback

# re: Troubleshooting SQL Server Quirks

Left by jay at 11/22/2007 4:33 AM
Gravatar 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?

# re: Troubleshooting SQL Server Quirks

Left by Mladen at 11/22/2007 8:02 PM
Gravatar 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

# re: Troubleshooting SQL Server Quirks

Left by Jen at 11/22/2007 8:09 PM
Gravatar 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

# re: Troubleshooting SQL Server Quirks

Left by jay at 11/23/2007 3:58 AM
Gravatar I just want to log all about data changing.
Like who/when/what has been changed.

# re: Troubleshooting SQL Server Quirks

Left by jen at 11/23/2007 1:45 PM
Gravatar take a look at those links provided, that will get you started

# re: Troubleshooting SQL Server Quirks

Left by snow boots for women at 10/9/2010 8:22 AM
Gravatar The last procedure is a simple query to retrieve the delta between last and current values. Be sure to schedule the spGetSystemCacheInfo procedure.

# re: Troubleshooting SQL Server Quirks

Left by fur boots at 10/9/2010 8:24 AM
Gravatar SELECT @previous=coalesce(SUM(single_pages_kb + multi_pages_kb)/1024,0)
FROM SystemCache

# re: Troubleshooting SQL Server Quirks

Left by north face jackets on sale at 10/24/2010 10:05 PM
Gravatar The last procedure is a simple query to retrieve the delta between last and current values. Be sure to schedule the spGetSystemCacheInfo procedure.

snow boots for women | columbia jackets | mac makeup | the north face outlet

# re: Troubleshooting SQL Server Quirks

Left by nrerr at 12/25/2010 6:24 AM
Gravatar Elini Watches at great prices, Elini diamond watches, Elini automatic movement, Elini diver watches, Elini man watches, Elini Authentic Watches, Elini Watch, Elini Watches, Elini Nazar Watch on sale at Discount Watch Store. Free sizing service on metal bracelets and the lowest price with our price Elini Watch Espresso. Mac web development, now more productive than caffeine. Download 1.1.2 · Buy Now · Espresso · What's New? Features · Release Notes The most amazing people in the world form the most beutiful country in the world ! Italians often have dark, curly hair, olive skin, and dark eyes. esspresso air filters, check valves, pressure gauges Compressor Accessories & Controls.Campbell Hausfeld, a leader in air compressors, air tools, nailers and tire inflators, can help you with your power tool needs. From spray guns and pressure air compressor parts Online shopping for Cordless Drills from a great selection of Home Improvement; Right-Angle Drills & more at everyday low prices.18 Volt Compact Lithium-Ion Cordless 1/2 In. Driver-Drill Kit. Model LXFD01CW. $179.00 Select to compare 3/8 in. 12-Volt Cordless Drill/Driver Kit cordless drill Free height and weight chart - find out if you're at the correct weight with this free handy chart.Weight Scales for everyday discount prices on Overstock.com! $2.95 shipping and product reviews on Weight Management products. weight scale

# cheap mac makeup

Left by dear at 4/13/2011 3:36 PM
Gravatar I've started monitoring our own servers' threshold and set a starting level until the baseline is mac eye shadow
established. The last procedure is a simple query to retrieve the delta between last and current values. Be sure to mac eyeshadow palette
schedule the spGetSystemCacheInfo procedure.
Comments have been closed on this topic.

Copyright © Jen

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski