We have Windows Server 2008R2 installed on VM Server.On that we have three SQL instances running. From few past months we are observing physical memory is going high. Earlier we observed it was at 86-88%, now it is 96-97%.We have 16RAM & 8 CPU cores on VM. what is the best and ideal configuration so that we can rectify the high physical memory issue.
View 13 RepliesEager to know to set the Max Value of Maxtransfersize and Buffercount adjustment but also anxious to know Physical memory impact.URL...The max size that can be used is the (Buffer Pool’s To Physical Memory / 16). As returned from the GlobalMemoryStatusEx (ullTotalPhys ) API call. HereBob dorr saysBuffer Pool’s To Physical Memory/16 what it means? (ie. Buffer Pool/ Physical memory/16.0 or (Physical Memory - buffer pool)/16
View 9 Replies View RelatedIs there an alert for physical memory in SQL2K5? My requirement is - I should get an alert when the free space on a particular drive comes below a threshhold.
------------------------
I think, therefore I am - Rene Descartes
Environment: Win2003 SP1, 32 bit, SQL Server 2K5
My server has 16GB RM but it is using only 3GB. And I see my server is using 3GB of Virtual Memory, too. Why my physical memory is not being utilized? How can I increase Physical Memory usage and decrease VM usage?
Canada DBA
We have a sql 2005 server with 8GB of RAM. Task Manager shows that it only has 198,250k (about 200 MB) of Physical Memory available. I wonder if that number should be higher.
What can I check (Perfmon counters? Which ones?) to indicate it that's too little RAM, the right amount, or too much.
Thanks,
Barkingdog
We are currently running a server with standard edition Windows Server 2003 R2 with standard edition SQL Server 2005 SP2.
The server has 4GB of memory of which 3GB is dedicated to SQL and 1GB to the OS via the use of the /3GB switch.
If we were to increase the physical memory to 12GB (and carve out 2GB for the OS and 10GB for SQL) would we have to upgrade the OS to Enterprise version to access the additional 8GB of memory we plan to add?
Thanks ALL!
I have a few in house developed application (VB based) that access the SQL server for adding, appending , creating tables. The application does the changes thru queries dynamically generated at the application level.
My MS SQL Server runs on a PIII / 256 MB Ram / 18 GB HDD
The problem is that the memory utilization of SQL server keeps growing constantly. Out of 512 MB (256 Physical + 256 Virtual) available teh memory utilization reaches a level of 490 MB and statys constant. Though SQL Server shows a utilization of 150 MB.
I suspect that SQL is not releasing memory back to the system. Please help in resolving. The problem may lie at the applications developed.
Jdindian
hi all
I got a small MSSQL server.. total database file size less than 7GB. with 2G rams installed 2 cpus. but for some reason when i check the task manager process mem usage is over 1.7G. the sql server memory setting in on Dynamically not fixed. and maximum 2G i believe is default. anyway. my question is over 1.7 memory usage is too high? because i dont have alot of transaction going. and cpu usage is very low. wondering if this's normal or not. and if is not normal . what cause the memory usage so high...... and how can i adjust back to normal. ? can anyone help me out? or any suggestion? thanks
I have a 2003 server with sql 2005 on it and the sqlservr.exe is using 880 meg of memeory and it will climb to 1.4 gig. if i reboot server it will go back to 100 meg and slowly climb back up. any ideas i am not a sql guy.
View 2 Replies View RelatedI am trying to attach a database to SQL 2005. This database has not previously been attached. I have only just installed SQL.
I get the following message: Unable to open physical file "C:ArrowSQLArr@DataArrow_data.mdf" Operating system error 5: "5(error not found)" (Microsoft SQL Server: Error 5120)".
I have loaded SQL and the database fiel and directory with the same user account which is a local administrator on this machine. I have checked that I have read/write access to the file.
The machine runs Windows Vista Business. SQL has SP 2 loaded. What causes this?
Hi,We have a prod server running on SQL server 2000 64 bit. It is a4cpu server with 16GB of RAM. we have a maxmemory setting of 15.5GBfor sql server. Inspite of 15GB being available for sql server, itstill uses paging file space, a lot. When looking thru task maanger wecan see sql server using 15.5GB of Memory usage and 22GB of Virtualmemory usage. I don't understand why it should even be using closer to7GB of Paging space, when it has so much memory. How does SQl serveruse Virtual memeory vs Physical memory?HAs anyone seen this before.ThanksGG
View 2 Replies View RelatedHello,
Question:
How can I keep my thread alive after an out of memory exception? That is, I understand that sometimes a server may be unable to satisfy a memory request, but I'm okay with waiting -- I'm not okay with being terminated (think of the reaction to Oliver asking for some more). I would think that, in general, when any application makes a request for a resource that is currently unavailble, but may be available at another time, that application (process/thread/fiber) would be put in a Wait Queue for that resource. On a high concurrency system, this could obviously lead to deadlocks; however, I think in the situation I describe below, the killing is overkill.
Discussion & Background:
In my project, I have a SqlFunction, we'll call "SqlDecimal BigFunction()" that will allocate a large chunk of memory (~3MB) and can take anywhere from 20ms to 500ms to complete (on my system, assuming no other processor load). There are also Functions that are used to set control points for BigFunction (implying thread/fiber state -- or, if there is a distinction, Transaction state), which we will call "SqlBoolean SetControlPoint(SqlInt32 x, SqlInt32 y)". The 3MB requirement is constant, regardless of the number of control points. (Incidentally, the actual implementations of these functions are in a referenced assembly)
In Code:
////////////////////////////////////////////////////////////////////////////////////////////////////
[ThreadStatic] // UNSAFE
static externalAssembly.MyClass myObj;
[SqlFunction]
SqlDecimal BigFunction()
{
if(myObj == null) return -1;
// DoSomeWork will do something like, byte[] b = new byte[3 * 1024 * 1024];
return externalAssembly.DoSomeWork(myObject)
}
[SqlFunction]
SqlBoolean SetControlPoint(SqlInt32 x, SqlInt32 y)
{
if(myObj == null) myObj = new externalAssembly.MyClass();
myObj.SetPoint(x.Value, y.Value);
return SqlBoolean.True; // because we can't have 'void' return type
}
////////////////////////////////////////////////////////////////////////////////////////////////////
In low to moderate concurrency (single hyperthreaded CPU with 20 sessions banging it in a loop), it *usually* does okay. In a higher concurrency situation (2 hyperthreaded cpus with 10 sessions stressing this code and 10 other sessions doing regular TSQL Selects) It runs for a long time, but will occasionally throw an out-of-memory exception. (Previously, I was managing my thread state manually with a locked dictionary, an Int32 key, and CreateSession/ReleaseSession calls). When an out of memory exception is thrown while the dictionary is locked, I get an AppDomain unload, which is *completely* unacceptable)
So, I know that sometimes, I won't be able to allocate my 3MB (it could be 3kb, it just shows up more readily with a larger allocation request). That doesn't mean my externalAssembly is "misbehaving" or "off-in-the-weeds". It just means the server is loaded right now and can't satisfy my request. One may catch an OutOfMemory Exception (perhaps to add additional info about the point of failure), but the thread is already being aborted.
I tried modifying this implementation to use a buffer pool that is allocated on start-up. That worked pretty well (reduced % Time in GC a bit, also), but it forced my external assembly to be marked as unsafe rather than just external access because of the Synchronization methods used to manage the buffer pool. It also doesn't scale, at least not as it sits. Its just a fixed size buffer pool. With more processors and less peripheral loading, the extra processors would just be waiting for a buffer. Besides that, I thought there was some escalation policy about "waiting too long", but I may be wrong.
I would like to eliminate the "UNSAFE" attribute from the primary assembly -- mainly because it "sounds scary", but more realistically, because it is unsafe! Or at least, experience in the field points to synchronization issues being a primary cause of unreliability in systems. Also, calling the C# lock, Mutex, Monitor etc call into native code to use the OS for locking. When this happens, SQL doesn't really know what you're waiting for and can't take that info into account when scheduling. All it knows is that you're waiting on an OS lock. I thought the hosting API would've allowed the host to optionally implement its own locking primitives, especially a host that runs its own scheduler.
I've looked into constrained execution regions and Chris Brumme's blog entry on hosting. Using them would help ensure some protection, but I think even they do not protect a thread from being unloaded in the face of an OutOfMemoryException (or any asynch exception); rather, they allow you to safely clean up unmanaged references and ensure state integrity for the appdomain.
At any rate, this is getting a little long winded. If anyone has any feedback, I'd be delighted to hear it.
Thank you.
-Troy
System Info:
SELECT @@version
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
We have a system(32GB RAM and 2 TB hard disk, Windows7,SQL SERVER 2008R2 enterprise 64 bit). Looks like whenever i run some query(even query result 50 records) on the database, the Memory utilization is very high(30 GB) in task manager. How can i control this over usage? The memory setting is default in server properties(min 0 and max 2147483647).
View 7 Replies View RelatedBacking up all databases on a sql server that hosts secondary high availability databases as well as other databases. The other databases back up fine, but the high availability secondaries all get the same error:
BackupDiskFile::OpenMedia: Backup device 'F:MSSQLBackupdbnamedbname_backup_2015_MM_DD_tttttt_ttttttt.bak' failed to open. Operating system error 2(The system cannot find the file specified.)
My database server memory utilisation is growing faster from past 1 week. it remained same for 1 week around 55% and now it is going to 70% and increasing.
Total OS memory is 32GB and I kept cap for sql server memory upto 29GB. Dont know what to do..
I have been having issues with our SQL server for awhile now. It seems to run out of memory every few days and when I look at the memory dump, the MEMORYCLERK_SQLOPTIMIZER seem to take over memory and eventually cause the server to crash.
Here is the SQL verison we are using: Microsoft SQL Server 2012 (SP1) - 11.0.3460.0 (X64) Jul 22 2014 15:22:00 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)..It is on a VM on Windows 2012 server. It has 20gb of RAM allocated to it and the MAX Server Memory is set to 16.5gb.
I have seen the MEMORYCLERK_SQLOPTIMIZER grow to about 11gb at the time of the server crash. Why that is happening? What is causing the memoryclerk_sqloptimizer to get so high? I have looked it up and it looks like it has to do with ad hoc requests, but is there something I can do to bring that memory down when it gets so high so that I can prevent a server crash?Do we just need to add more memory or is there a memory leak somewhere?
I know I should probably be posting this in the RS section but I have a Win 2008 R2 server and RS 2012 along with SSIS and Database server installed. I also have SQL 2005 instance with Sql 2005 SSIS running on the server.
I saw that Reporting services was consuming 9gb of ram a few days ago with no published reports. It's just a default install.
So I investigated the settings and the document: [URL]......
I added this section to the reportserver.config file to restrict memory usage
<WorkingSetMaximum>512000</WorkingSetMaximum>
<WorkingSetMinimum>128000</WorkingSetMinimum>
Any example altering the reportserver.config file in your standard build of sql server reporting services?
My Sql express will use up to 1G or more memory and never release.
I had the same problem on SQL 2005 standard, I solved by adding /3G in boot.ini and turn on AWE.
but it seems SQL express doesn;t support AWE. so how could I do here?
thanks
We have two server configured in always on mode .
windows is sql server 2012 64 bit edition and sql server is 2012 64 bit edition.
RAM installed on both server is around 65 gb of which 49 gb is max server memory allocated for sql services on both servers.
database related to reporting services are also in always on group .
We have also configured for reporting services and both are running on their respecting server.
Issue is on primary server reporting service is using almost 7 gb while on secondary it is using 10 gb even when there are 5 reports and its used within offices .
what issue and how to check why ssrs is using high memory..
any query , perfmon counters
reports are randomly used at client side
i have checked memory utilization through task manger..
When I deploy the cube which is sitting on my PC (local) the following 4 errors come up:
Error 1 The datasource , 'AdventureWorksDW', contains an ImpersonationMode that that is not supported for processing operations. 0 0
Error 2 Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Adventure Works DW', Name of 'AdventureWorksDW'. 0 0
Error 3 Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Customer', Name of 'Customer' was being processed. 0 0
Error 4 Errors in the OLAP storage engine: An error occurred while the 'Customer Alternate Key' attribute of the 'Customer' dimension from the 'Analysis Services Tutorial' database was being processed. 0 0
How to accurately track how much memory was granted to a DBCC checkdb during it's execution?
I'd like to do this without continuously querying dm_exec_query_memory_grants, if at all possible - but if that's the only option.
Is there any easy way to detect SQL Server memory is getting paged out? Any DMV query or tips to confirm it is paging out.
My SQL Server Version is: Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)
Is it available by default or it has to be Configured.
If yes then how to configure Tempdb in Memory for SQL 2012 SP2 CU1?
Is there has any SQL query to check running out of "min memory per query SQL Server"(default value is 1024KB)?
View 2 Replies View RelatedHow to set maximum server memory by using sql scripts in sql server 2014?
View 5 Replies View RelatedWe have a new set up on VM to run an application running 24*7 (migrated from SQL server 2008R2) with below configs:
1. OS- Windows server 2012 Standard 64 bit hosted on Virtual Machine
2. Memory 16 GB and Cores =4 with 2.4GhZ processor
3. SQL server 2012 SP2 , 64 bit Standard edition.
4. Total size of databases as of now 15 GB with biggest being 5 GB.
How should i go around in setting the MAX and MIN server memory settings. I have this set up for many of SQL 2005 and 2008R2 servers, but for 2012 i heard that things has slightly changed.
How should i start analyzing and setting the right value of this MAX and MIN?
One server, a part of a 2 node always on cluster, had problems, I had to restart.
I'm getting this error for example now:
Error: 49910, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
+
SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.
Now, on the instance I have activated maximum servere memory, my question; Is it possible to adjust this setting without starting the service?
Some parameter for example?
The server itself have enough memory.
If I create a memory table inside a while loop, I expect that every loop the memory table is (newly) created and thus empty.
But when I test this, I see that on the second, third, ... run the inserted data in the memory table is still present and not cleared.
This can be reproduces with the following code:
DECLARE @tbl TABLE
( [id] BIGINT )
DECLARE @tbl2 TABLE
( [value] BIGINT )
INSERT INTO @tbl
VALUES (1), (2)
[Code] ....
/*
Expectation: twice 11 and 12 in @tbl2
Actual result: three times 11 and 12
*/
SELECT *
FROM @tbl2
Is this a wrong assumption or is this a bug in SQL Server? (Tested on: SQL Server 2014 and 2008).
On first Node A: The server has 16 GB of physical RAM.
On second Node B: The server has 10 GB of physical RAM.
Now, this being Active Active, Node A can be clustered on failure onto Node B..Now reporting server is configured under these two nodes, with defined MAX and MIN server memory of 12 as MAX and 0 is min IN GB.Now with this setting on SQL whenever the cluster moves, such config make OS goes low on node for 10 GB.I am only left with option of switching this MAX and MIN to a default setting or is there any other alternative such as script which can change this setting accordingly when cluster moves to respective server.
I am searching for query to find total memory allocated to sqlserver, and how much being used utilized as well cpu utilization percentage .
View 10 Replies View RelatedAfter I have set the maximum sql server memory in sql server 2014 using sp_configure, do I need to reboot whole server or restart sql service?
View 4 Replies View Related1. I need to make use of in memory engine for my pr-existed develop procedures ,tables ,index. do I need and code changes for application and how to store tables /indexes in OLTP memory
Assume table index may have primary key index as well.
2. If table with one primary index and 2 foreign constraints, 3 non clusters indexed. which one able o load to memory area and how t do that.
3. In memory is lock free zone. usually locks will happpen in RDMS context . how this works without locks.
My Integrity job started failing recently with the following error. Msg 701, Level 17, State 123, Line 1 There is insufficient system memory in resource pool 'default' to run this query. Process Exit Code 1. The step failed.
select @@ version
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64)
Jul 9 2014 16:04:25
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
System is having 4GB RAM and SQL is using most of it. It has 2 processors.