I/O Congestion Troubleshooting: Memory And Swap File Usage Question
Jul 20, 2005
We are hosting a 140 GB database on SQL Server Version 7 and Windows
2000 Advanced Server on an 8-cpu box connected to a 15K rpm RAID 5
SAN, with 4 GB of RAM (only 2 GB of which seem to be visible to the
OS) and a 4 GB swap file. (The PeopleSoft CIS application will not
permit us to upgrade to SQL 2K.) We recently upgraded the server from
4 to 8 cpus and the SAN disks from 10K to 15K drives. But we still
have heavy SAN disk usage, sometimes at 100%, and read queues often
averaging 4 and peaking at 12.
The CPUs are loaded at only 20-50%. (The politics are such that it is
easier to throw hardware at the problems.)
We are looking into archiving, converting from RAID 5 to RAID 10, and
at splitting the mdf file into several file groups in an attempt to
get more disk heads into play. (We are also looking at rewriting the
application to reduce the read volume and frequency.) Does anyone have
any other ideas?
Incidentally, does swapfile get used when the physical memory equals
the OS maximum? If the OS can only see 2 GB and we have 2 GB (actually
4 GB) of memory, is the 4GB local swap file on the C drive unused?
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 .
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 12 buffers were considered and 12 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
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.
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.
One of the production box running only sql server application, is showing 80% memory usage on the task manager-memory usage history right now.
We are running sql server 2000 standard version-sp3 with 2GB memory on this box. Server is not on the scheduled reboot at this point.
We have seen this behavior for this box last month that after task manager showing 90% memory usage contantly for several days, when server was manually rebooted, memory usage dropped to 35%. Now it's back to 80%.
Our DBA thinks that server should be rebooted on a regular schedule regardless of memory problem. Our network admin doesn't seem to agree with this. He is not ready to reboot the machine even with this high memory usage.
There is no noticable difference performancewise yet.
My questions are: Is it bad that memory usage reaches from 35% constant to 80-90% or is it common? Should sql server be rebooted immediately to take care of it? Should sql server 2000 rebooted on regular basis regardless of any problems? Shouldn't sql server be releasing memory back to the OS even without rebooting? How do I find out whether server actually is going through memory problems and what is causing it?
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
Currently I have an application that uses SQL 2000. The SQL server service tends to take up as much of the physical memory as possible. The problem is I also have other services relating to this application running that are very important.
What tends to happen after a period of time is SQL takes up all of the physical memory, so that the other services are using the paging file (virtual memory). This causes extremely slow response time over the network as these other services are having to parse the paging file.
Upgrading the memory is currently not an option :(
I know there is an option to set memory usage for SQL but I am unsure how this would respond in a production environment. What would happen if SQL would require more memory than what was allocated to it?
Can SQL release the memory and still act as normal?
I am using SQL Server 7.0 on a Windows NT machine. We have been having problems with SQL Server not releasing the memory after it has utilized it.
Currently it is configured to allow a max of 511MB memory (1024 is total on the machine). I had some advice that my best solution would be to reduce the Max Memory to a lower value (say 400 MB) to help reduce the problem.
Is this not counter-intuitive? Or is this the correct solution?
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.
Could some please point me in the right direction?
We have a database and it's about 28GB in size, recently the SQL server process that runs uses approximately 1.6GB of Memory.
I have tried running SQL profiler to find out which Stored Procedure is causing this but came up unsuccessful. When restarting SQL the process it run's at about 50MB for about 20sec and then starts climbing up to 1.6GB of memory usage.
I am using a tool to monitor SQL Server and Windows. It is warning me that:
Process 1004:services has a virtual address space of 1,846.20 MB. This is close to the Windows two gigabyte address space limit.
When locate the process 1004, it shows 15 threads that Elapsed time for all of them is 1d, 3hrs. The Thread state is Waiting and the Thread Wait Reason is "Waiting for an Execution Delay to be resolved".
I think that 1d, 3hrs is from the time I rebooted my server.
I front a series problem with my SQL Server. I Use SQL Server 2000 standard on Windows 2000 Server machine. The problem: after certain actions such as DTS (that takes table contents and insert to a a file) the Memory usage of SQLservr.exe is grown significantly and after it finshes with success it doesn't free the used memory for the action - in this case the DTS. maybe this explains why I have Memeory Usage of 1700MB of Sqlservr.exe on windows task manager.
Is it a bug? why SQL doesn't free the memory after finishing the action. Any Solutions ? Please send me a short feedback.
Hello all,How can I tell how much memory SQL Server is using on a server. OnWindows 2000, whenever I go to Task manager/processes/memory usage SQLServer seems to be showing 1,744,124K. On all of my servers withvarious size, usage of databases, all of them show SQL Server to beusing about the same amount of memory. Can someone explain this to me?Shouldn't it use more for larger databases, heavy hitting databases?Also, I normally check Dynamically configure SQL Server memory and putthe maximum threshold to a little bit less than the max on the server.The minimum query memory is set to 1024. Is that 1024 a subset of thememory used by SQL Server, or is this additional that can be used?Thanks,Raziq.*** Sent via Developersdex http://www.developersdex.com ***
Hello All,On all of my SQL servers (2000 with SP3), when I go to task manager andlook at memory usage, sqlservr.exe is always at 1.7gigs. If I reducethe maximum to let's say 1 gig it will go down to 1 gig. But if it iseven at 2gigs or 3 gigs it will be showing 1.7 gigs. Why? Is is ok toreduce the memory usage of sqlservr.exe?Raziq.*** Sent via Developersdex http://www.developersdex.com ***
I am using SQL 8 Personal edition with sp2 applied. I set the maxserver memory to 32MB and leave the min server memory at 0. When myapplication starts hitting the database hard the memory usage reportedthrough task manager peaks between 41-42MB. I've stopped and restartedthe MSSQLserver service and checked that the running values are what Iset them to be. Does anybody have any ideas as to why the sqlservr.exewould be utilizing more memory than the configured value?This message was posted some years ago but nobody answered. Now I havethe same problem! Does anybody have an explanation?Thanks a lot
Hi,does someone know how much memory a user session takes on a SQLServerdatabase server? How many concurrent user sessions can a normal server(4 GB primary memory) handle, before connection pooling is needed?/Fredrik
I'm wondering about the memory usage of SQL Server Ce and the Compact Framework.
I've heard I can use only 32 MB per process. SQL Server Ce runs in process and my database file is about 60 MB big.
1. Does that means, I can only use for my application: 32 MB - CLR - SQL-Server Runtime - parts of my database file hold in memory
2. Is SQL Server Ce swapping parts of my database file into my process memory? If so, is there a chance to group some tables, that they are swapped together to increase performance?
3. Means big databases big memory usage in my process memory?
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!
Why does my Sqlservr.exe file take up 200 megs of my nt memory. When i control alt delete and go into task manager, sqlservr.exe is always at the top of the memory usage list.
