Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
yalgaar
Starting Member
43 Posts |
 Posted - 2006-04-10 : 11:28:27
|
| I have a custom .net application that uses SQL 2000 server. All users are compaining performance issues and white-outs while they are using the application. I am almost certain that it's the SQL server that is the curprit. All the other components involved in the application hardly has any CPU or memory usage when I check the performance in the task manager.On SQL server, I see that the process sqlserver.exe is taking like 2.8GB of memory. Is there a way to find out which exact SQL query or process is taking so much of memory? I belive there may be a bad SQL process that is stuck and taking all the memory? Is there a way to find out?Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-10 : 11:31:24
|
| ry running thishttp://www.nigelrivett.net/SQLAdmin/sp_nrInfo.htmlIt'll show you what connections are doing and their disk io.You shouldn't have anything else running on the same box as sql server or you are asking for trouble. If you do you should limit the resources available.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-11 : 00:51:24
|
| "I see that the process sqlserver.exe is taking like 2.8GB of memory"In general SQL Server will help itself to all free / available memory. It doesn't necessarily mean that it needs it at any moment in time!How big is your database (both the overall file size, and an indication of the number of tables and the number of rows in the biggest tables)?How many users are connecting (max. concurrent users)?What is the specification of the server (number/speed of CPUs, RAM, configuration/number of disk drives)?Kristen |
|
|
|
Jhalmans
Microsoft SQL Server Product Team
12 Posts |
Posted - 2006-04-15 : 02:04:37
|
| SQL Server 2000 will reserve virtual address space for the maximum possible size of the buffer pool and then grow to that size over time. The growth is driven by user requests, as users request more data sql server commits more memory up to the Target server memory value. That's not to say that it is oblivious to external memory pressure. SQL Server listens for system memory notifications and will decommit memory allocations when the system is under pressure to avoid paging. Of course if some other application is requesting memory faster than SQL Server can decommit you might have issues.To prevent this situation simply set the max server memory to a value that will leave enough room for your other applications.-JeromeThis posting is provided "AS IS" with no warranties, and confers no rights |
|
|
|
|
|
|
What is taking up so much memory?