February 16, 2010 at 1:53 am
Hi,
I'm working in sql server 2005 SP3.
my server has 2 GB RAM and DB size is 200 GB HDD is 465GB.
when testing is going on, the entire memory gets occupied and the queries are getting delayed.
how can i come out of this.
any suggestions, links are welcome
thanks
regards
ami
February 16, 2010 at 3:02 am
fire Sp_updatestats on the database (considering this is test server)
then use database
Try to find out the queries which are taking more IOs using profiler
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
February 16, 2010 at 5:17 am
Anamika (2/16/2010)
Hi,I'm working in sql server 2005 SP3.
my server has 2 GB RAM and DB size is 200 GB HDD is 465GB.
when testing is going on, the entire memory gets occupied and the queries are getting delayed.
how can i come out of this.
any suggestions, links are welcome
thanks
regards
ami
Run a server side trace instead of profiler and capture the query's. Also see the Execution Plan.
You already said its testing server
Did you run any maintenance plan?
First collect the fragmentation details for all the objects and do the rebuild.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 16, 2010 at 5:19 am
2 GB RAM is not enough for a server with 200 GB database. You need to add more RAM.
February 17, 2010 at 2:20 am
Hi All,
Thanks a lot for your inputs.
I'm working on those.
and i have installed performance dashboard reports to find out the time taking queries.
Once i find some significant result, will get back to you.
untill then, any suggestion / solution / help is highly appreciated.
Suresh, i need to provide or justify why i want increase the RAM.
that is what i'm trying to achieve. is there any algorithm / strategy to fix the RAM size?
Regards,
ami
February 17, 2010 at 9:11 am
Anamika (2/17/2010)
Hi All,Thanks a lot for your inputs.
I'm working on those.
and i have installed performance dashboard reports to find out the time taking queries.
Once i find some significant result, will get back to you.
untill then, any suggestion / solution / help is highly appreciated.
Suresh, i need to provide or justify why i want increase the RAM.
that is what i'm trying to achieve. is there any algorithm / strategy to fix the RAM size?
Regards,
ami
You need to check your paging counter in perfmon, high paging counters can indicate memory pressure. You should also check the PLE (Page Life Expectancy). A low value here will also indicate memory pressure. How much of the 2gb do you have allocated to SQL? and how much is it currently using?
February 17, 2010 at 9:16 pm
Hi,
in the performance monitor I've selected Paging file %Usage and %Usage Peak.
how to check page life expectancy?
My maximum Server Memory shows 2147483647 - nothing but 2GB correct?
how to allocate memory for sql server.
Thanks a lot for your help,
Regards,
ami
February 17, 2010 at 10:53 pm
Anamika (2/17/2010)
Hi All,Thanks a lot for your inputs.
I'm working on those.
and i have installed performance dashboard reports to find out the time taking queries.
Once i find some significant result, will get back to you.
untill then, any suggestion / solution / help is highly appreciated.
Suresh, i need to provide or justify why i want increase the RAM.
that is what i'm trying to achieve. is there any algorithm / strategy to fix the RAM size?
Regards,
ami
I do not know any formula/method to estimate the RAM requirement.
I hope other experts will pitch in and help in this matter.
Regards,
Suresh
February 17, 2010 at 11:04 pm
Hi Ami,
You can specify the max and min memory sql server should use in server properties--> memory.
February 17, 2010 at 11:17 pm
Hi
Suresh: put it in other words, i would like to know what are the methods through which we can determine the RAM capacity for a server.
any links, white papers are highly appreciated.
Ratheesh:
the minimum is 0 and the maximum is 2147483647
in the performance monitor I've selected Paging file %Usage and %Usage Peak.
how to check page life expectancy?
My maximum Server Memory shows 2147483647 - nothing but 2GB correct?
how to allocate memory for sql server.
Thanks a lot for your help,
Regards,
ami
hope the maximum memory available in the server is allocated to sql server.
my problem is while testing the full memory is being utilized and i'm facing performance issues like delay in query result etc..
and i want to give strong reason to increase the memory. mean while i started taking performance reports. and trying to fix the delaed queries. next let me do Sp_updatestats on the database. then probably to fragmentation.
if nothing is working.........no way finally management has to increase the RAM 🙂
thanks
regards
ami
February 17, 2010 at 11:32 pm
February 17, 2010 at 11:36 pm
Anamika (2/17/2010)
Hi All,Thanks a lot for your inputs.
I'm working on those.
and i have installed performance dashboard reports to find out the time taking queries.
Once i find some significant result, will get back to you.
untill then, any suggestion / solution / help is highly appreciated.
Suresh, i need to provide or justify why i want increase the RAM.
that is what i'm trying to achieve. is there any algorithm / strategy to fix the RAM size?
Regards,
ami
What is the edition are you using ?
What is your machine configuration ?
Hi Ami,
You can specify the max and min memory sql server should use in server properties--> memory.
How can you said without knowing the above ?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 17, 2010 at 11:41 pm
You need lots more RAM and probably much faster IO. Do you really have just one disk for this??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 17, 2010 at 11:42 pm
Anamika (2/17/2010)
HiSuresh: put it in other words, i would like to know what are the methods through which we can determine the RAM capacity for a server.
any links, white papers are highly appreciated.
Ratheesh:
the minimum is 0 and the maximum is 2147483647
in the performance monitor I've selected Paging file %Usage and %Usage Peak.
how to check page life expectancy?
My maximum Server Memory shows 2147483647 - nothing but 2GB correct?
how to allocate memory for sql server.
Thanks a lot for your help,
Regards,
ami
hope the maximum memory available in the server is allocated to sql server.
my problem is while testing the full memory is being utilized and i'm facing performance issues like delay in query result etc..
and i want to give strong reason to increase the memory. mean while i started taking performance reports. and trying to fix the delaed queries. next let me do Sp_updatestats on the database. then probably to fragmentation.
if nothing is working.........no way finally management has to increase the RAM 🙂
thanks
regards
ami
Read the Index defragmentation article added in my signature.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 17, 2010 at 11:43 pm
Hii
Do not change any configuration settings without having the root cause why RAM is getting utilized. ByDefault sql server uses available RAM.
2ndly run the perfmon in peak hrs with the foll counters(attached, Change the extention to .htm and import this file in perfmon)save the o/p in .csv file , post the output file if u can.
this perfmon o/p can tell if u need to upgrade RAM or not.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply