| Author |
Topic |
|
mvanwyk
Yak Posting Veteran
99 Posts |
 Posted - 2006-11-27 : 13:49:41
|
| Hi there group.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.Please assist. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-27 : 13:52:51
|
| That sounds perfectly normal - it's not the stored procs using the memory its the cached data (called the buffer cache). You can adjust the server properties to force it to use less, but it will affect performance. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
mvanwyk
Yak Posting Veteran
99 Posts |
Posted - 2006-11-27 : 15:39:08
|
| Thanks for the response.I'm also running IIS and serving ASP pages.A total of 3 websites using 2 databases.Database 1 =28GBDatabase 2 =16GBThese website run queries which fail with a memory exception error occurred |
|
|
|
mvanwyk
Yak Posting Veteran
99 Posts |
Posted - 2006-11-27 : 15:43:23
|
| System.OutOfMemoryException was thrown |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-27 : 15:43:29
|
| You should definitely run the web sites on a separate server - and then also check the queries to make sure they aren't returning a lot of unnecessary data - remove columns if possible, make WHERE clauses return fewer rows. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-11-27 : 15:43:32
|
quote:
Originally posted by mvanwyk
Thanks for the response.I'm also running IIS and serving ASP pages.A total of 3 websites using 2 databases.Database 1 =28GBDatabase 2 =16GBThese website run queries which fail with a memory exception error occurred
Well there's your problem...you really should have another boxBUT, you can configure sql server to limit how much memory it can use...which is not a good thing, but if you have to, you have toI would have an application server personallyBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-27 : 15:43:56
|
| If you think the memory is faulty, AND you can take the server off line for AT LEAST 48 hours, then I recommend this utility:www.memtest86.comKristen |
|
|
|
mvanwyk
Yak Posting Veteran
99 Posts |
Posted - 2006-11-27 : 15:45:33
|
| Thanks More on the monitoring is profiler the monitoring tool that one can use or are there others. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-27 : 15:48:24
|
| "I would have an application server personally"Me too ..... actually, I do in fact! |
|
|
|
mvanwyk
Yak Posting Veteran
99 Posts |
Posted - 2006-11-27 : 15:52:07
|
| Thanks again Guys you have help a lot.I guess its back to the drawing board.This probably will teach me to adopt "best practice standards".Does any one know where I can find a guide line for "best practice standards" concerning databases? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-27 : 16:02:35
|
Heheheh ... you're not fussy which "best practice standards" you adopt then?  I don't think there is anything particularly appropriate here, but reading around some stuff may lead you to more appropriate material:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210Kristen |
|
|
|
lamkf
Starting Member
1 Post |
Posted - 2006-12-19 : 16:36:15
|
| It's probably just a matter of SQL Server wants to hold on to the memory if you allowed it to. The following is a good link that describe the memory usage/settings for SQL Server.http://www.zaptips.com/SQLServer/SQLServerMemoryConfiguration/tabid/63/Default.aspx |
|
|
|
|
Excessive memory usage