August 23, 2004 at 3:58 pm
I have a SQL 2000 SP3 Enterprise Edition with 8 GB of RAM with AWE and PAE enabled. This server hosts a data warehouse that is updated monthly in an 8 hour process. Other than the monthly update, it only has a handful of people running reports against it. The maximum SQL Memory is configured at 6144 MB.
The department owning the server has another server that can support up to 4 GB of RAM. They would like to use the second server for development/testing. They have asked me if I think that they can get by with the 4 GB of RAM or should purchase a new server with 8 GB.
My plan is to collect performance data for a few days, including during the monthly process on the production server. That is where my confusion begins. If I look at "SQLServer:Memory Manger\Total Server Memory" and "SQLServer:Memory Manager\Target Server Memory" it looks like the server is maxed out even when there is nothing going on. Both are at 6310096 KB.
I realize that having AWE enabled complicates things, but is there any way to tell what SQL is really using?
Thanks,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
August 24, 2004 at 7:11 am
Try monitoring page life expectancy under buffer manager. This will tell you how many seconds your data is staying memory with references. A general rule of thumb I read somewhere indicated that anything over 180 seconds was good. If you have a high number, adding more memory probably won't help that much, but it all depends on your senario. There are a lot of other variables that you need to look at such as number of processors, size of database, disk configuration, database design, index selection, etc. The extra memory will probably be your least concern since you already have 4 gigs and have the /3gb switch set.
August 24, 2004 at 7:16 am
Opps, I meant "This will tell you how many seconds your data is staying memory withOUT references". My typing is slower than my thoughts this morning.
August 24, 2004 at 7:37 am
Thanks for the tip! I just found this article, http://www.winnetmag.com/Windows/Article/ArticleID/43117/43117.html, on the subject thanks to your suggestion.
I think they are going to try to match processors and drives on the dev/test system. And the database will actually be a copy of the production database, so in this situation, so the memory is the biggest question.
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply