• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

memory as a performance hit

Hi:

My company's server is a Windows 2000 machine that has 3G memory and a SQL Server 2000 DBMS. I have a stored procedure that runs slowly. Is there a qucik, easy way to see whether memory is the major performance hit? While the stored procedure is running the mem usage on Task Manager shows about 1.7G. Does that a guarantee that the memory size isn't a problem? Thanks.
0
sdc248
Asked:
sdc248
  • 5
  • 4
  • 3
2 Solutions
 
star_trekCommented:
WHen the SP is running you can you perfmon to see how memory is used.

Also in query analyser when you are running stored procedure turn on Execution Plan (query -execution plan) you can see the I/O cost for running that query for each step. That way you can optimize the query too.
0
 
Dave_HuntCommented:
My understanding is that Windows 2000 Server requires a /3GB and /PAE switches to be inserted into the boot.ini to allow applications to use more than 2GB of RAM.  Also of importance is the fact that SQL Server WILL use almost all available memory on the server unless you change the Max Server Memory option.

I would check out some of the articles I've referenced below, they should get you on the right track.

I agree with star_trek, no matter how well the server runs, inefficient programming of a stored procedure will slow it down.

Dave

Intel Physical Addressing Extensions (PAE) in Windows 2000 -> http://support.microsoft.com/kb/268363/
Large memory support is available in Windows Server 2003 and in Windows 2000 -> http://support.microsoft.com/kb/283037/en-us
How to adjust memory usage by using configuration options in SQL Server -> http://support.microsoft.com/kb/321363/en-us
0
 
sdc248Author Commented:
Got you. Thanks.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sdc248Author Commented:
hi Dave:

How do I find out what is the "max server memory" configuration for my server? Thanks.
0
 
Dave_HuntCommented:
Using SQL Server Enterprise Manager, right click on the server and select properties, there is a Memory tab that allows you to adjust memory utilization for SQL.

Dave
0
 
star_trekCommented:
But before changing the SQL memory I would first check the execution plan and then change it accordingly..my  2 cents
0
 
Dave_HuntCommented:
I completely agree, good performance does not compensate for poor programming!

We have found that if we allow SQL to "auto tune" it's memory, other applications running on the server have slower response.  We ussually limit SQL to about 75% of the physical memory, but that is our environment.  Depending on what other applications you are trying to run on the server, your milage will vary.
0
 
star_trekCommented:
I would do execution plan not only for poor programming but also see how much of your resources are taken by the query. That way sometimes if you have an huge table then query takes kind of long time taking all of the resources. If you change the SQL memory more than what is required then all the other applications will have no memory as it has been already taken and other applications will be slower as Dave said.
0
 
sdc248Author Commented:
Advices taken. Two questions please:
1. I open perfmon but see nothing, ie. the red bar stands still on the left side of the graph. Do I need to do something to "start" it?
2. What are the warning signs in Execution Plan? What is "query cost," the running time? the memory?

Thanks.
0
 
sdc248Author Commented:
I read somewhere that SQL Server 2000 Standard edition only supports 2G of memory. The table my stored procedure querys on is about 4G and, depending on the parameters supplied, it would need all of the rows. Therefore I really suspect memory is a major, if not the only, performance bottleneck.

I hereby increase the points for this thread. It would be very much appreciated if anyone could give me tips of how to aduit performance, especially I would appreciate comments on the two questions mentioned in the above posting. Thanks
0
 
star_trekCommented:
In perfmon click on + on Performance logs and alerts. Right click on alerts - new alerts -  give some name to it and click ok - give a comment to recogise why it is being used for - click add button - performance object - memory and create the alerts on what you can do available in Mbytes, page faults and anything else that you think is right.
Also do alerts for paging file in the same way. You can do an application log entry or network message sent.

Also what warning are you getting in Execution plan. I/O will give how much resources are taken
0
 
star_trekCommented:
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now