Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mattd
Starting Member
11 Posts |
 Posted - 2003-07-08 : 09:07:13
|
| Hi all,I'm having a recurring problem with my ASP/SQL Server 7 website. The website will be running smoothly, with a fairly consistent level of (externally visible) performance, until, after about a week, I'll suddenly start to get the following error...Microsoft OLE DB Provider for ODBC Drivers error '80040e14'[Microsoft][ODBC SQL Server Driver][SQL Server]There is insufficient system memory to run this query....wherever a SQL query is made. Rebooting the SQL Server machine clears up the problem, until it reappears about a week later.Examining the SQL error log, everything seems fine for days, until the following messages start appearing each time a stored proc attempts execution:WARNING: Clearing procedure cache to free contiguous memory.Buffer Distribution: Stolen=3562 Free=128944 Procedures=20 Inram=0 Dirty=69219 Kept=27 I/O=0, Latched=0, Other=21420Buffer Counts: Commited=223192 Target=223192 Hashed=90666 InternalReservation=82 ExternalReservation=0 Min Free=81Procedure Cache: TotalProcs=2 TotalPages=20 InUsePages=19Dynamic Memory Manager: Stolen=3582 OS=439 General=3864 Query Plan=77 Optimizer=6 Utilities=5 Connection=69Global Memory Objects: Resource=443 Locks=1639 XDES=1 SQLCache=94 Replication=2 LockBytes=2 ServerGlobal=20Query Memory Manager: Grants=0 Waiting=0 Maximum=161478 Available=161478Does anyone speak the language of SQL Server internals, who can tell me what this means?Just from the behavior I'm guessing there's a loop or somesuch in my code that's eating procedure cache memory, but what's the best way to track and pinpoint the stored proc where it's occurring? I was thinking I could use Profiler to track the start and completion of procs, then get the longest running from that. But is there any way to see at a granular level what's impacting the memory?I've been going through KB articles as fast as I can read them (currently trying things suggested in "HOW TO: Troubleshoot Application Performance with SQL Server" and "HOW TO: Troubleshoot the Performance of Ad-Hoc Queries") but I'm hoping someone out there may have run into something similar before and be able to offer suggestions.This is SQL Server 7, running on a dedicated Windows 2000 Server with 2GB RAM and dual 1.8 GHz processors. I'm using the Full Text Search add-in, and as per Microsoft's recommendation set the server's total virtual memory to 6 GB and the SQL server max memory setting to 3 GB.Thanks for any and all advice,matt |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-07-08 : 10:07:52
|
| You're generally describing the effects of a slow memory leak, but it's not necessarily caused by SQL Server. The log entries you mentioned are the efforts of SQL Server to deal with an ever-shrinking heap as the leak gets worse.1) What else are you running on this machine?2) Are you boosting thread priority or using NT fibers?3) At the moment just prior to wanting a reboot, how much memory is sqlsrvr.exe allocating?Jonathan{0} |
 |
|
|
mattd
Starting Member
11 Posts |
Posted - 2003-07-08 : 10:41:35
|
quote:
1) What else are you running on this machine?2) Are you boosting thread priority or using NT fibers?3) At the moment just prior to wanting a reboot, how much memory is sqlsrvr.exe allocating?
1) Nothing except for the virus scanner (Network Associates NetShield).2) Boosting priority yes, fibers no.3) Not sure...I can try to find out, but it may take several days before the condition occurs again.Thanks much for your help!matt |
|
|
|
|
|
|
|
|
Insufficient system memory to run this query...