| Author |
Topic |
|
bluefire_xl
Starting Member
8 Posts |
 Posted - 2007-09-12 : 04:45:19
|
| We got a server that uses a lot of page file memory and when i tried to check the processes in the task manager i found out that sql server uses a lot of memory and every morning our sql server connection is very slow and always prompts a timeout error. so what i do is start stop the sql server service and the page file usage will automatically drop making the server connection much better but after a while page file usage will still go up. Is there a way to know what causes the massive usage of page file memory? |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-12 : 06:37:09
|
| i think there are some articles by Graz on this topic, check them outNote: sql server is a memory beast, restarting the services doesn't solve anything, in your case atleast--------------------keeping it simple... |
 |
|
|
bluefire_xl
Starting Member
8 Posts |
Posted - 2007-09-12 : 09:23:39
|
| i was really hoping to eliminate the massive page file usage because it really slows up our servers but with the others servers with sql we dont have this kind of problems. both of them have the same number of database. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-12 : 23:31:01
|
| How much memory does the server have? What's total db size? Is it dedicated sql server? What's sql edition? |
|
|
|
bluefire_xl
Starting Member
8 Posts |
Posted - 2007-09-13 : 08:02:04
|
| is there a way to know the total db size in a query or something. it has 2gb of memory. what do you mean by a dedicated sql server? the edition is sql server 2000. |
|
|
|
suresha_b
Yak Posting Veteran
82 Posts |
Posted - 2007-09-13 : 08:34:21
|
| sp_helpdb shows db size. Dedicated means server is used for running only SQL Server. Not to run other services such as IIS, DNS, DHCP, Domain Controller, etc. |
|
|
|
bluefire_xl
Starting Member
8 Posts |
Posted - 2007-09-13 : 09:13:48
|
| is there a way to sum all the db size. but i have use excel to sum it up. total db size is 29774.9 MB. yes it is a dedicated server.everytime its page file usage goes up to almost 4GB i start stop the service just to make the server connections fast again. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-13 : 23:40:46
|
| Did you check cpu time and disk i/o? What kind of disk array do you use? |
|
|
|
bluefire_xl
Starting Member
8 Posts |
Posted - 2007-09-14 : 00:39:58
|
| im sorry but im really a newbie on this how do you check the cpu time and disk i/o? not sure with the disk array but im think its on raid 1. am i correct on the disk array that you are asking? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-15 : 00:36:18
|
| Check cpu time and so in perfmon, and check disk in array manage tool. Work with your win admin and dba. |
|
|
|
bluefire_xl
Starting Member
8 Posts |
Posted - 2007-09-15 : 20:26:32
|
| i am the database admin well a newbie one. so what do i have to check with the cpu time and check disk? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-15 : 20:45:34
|
| Perfmon. You need to work with your win admin then. |
|
|
|
bluefire_xl
Starting Member
8 Posts |
Posted - 2007-09-16 : 01:56:29
|
| is it possible if we can determine if an application is the cause of my problems? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-16 : 17:52:46
|
| You have to figure it out with tools like perfmon and profiler. |
|
|
|
bluefire_xl
Starting Member
8 Posts |
Posted - 2007-09-16 : 22:35:52
|
| can it be automated so i can just view and compare the historical files of both monitoring tools. if yes how? thanks |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-16 : 23:59:29
|
| Yes, you can set perfmon to start and stop in windows. Ask your win admin. You can also run sql trace with system sps, check books online for details. |
|
|
|
|
Slow sql server connection problem