User AWE to allocate memory
-
Where do I find this option in SQL 2012? I have windows 2008R2. How do I make ure AWE is allocating memory?
Also for 60GB physical memory server, what should be my max memory configuration for SQL server? Any help would be greatly appreciated.
-
You dont.
http://support.microsoft.com/kb/2644592
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
It's only necessary for 32 bit. If you're still running 32 bit SQL now, it's well past time to reconsider and install the 64 bit version instead
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
In the SQL configuration what should I put on max memory?
I'm on 64 bit VM. Running 2012. Have 64 gb ram.
-
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
It seems like SQL server is leaking memory. I assign 30 GB to sql server memory. If I look at task manager, 30GB of Memory is used. What are the counters that I can look at and their values (on perfmon) to make sure SQL server is
the one that is leaking memory. What can I do to fix it?
-
Why do you think that SS is leaking memory? When you set Max Server Memory it only applies to the buffer pool. SQL Server can use memory in other areas other that the buffer pool. Did you download that free ebook that Gail mentioned? Please read chapter 4 on Memory Management.
-
SQL_Surfer (1/16/2014)
It seems like SQL server is leaking memory. I assign 30 GB to sql server memory. If I look at task manager, 30GB of Memory is used.You told it to use 30GB, it's using 30GB, what's the problem with that?
If you don't want SQL using 30GB, then lower max server memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks Gail. Little bit off the topic. Reading through the book; I came to know about Total Server Memory and Target Server Memory. Couple of my instances has both of these values equal. Does this indicate memory pressure? Do I need to change max server configuration? Use AWE to is enabled. This is for 2005 sql server.
-
First question I would have is, are you having performance issues now? or is this for a baseline?
Total Server Memory is the total memory that has been allocated to SQL Server and Target Server Memory is the memory that SQL Server wants. If you notice Target is more that Total then you maybe experiencing memory pressure, but there are other counters that you should also look at too. I would also look at Page Life Expectancy, Memory Grants Pending, Lazy Writes/sec, Checkpoint Pages/sec. and compare them to my baseline.
Start with this query (from Glenn Berry's Diag Scripts[/url]) and see what the System Memory State is:
SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)],available_physical_memory_kb/1024 AS [Available Memory (MB)],
total_page_file_kb/1024 AS [Total Page File (MB)],
available_page_file_kb/1024 AS [Available Page File (MB)],
system_cache_kb/1024 AS [System Cache (MB)],
system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE)
-
Have had couple of instances where we needed to rebuild tempdb...and several instances where our BCP just quits...with the following message:
SQLState = 08001, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to complete login process due to delay in opening server connection
BTW, I'm getting below error. Those were for 2005 server.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.dm_os_sys_memory'.
-
BTW, I'm getting below error. Those were for 2005 server.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.dm_os_sys_memory'.
Your question is posted in the SQL Server 2012 forum so I gave you the script for that, but if you follow that link I gave to Glenn's blog he has scripts for 2005 as well.
-
SQL_Surfer (1/16/2014)
Thanks Gail. Little bit off the topic. Reading through the book; I came to know about Total Server Memory and Target Server Memory. Couple of my instances has both of these values equal. Does this indicate memory pressure?No.
As for AWE being enabled, is that a 32-bit instance?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Yes it is 32 bit instance.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply