I'm hoping that some might have an answer for me after much net searching.
I have a server (200GB disk space, Dual 3.8 GHZ processors, 4GB memory) that hosts 6, very small, SharePoint sites (WSS 2.0) and SQL 2005 express handling 1 config DB and 7 content DB's.
The biggest DB at the moment is only 300 MB and the sites are not actively being used yet, they are only open to a select number of users (+ - 25 in total) who are using them as reference "areas" at the moment. Each site is running in it's own application pool as well.
I find that the sqlserv.exe process increases in memory usage and does not seem to decrease. It gets to the point of 960 MB usage and then databases cannot be used (SQL 2005 Express max memory is 1GB). SQL seems to "close" them down and site errors being received are "site is not in configuration database". When the SQL service is restarted the memory usage idles around 70 - 80 MB, the sites are 100% again but after an hour or 2 the memory usage is sitting at 560 MB again and doesn't seem to decrease.
Is there a way I can bring this memory usage down?
With SQL Server 2005, I have the problem that sqlserv.exe keeps using more and more memory. Now I did some research and found the following anwers in different places:
*It seems to be the way that SQL Server works. It just uses all the memory which it can get. I have two remarks about this: 1) I that is true, then why is a fresh installation of SQL Server not consuming all the memory it can get? 2) After a few weeks, it consumes so much memory that the whole system (even SQL Server itself) start running very slow. I don't think that's the intended behavior?
So I thought about restricting the maximum memory that SQL Server uses, but people keep saying that's not the way it should be done: SQL Server should be allowed to allocate memory dynamically.
Can anyone help me with my questions?
One other thing that may be of relevance: This instance is a disitributor for replication and the distribution.MDF file also seems to be increasing over time. It became much larger than the application database.
Would anyone have any suggestions/advice on how to determine what is causing the memory usage for sqlserver.exe to increase a dramatic pace in windows task manager? What would be a good resolution to slow down this memory usage? Thanks!
Hi all, Our Production server has 4GB RAM and is running SQL Server 7.0. By default since SQL Server 7.0 Standard Edition can take up only less than 2GB, our SQL Server is now using only 1.8GB (leaving the rest for the OS, Windows 2000 Server).
Inorder for SQL Server to take advantage of more than 2GB of RAM it is suggested that boot.ini be modified to include the switch, /3GB
Has anyone seen any issues with doing this? Is it safe to do so on the Standard Edition of SQL Server 7.0?
OVERVIEW I run a MS SQL DB and have 7GB of RAM allocated to it to use. My DB size is around 30GB. I have about 30-40 users at any given time accessing this DB, and on any given moment we may have 2-3 queries being processed at the exact same time, but not really a high rate of usage.
The SQL is running on a 2 - Dual Core Xeon 2.8Ghz processor server in RAID 1+0. The average CPU usage is around 8-15% at most times, spikes to 60-70% sometimes then drops back down.
QUESTION Why or what would cause SQL to show in my Task Manager as using 7GB of RAM constantly? When I restart SQL, system RAM drops to 1.7GB and holds. As traffic increases, as queries begin to be processed in SQL the RAM rises till it reaches the 7GB limit I set. Before it reaches this limit, the ASPX pages run smooth as silk. Once it reaches this 7GB limit they begin to crawl. A process that would take 2-6 seconds, now takes 30-60 seconds.
I know a couple years back I ran into this and it was because processes were not being closed and SQL was holding them in RAM, but I have been assured this is not the case now. I was told that because my DB is 30GB total (MANY tables, most are hardly ever accessed, and the bulk of the data is hardly accessed) that it is common for SQL to use and hold on to 7GB of data - is this true?
What would cause SQL to hold 7GB and slow way down? IS there a KB article that could help me understand this?
I have a three-tier app written in C#, which takes information from a third party source (typically an array of double precision floats) and commits it to a SQL 2005 Db.
The Server then notifies the client that the information is available and the client queries the Db (through the server) in order to display it in "real-time". It all seems to work fine except that the memory usage by SQL 2005 just keeps rising. I have run a memory profiler on the server and client apps and they do not have a leak.
The test I am running has all three apps on the same machine and is reading in about 250k of data a second. It typically runs for just under two days on a machine with 2Gb RAM before falling over with System.OutOfMemoryException.
I have tried setting max memory usage but it seems to make no difference... anyone seen anything similar or know what my problem might be?
We recently did an in-place upgrade of our cluster from SQL 2000 Enterprise to 2005 Enterprise. We are seeing memory utilization on the server that is not expected and wanted to get an idea from others if this is normal. Here is our setup:
32-bit Windows Server 2003 SP1 SQL 2005 SP2, 2-node cluster 16 GB memory on each node /3GB /PAE switches in the boot.ini file AWE Enabled, min memory=0, max memory=14000
The strange bit is that in the Task Manager the SQL service is showing as only using 205 MB of memory and the pagefile usage is at 13 GB. This is troublesome since it looks like SQL is using virtual memory instead of physical memory. We recently upgraded the memory from 4 GB to 16 GB, the sql cluster service account was added to the Lock Pages in Memory policy, the boot.ini switches were set and the AWE enabled/max_server_memory script was run before upgrading to 2005. Can anyone confirm either 1) this is normal or 2) how to correct the memory usage?
I have also questioned using the /3GB switch; would it be better to remove that and set the max memory for SQL to 12000?
Any help would be greatly appreciated. If I left off any information that would be helpful, please let me know.
We have an application that we currently run on SQL Server 2000 that works by creating a DTS package that it then executes.
Due to performance reasons, we have been considering switching to 2005, for a few reasons. Can anyone confirm clarify the following?
1) SQL Server 2000 caps RAM usage at 2GB, whereas SQL Server 2005 is only limited by the OS - RAM usage is a big current issue for us, so if upgrading to 2005 would solve this it would help a lot. Can anyone confirm my understanding of this? 2) Would using the legacy DTS in SQL Server 2005 take advantage of this RAM difference, or is it running on the old 2000 engine and only able to use the 2GB?
we are working on a release 2.0 mobile solution right now. In our version 1.0 we did not have to worry about memory issues as our application was the only application running on our target devices (e.g. T-Mobile MDA Compact II Pocket PCs, WM2005). Now we need to share the available memory with others. As our application relies on its SQL Server 2005 Mobile Edition database we are wondering about memory usage of that server.
We know that a Pocket PC divides its memory into Storage and Program. If our application uses a 5 MB database and 1.5 MB for DLLs and it's exe-file. These files reside in the storage space when not loaded. When the application starts up it is loaded in the program memory. What happens to the 5 MB database file? Is is loaded into Program memory as well? Are only portions of that file loaded? Or is nothing loaded at all?
Does anyone have a deeper insight into that server an can answer my questions.
today I've put in production a big database accessed by 200 concurrent users, this database has READ_COMMITTED_SNAPHOT set to ON.I know that RCSI set to ON is very aggressive on tempDB so I'm monitoring it.I've noticed that the Transaction log space usage (%) on TempDB is slowly but ever increasing, I mean in the last 24 hours I've started from a 99% space free, now we are 37% space free...is it normal? TempDB log is 35GB in size.
I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .
I've received conflicting information from Microsoft personnel so thought I'd see what some thoughts here are.
In summary, we upgraded a server from SQL Server 2000 SP4 Standard to SQL Server 2005 SP2 Standard. This servers main purpose is to handle alot of merge replication to anonymous pull subscribers. We have some Transactional replication also occurring. There are 8GB memory on the server.
During the upgrade we ran into memory pressure on MemToLeave. We put the /3GB parameter in boot.ini and -g512 on the startup per Microsoft's suggestions. This got us past the upgrade process.
After the upgrade, we took off the boot.ini setting and the -g512. We enabled AWE and assigned 6GB to SQL Server. Then once in a while when the merge snapshots were running, we'd receive some "system out of memory" errors. I went ahead and put -g512 back on and haven't received the error since.
My question to Microsoft then was if we go to say 16GB of memory on the box and give say 14GB to SQL Server, would it be beneficial to set the -g option to a higher number. That's when I got into a discussion with the Microsoft person that SQL Server 2005 Standard would not use anything above 4GB, which is opposite what the Microsoft site says, others have said, and opposite to what I'm seeing for memory usage with DBCC MEMORYSTATUS showing the 6GB being used We'll be talking to our TAM about our suppport, specifically on Replication topics, as we've had some problems getting knowledgable support on this topic. If anyone knows of support outside of Microsoft on Replication topics, I'd love to hear about it.
Any thoughts on the tweaking of memory related to our environment? I know it may be site-specific and we may have to do some trial and error, but with:
1. Doing heavy merge replication processing on the server(1,500 subscribers). 2. say we get 16GB on the box(server is Windows 2003 SP2 Enterprise)
are there some suggestions on a -g setting to best utilize Buffer Pool and MemToLeave ? Some other things to do? Is there some process/method to help determine how best to define the memory settings? If there a way to see how much BPOOL and/or MemToLeave the system is using at a given moment? DBCC MEMORYSTATUS gives alot of info, but I'll be the first to admit that I don't know what alot of the info there is really telling me. If there some white paper, etc that would help determine what the system is doing memory-related, that'd be great to know.
Out techs informed me that they are getting reports of a system slow down. When they look, they find sqlserver.exe has lots of memory allocated to it. They reboot the server and then it runs okay for a few weeks. They tell me this just started happening recently.
SQLServer itself has not been touched in months. They are, however, starting to use one of the databases heavier.
I found a setting where you can set max_server_memory. Any problems if I set this to a value?
I have a Windows sever 2012 with sql server 2012 enterprise. Ram size is 22GB. Sometimes SQL sever takes 95% memory.My question, How to reduce memory size without killing any process because it's production server.So there are many background process is running. And,Is there any guides to learn why Memory is raise d so high and how to reduce it.
I'm having a problem with memory and cpu increasing after I enable the broker on my database. Before I enable the broker, my memory will be at 70 mb, but after I enable broker, memory increases to 100, 200, and keeps rising even until 700 mb; plus the sql server process uses more of the CPU, ranging between 30-40 percent.
Any ideas what could be going on, and possible solutions? Thanks
I have 1 problem here. I developed a desktop application and I am using SQL 2005 Express as my application database.
I found that after I closed my application, the SQL 2005 Express memory resources do not release. How do I release the memory resources except of restart the SQL services?
Do you guy have any better recommendation of other database is more suitable for desktop application?
SQL Express Ed is limited to one CPU, what happens if the CPU is a dual core CPU? Does SQL Express care? Does it see a dual core as a single CPU?
As for memory, stupid question, SQL Express is limited to 1 GB RAM, if I run it on a server with say 4GB of ram packed into it, will it affect SQL Express' performance? Will SQL Express even run if there is more than 1GB available? Or will SQL Express simply ignore the extra RAM with no negative consequences?
Good day to all, I'm new here, so I don't know if this is the right forum to post my problem. I have a web application written using C# .net 2005 (W/ajax). The application has a module that uploads data from excel file to the sql server 2005 database. w/c is by the way, i'm using SQL 2005 Express Edition, the app can upload up to more than 10,000 records from an excel file. Everything is ok until it was deployed in a test environment, while having a run through with the system, the application encounter an error after which, we cannot log in to the system anymore. I restarted the server (web and sql server in 1 machine running winxp) then I can log-in again in the system. When I'm tracing where the problem came from, I noticed that the memory usage of sqlservr.exe increases everytime the app connects to the server. I already fix some code to close some objects that might have caused the high memory usage, then I run sp_who in the management studio and there are still connections used by the app AWAITING COMMAND. Then I manually kill (using kill spid) connection that are left opened by the application. But the mem usage of sqlservr did no decrease. Is there a way to release the memory usage of sqlservr.exe? In ASP.Net ? I have a hint that this has been causing the error. Thanks a lot.
Hi, it seems that every day SQL Server 2000 has some kind of memory leak, the memory usage creeps above 150000 approximately 3 times per day. Is this normal? It starts at about 13000.
Is there any way that I can monitor what is causing the memory usage to be so high and maybe rectify it?
I was on my friend's server earlier and I checked his system stats. Between sqlservr.exe and msftesql.exe he was using 2.9gb of ram. This is not a high traffic server so I felt that was just a wee bit high. What are some usage experiences you have seen? I have not worked with 2005 so I don't know what the usage should be like for a low to medium usage server. The guy that admins the db is kinda a moron anyway. He said he has said to the server admin it has been optimized, but I dont believe it one bit at 2.9gb of usage. Any comments would help.
I need to know which of the following two methods do need less RAM.
There are 2 big tables, each about 9 M rows, and 6 small dimension tables with each about 10 to 100 Rows. The dimension tables are joined by their id's with one of the big table.
The Structure of a dimension Table looks like
CarID (tinyint), Description (varchar(20)) 1 BMW 2 Porsche
I want to join the 2 Big Tables in a materialized view. Later i will run queries like select * into #temp from dbo.vw_materialized_view where Car = 'BMW'
So, back to my question, will such a query take less memory (ram) when i joined all 8 tables before I created the mat. view or will it take less when I only join the 2 big tables in a mat.view and later join the mat.view with the 6 dimension tables?
Can some one tell me where can I get the CPU and Memory Usage of my server on the Sql Tables or a script or store proc that i could run to get the above. I need that and to store its results in history to show how are our servers perfoming
I have setup 2 x64 SQL boxes now that are having problems with memory. Both boxes were pretty much setup with the defaults on windows 2003 R2 64 bit server, only changes were file locations etc.
On both servers the memory usage continues to climb over the course of a few days until all physical and virtual memory is used and the server comes to it's knees. A reboot will stabilize it for a few days while the memory usage ramps up again.
The sqlservr.exe process does not report much memory but the commit charge in the performance tab of task manager shows the maxed out value. I thought the sqlservr.exe process only didn't report the correct memory figure when AWE is enabled which it isn't.
I thought as a default, SQL server would dynamically allocate memory as needed and had internal mechanisms that would not use more than the physical ram available. The one server has 16GB and I even set the max SQL memory usage to 12GB but still my total memory usage would get out of hand. SQL Server is the only application on these boxes.
Am I making some incorrect assumptions and need to change the way SQL is setup? Any suggestions?
I'm trying to get the machine free ram value using either T-SQL orSQL-DMO.I found .Registry.PhysicalMemory which takes care of the total ram butI still need eithrt the free or the used.Any ideas?Thanks!
We have some CLR sprocs and tvf's we run in a batch job that recently have been getting the out of memory issue. I want to increase the amount of memory allocated to the CLR using the -g startup switch but i want to make an intelligent decision on how much to allocate. What are some of the best ways that you have found to estimate how much to give the CLR?
How much available memory is best practice for SQL servers? We have some alerts set up by our system admin, they go off if the available memory goes below 500mb. Which is fine for other servers but I feel for SQL server it isn't quite correct. Currently we stand below at about 475mb and the PF usage is around 7gb.
It is SQL 2005 standard SP1 x64 dual intel xeon 5160s 8gb physical memory buffer hit cache is > 99%
I am just not confident enough to make a solid decision here that this is acceptable. In the beginning of this month I ran some counters for 24hrs to get a feel for the baseline. The average for available memory was about 850mb at that time. So I am wondering if it is going to keep declining and turn into a problem. The server/instance has not been restarted since maybe last November.
Does anybody know why BCP on v6.5 grabs so much memory for SQL Server? I have a few table imports where the BCP process will consume over 460MB of RAM during the imports.
The BCP cmd file is executed via an xp_cmdshell call. The server has 2+GB of RAM, but the BCP process effectively flushes large amounts of data from the buffer. It takes quite along time for the cache to recover from this, and after this, the rest of the nightly processes run much slower, as they end up having to hit the drives to retrieve information that should already be in cache.
If anyone can shed some light on this it would be much appreciated.
