sql 2005 mem ? round 2

Printable View

10-16-2006, 11:08 AM
timc

sql 2005 mem ? round 2

I have a sql 2005 enterprise edition running on win2003 with 16 gig of memory. I was getting errors on sql server while running a large query. I have the same boot.ini on a sql 2000 server running on win2003 and it utilizes all the memory.

now have the following for my boot.ini
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINDOW S
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windo ws Server 2003, Enterprise" /fastdetect /PAE /NoExecute=OptIn

Is there a way with a dbcc command I can see if sql 2005 is using the 15 gig I have it set to? I run sp_configure and it shows 15 gig as the setting
10-16-2006, 11:34 AM
timc

I ran dbcc memusage and got the following

VM Reserved 1666956
VM Committed 79368
AWE Allocated 2236416
Reserved Memory 1024
Reserved Memory In Use 0

It seems my awe is high, am I correct in that assumption?
10-16-2006, 02:05 PM
rmiao

Did you enable awe and restart sql? Check 'sqlserver:memory manager target server memory' and ''sqlserver:memory manager total server memory' in perfmon.
10-16-2006, 02:09 PM
timc

Yes, my total sql server memory shows 260864, while total shows
12582912. I still think I am not getting past the 2 gig. 16 gig physical on box
10-16-2006, 02:38 PM
rmiao

What's run_value of 'awe enabled' in sp_configure?
10-16-2006, 03:13 PM
timc

run value is 1
10-17-2006, 09:26 AM
rmiao

You sure you have sql2k5 enterprise edition? What's total db size? How did you set max memory to 15gb? Does sql service account have 'Lock pages in memory' user rights in OS?
10-18-2006, 02:29 PM
timc

FInally got it to work, has MS on the phone. I set the memory done from 14 to 12 and it worked. Rebooted, set it to 14 and now it works. MS could not tell me why
11-21-2006, 03:23 PM
ScottShelby

Tim,

Can you please let me know exactly what MS had you do and the result you are getting back from the DBCC Memorystatus command? We are faced with a similar issue and it looks like sql is only using a portion of what is actually allocated. Here are our stats...

Memory Manager KB
------------------------------ --------------------
VM Reserved 1674140
VM Committed 104384
AWE Allocated 6747704
Reserved Memory 1024
Reserved Memory In Use 0

Thanks,
Scott
11-21-2006, 03:34 PM
timc

What does your sql memory show in the counter for sql memory. We rebooted the machine and it started working. I can send you my boot.ini if you give me email
11-21-2006, 03:59 PM
ScottShelby

Thanks for the fast reply. I just sent you a PM with my contact info.

Scott