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 |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2007-06-22 : 05:44:48
|
| Hi all, We have our SQL Server 2005 installed in a server with below specifications. MS Windows Server 2003 R2 Standard x64 Edition Service Pack 2 Intel(R)Xeon(R)CPU 5160 @ 3.00GHz 3.00 GHz, 8.00 GB of RAMThe issue is , When a query window is opened and a procedure is executed or a DBCC command is run, the PF usage keeps on increasing and reaches the maximum 8.81 GB and remains there forever even if the query is stopped and the session is closed.The CPU usage also shoots up and stays between 90 - 100 %.Now, it is not possible to open a new query or do any operationin the SQL server. Timeout Expired error is thrown.My questions are , 1. Is that good for the SQL server to occupy the total RAM ?2. Is the performance related to the RAM space occupied by SQL server ?3. What should we do in order free the PF usage after the a process is complete ?4. Have anyone faced similar scenario ? Any solutions ?Thanks for your time & any help should be apprecitated.HariArul |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-22 : 06:07:34
|
| What have you set the max memory available to sql server to?Sounds a like you might have allowed it to use all the memory leaving nothing for os (or at least not enough).==========================================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. |
 |
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-06-22 : 06:14:29
|
| It has the default value.The option Maximum Server memory is set to 2147483647 MB. |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-06-22 : 06:30:59
|
| Is there any ideal value that should be set to the Maximum server memory ? Even if the default value is set , will this not come down when the processes are closed ? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-22 : 15:43:45
|
| What's total db size? May need more memory on the server. |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-06-23 : 02:36:40
|
| The database size is nearly 130 GB. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-23 : 22:23:53
|
| Did you check query execution plan? Any blocking on the server? |
|
|
|
|
|
|
SQL Server Page File usage.