September 3, 2008 at 12:11 pm
We have a Datawarehouse process that runs every Sunday. Last Sunday the process simply stalled.
Server specs:
Windows 2003, Std, SP2 x64
SQl 2005, SP2, Std, x64
8GB RAM
Page file: 4GB (will increase to 8GB)
Below I list what I found in various sql logs.
Any ideas on how to proceed much appreciated.
Barkingdog
P.S. I plan to upgrade the RAM to 16GB but that may not be the issue. It could be a SQL 2005 or
app memeory leak issue.
>>>
2008-08-31 11:53:45.03 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 242284, committed (KB): 7404592, memory utilization: 3%.
2008-08-31 12:48:14.82 spid2s LazyWriter: warning, no free buffers found.
2008-08-31 12:48:15.15 spid2s
>>>
>>>
2008-08-31 18:12:36.26 spid233 Using 'dbghelp.dll' version '4.0.5'
2008-08-31 18:12:36.56 spid233 **Dump thread - spid = 233, PSS = 0x00000001A670BEC0, EC = 0x00000001A670BED0
2008-08-31 18:12:36.59 spid233 ***Stack Dump being sent to C:\mssql2005\MSSQL.1\MSSQL\LOG\SQLDump0001.txt
2008-08-31 18:12:36.60 spid233 * *******************************************************************************
2008-08-31 18:12:36.60 spid233 *
2008-08-31 18:12:36.60 spid233 * BEGIN STACK DUMP:
2008-08-31 18:12:36.60 spid233 * 08/31/08 18:12:36 spid 233
2008-08-31 18:12:36.60 spid233 *
2008-08-31 18:12:36.60 spid233 * Latch timeout
2008-08-31 18:12:36.60 spid233 *
2008-08-31 18:12:36.62 spid233 *
2008-08-31 18:12:36.62 spid233 * *******************************************************************************
2008-08-31 18:12:36.62 spid233 * -------------------------------------------------------------------------------
2008-08-31 18:12:36.62 spid233 * Short Stack Dump
2008-08-31 18:12:37.07 spid233 Stack Signature for the dump is 0x00000000E10C3FED
>>>
>>>
=====================================================================
BugCheck Dump
=====================================================================
This file is generated by Microsoft SQL Server
version 9.00.3042.00
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
>>>
September 3, 2008 at 12:28 pm
had a bunch of these earlier in the year and last year
make sure you specifically set paging file size because in x64 windows the default size is wrong. second enable lock pages in memory and third set a maximum size for sql memory that is around 4GB less than total memory
September 3, 2008 at 6:31 pm
Thanks for the interesting responses.
Also, I don't think the "Enable the Lock Pages in Memory Option" is relevant to my situation. From
http://technet.microsoft.com/en-us/library/ms190730.aspx
I found this quote:
"Locking pages in memory is not required on 64-bit operating systems."
Barkingdog
September 3, 2008 at 6:34 pm
it's not required but it can help
there was a blog post on MSDN a few years ago from one of the SQL 2005 dev's and if you search here, a lot of people recommend it. you have to test it and performance depends on your situation, but it's something to think about.
September 4, 2008 at 2:31 am
Lock pages is ignored by standard edition.
What's your SQL service's max and min memory set to?
Also, check the disk performance. Run perfmon for a while (24 hours or so) preferably over the sunday processing and check the following:
Physical disk: Avg sec/read
Physical disk: Avg sec/write
Physical disk: %Idle time
Physical disk: Avg disk queue length
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply