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.
sdc248Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdc248Author Commented:
Got you. Thanks.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

sdc248Author Commented:
hi Dave:

How do I find out what is the "max server memory" configuration for my server? Thanks.
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
star_trekCommented:
But before changing the SQL memory I would first check the execution plan and then change it accordingly..my  2 cents
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.
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.
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.
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
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
star_trekCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows 2000

From novice to tech pro — start learning today.