.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

memory configuration for better performace SQL Server 2008 databse

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :

I want to know what should be best memory configuration for SQL Server 2008 R2.


View Complete Post

More Related Resource Links

WmiPrvSE.exe causes a huge memory leak in SQL server 2008 R2, why?

My OS is windows server 2008 r2 data center 64 bit and my SQL server is server 2008 R2. The problem is when I open sql server, the WmiPrvSE.exe process consumes 20 - 60kbs every second. Several hours later, it eats up at least 200mbs memorry and this process seems like it will never stop. Can any experts give me some ideas to fit this mess? PS: My mainboard is Asus striker II formular, cpu is intel quad-core. Also, all my software are updated to latest version.

SQL server memory / speed configuration

Let me preface this by saying I'm aware of SQL server memory architecture, i.e it takes as much as it can get it's hand's on ...I've read through all the post's on here and they have been very helpful ... However if someone could take a look at these these configuration values ... I'm trying to get a new server dialed in, I'm still having some user end speed issues, and application hangup's that seem sql related ... I'm by no means a dba so if someone who's knowledgeable could point me in the right direction I would appreciate it ... I feel like something is not configured properly ... The box has Microsoft SQL Server 2008 (RTM) 64-bit on Windows NT 6.0 <X64> Service Pack 2 Dell Power Edge R10 16 Logical CPU, 8 Hyperthread 12gig of memory (Right now SQL has 10/12 gig allocated in the task manager ( I know this is not accurate ) I'm adding another 12 gig tomorrow ... So the machine will be running 24 gig) There's two 9 gig databases running on the machine with several 4-6 million row tables .... Server is SQL Dedicated Configuration Values   Page Life Expectancy 18,038 under moderate load, tho I've seen it as low as 1,200 under heavy load .... Buffer Pool Usage CACHESTORE_SQLCP 2778616 OBJECTSTORE_LOCK_MANAGER 38816 MEMORYCLERK_SOSNODE 20936 CACHESTORE_OBJCP 18992 CACHESTORE_PHDR 16472 USERSTORE_SCHEMAMGR 12184 USERSTORE_DBMETADATA 8264 USE

SQL Server Reporting Services 2008 R2 - The configuration system has already been initialized

After correcting issues related to .NET 4.0, I am now encountering the error below. It occurs when I attempt to access one of the two web sites. I've been tracking my install/config progress here: http://serverfault.com/questions/178353/what-would-cause-a-500-internal-server-error-when-accessing-the-report-manager-ur   Error Log: appdomainmanager!DefaultDomain!ae8!09/07/2010-14:47:12:: i INFO: Appdomain:13 ReportServer_11-11-129283588326974843 started. appdomainmanager!DefaultDomain!ae8!09/07/2010-14:47:12:: e ERROR: AppDomain ReportServer_11 failed to start. Error: The configuration system has already been initialized. library!DefaultDomain!ae8!09/07/2010-14:47:12:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerHttpRuntimeInternalException: Failed to create HTTP Runtime, Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerHttpRuntimeInternalException: An internal or system error occurred in the HTTP Runtime object for application domain ReportServer_11.  ---> System.InvalidOperationException: The configuration system has already been initialized.    at System.Configuration.ConfigurationManager.SetConfigurationSystem(IInternalConfigSystem configSystem, Boolean initComplete)    at System.Web.Configuration.HttpConfigurationSystem.EnsureInit(IConfigMapPath configMapPath, Boolean listenToFileChange

Not Releasing memory in sql server 2008 R2

HI I have a server with 8 Gb Ram I installed Sql server 2008 R2 Enterprise Edition on 64 bit platform Enterprise edition,in that for Sql server I allocated 6 GB of Ram I enable the AWE oprtion as well but in processing time it taking above 6 Gb ram like 7.gb or more after query completing its not release the memory how can I resolve this issue. Actually when we running quries it taking the memory and memory increasing gradually after executing the query its not releasing the memory per example I set maximum 6 GB per Sql server then I run the query memory will increase gradually like 3.4 gb,3.7 gb, 4.5gb,and so on it will reach 7.56gb then after it steadily over there not come to down I am using X64 bit windows server 2008 sql server 2008 R2. My system configuration is sql server 2008R2(10.0.3798) windows server 2008 X64 Ram is 8GB

Configuration Wizard - Stuck on Microsoft SQL Server 2008

I have tried this three times and don't know what to try to be different. Configuration Wizard - Microsoft SQL server 2008  R2 Fails on Microsoft SQL server 2008  R2 Microsoft SQL server 2008  R2 is required in this training Kit Down load I have go though the download process at least 3 times and the configuration wizard still gets stuck on this step.   Any suggestions are appreciated.   I have a Window 7 PC.  

Memory consuming very hign after installing the SQL Server 2008 R2 64 bit

Hi all I have a  Server with  SQLServer 2005 64 bit and I installed SQL Server 2008 R2 64bit and I moved the Databases and logins to SQL Server 2008 R2.After that (to night) I ran Integrity check + rebuild index + update statistics jobs. after that I found SQL Server consumed more memory and not released.what was the reason behind and how to clean that memory. Please guide with query or with an idea, your help is greatefull for me.   Thanks in Advance    SNIVAS

SQL Server 2008 R2 on Window Server 2008 Physical Memory keep growing


We are running SQL Server 2008 R2 on on Window Server 2008 (x64).

Once in every 2 weeks we end up restarting the SQL Server, due to  Physical Memory keep growing.

Is there a good way to pinpoint the problem what cause this Physical Memory to grow?

Thank you

Armine Bell

What are the memory limits of SQL Server 2008 Workgroup edition (32 bit/non-R2) running on Windows S


We have a situation where one of our customers is running Windows 2003 Enterprise Edition 32bit with 8 GBs of physical RAM. Upgrading to Windows Server 2008 64bit is not an immediate option (although I wish it were). All the documentation about SQL Server 2008 Workgroup edition indicates that the new version will address 4 GBs of memory however it does not distinguish between 32bit vs 64bit.

Previously we have run SQL Workgroup 2005 under Windows 2003 and have applied the /3gb switch in boot.ini to get SQL to recognize the maximum amount of memory possible. Now that we are using SQL 2008 with the /pae switch, do we need to still specify the /3gb switch or can SQL 2008 32bit actually see more than the 3GBs and address 4? I haven't been able to find any information on this configuration.




SQL Server 2008 Memory Usage


We are using SQL Server 2008 x64 SP1 CU8 EE, and we have 32 GB of RAM on the server. We currently have the instance restricted to 14 GB of RAM, the "Lock Pages in Memory" security setting on the service account, and AWE enabled in SQL Server. This server is part of a 3 node active/active/passive cluster. My question is:


How do I know how much memory sql server is actually using, or if the server is under memory pressure? 

John M. Couch

SSAS 2008 taking all server memory


We just upgraded to SSAS 2008 64 bit (and windows 2008 R2) a couple of weeks ago and now we have an issue that I can't seem to figure out.  Our cubes have not changed at all.  Previous to the upgrade we were running server 2003 and SSAS 2005 54 bit.

The issue is over the past week the SSAS servers have decided to take up almost all the memory (about 90%) on the server and render itself useless.  It is the SSAS service that is taking up almost all the memory and if I stop other services to free up some it just takes that up too.  So here is a break down of the issue and what I have tried.  The issue seems to be happening faster and faster now so I am at a loss currently.

Setup that has issue:

  • SSAS 2008 SP1 64 bit on SQL 2008 R2
  • Default configs as that worked previously in 2005
  • not structure changes
  • machine has 2 quad core procs and 16 GB mem
  • accessed via web calls and linked server on SQL server on different machine

What I have tried

  • install CU10 (due to other issue)
  • rebuild aggregations
  • reduce memory limits to 40% for low and 50% for max respectively

Results of what I have tried so far: memory still hits the 90% within 24 hours so that tells me the changes have done nothing.  I don't know what to do. <

Report Server Instance not shown in SQL 2008 R2 version for Configuration Manager



I am using Windows Server 2003. I have installed SQL Server 2005 and SQL 2008 R2 version in the machine. The sql server 2008 R2 version has the instance name as SQL2008R2

I am trying to configure "Reporting services Configuration Manager" for the SQL 2008 r2. I gave the machine name and hit the Find button, I am not able to get the Report server instance name.

But when I gave the server name, i am able to browse to the folders page in the Internet explorer. so the server is available . I gave like this in the IE, and I am able to see the page:

http://Server IP/Reports_SQL2008R2/Pages/Folder.aspx

In the eventvwr, I found the following errror "The Drive:\Program Files\Microsoft SQL Server\MSRS10_50.SQL2008R2\Reporting Services\ReportServer\RSReportServer.config file failed to load. This error may occur if the XML is not valid. "

How to get the Report server instance name in the configuration manager.

Thank you

SQL Server 2008 - High Memory Usage


Hi ,

  We are facing problem with the high memory usage with SQL Server 2008. 64 bit , standard edition sp2 . 8 GB RAM , 8 processors .

Memory usage keep on growing ..sometimes it will reach to 7.5 GB also .... and its not coming down ..untill we restart the service...

These are the details of DBCC MEMORYSTATUS :: Please suggest how to overcome this problem

Memory Manager                           KB
---------------------------------------- -----------
VM Reserved                              8484792
VM Committed                             4220368
Locked Pages Allocated                   0
Reserved Memory                          1024
Reserved Memory In Use      &n

SQL Server 2008 R2 on Windows Server 2008 R2 Memory Usage issues and not giving memory back


Okay so i have a physical server i am trying to replace with a virtual server. Its doing a large load process every night, about 190GB on one disk.

The VM is:

On vSphere 4.1 ESXi

O/S is Windows Server 2008 R2 Enterprise

SQL version is SQL Server 2008 R2 Enterprise.

4 vCpus

Now 20GB of memory

Disks assigned to this virtual are all seperate LUNs except for the O/S which is on a shared LUN which is not busy. So 1 LUN/Datastore per 'disk drive on this SQL virtual. Data/Log/TempDB/Backup are on their own LUNs.

All the recommended exclusions are in place for McAfee on a SQL Server.

ESXi 4.1 Enterprise host is not overcommitted. Has 4 x 6core 2.6Ghz AMD processors. BL685c G6 and 128GB of memory. Almost nothing else is running at the time i am testing this load and host is not overcommited or stressed or anywhere near it.

SQLServer process shows 127,236K Working Set and 130,040K Peak working set and memory Private working set 88,828k and Commit Size of 302,792k.

It seems when i check the memory usage in task manager it doesn't show much. In Windows 2003 X64 in would show the GB being used, like 8GB right in task manager. But i don't see that in Windows 2008 R2 task manager. I downloaded SysInternals and it shows the memory on the Windows 2008 r2 box as 15Gb of AWE memory. How can this be? The case is th

SQL Server 2008 R2 Express - Protected Memory error


Am trying to install SQL Server 2008 R2 Express Database with Advanced Services 64-bit on Win7 Enterprise N 64-bit standalone.

Had previously installed SQL 2005 Express with tools, but that has been completely uninstalled and computer restarted.

Have Msoft .NET Framework 4 Client Profile plus Msoft SQL Server native Client currently installed.

Chose download from here (http://www.microsoft.com/express/Database/InstallOptions.aspx) and everything seemed to be running smoothly until during New Installation phase of SQL Server Installation Center, setup encountered "Attempted to read or write protected memory...Error code 0x84B10001."

Have been searching for solutions, but most are dated back in 2007-2009, and I'm hoping (!) there are some later solutions available. What are my options?

Thanks in advance!           ~chornung

SQL server 2008 memory management


I want to talk about one of the most discussed SQL issues. Memory memory management.

I am working on a SQL server 2008 R2 with all the combined DBs of 60GB. User activity is not very high. This is sharepoint DB. Most of the time CPU utilization is not very high but memory is consumed around 98%.

Current memory is 12GB. How can I calculate the memory required on this server.

Rama DBA

SQL Server 2008 R2 on Windows 2008R2 Ent - Drives+Raid Configuration + W2008 Disk Initialization


Hi, I just want to make sure that we setup this server the best way possible as we are moving from a previous box where

performance was affected mainly because of partitioning and SQL memory optimizations.


The Hardware:

Dell PowerEdge R710 

  • Intel® Xeon® X5660, 2.8Ghz, 12M Cache,Turbo, HT, 1333MHz
  • 64GB Memory (8x8GB), 1333MHz Dual Ranked RDIMMs
  • PERC H700 Controller
  • 8 x 300GB 10K RPM Serial-Attach SCSI 6Gbps 2.5in Hotplug Hard Drive
  • Virtual Volume 1: 2 Drives = Raid 1 - for the OS
  • Virtual Volume 2: 2 Drives = Raid 1 - for the ldf's
  • Virtual Volume 3: 4 Drives = Raid 10 - for the mdf's

Windows 2008R2 64bits is installed on Volume 1.

- I would like to know what suggestions you might have for the Initialization of the other 2 volumes (MBR or GPT).

- Any other settings like partition align = 64

- Memory Settings at the OS level.

- Memory Settings on SQL.

- Any other recommendations.

Cluster Security Policy -- Configuration in SQL Server 2008 R2 (Side by Side with SQL Server 2000


Hi all

Please I have SQL Server 2000 running on Windows 2003 SP2 (A/P) Cluster,Now we are adding new SQL Server 2008R2 Instance to Cluster.

I want know "Database Engine Domain Group","SQL Server Agent Domain Group" section,can I select excisting SQL Server 2000 Engine Domain group or I required to Configure  some thing else.


How to find out "Database Engine Domain Group","SQL Server Agent Domain Group" of  excisting SQL Server 2000.


Thanks in Adavnce



ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend