Check available memory for SQL 2000

We have a batch that takes 30 mins to run, but sometimes
takes 5 hours. I suspect the problem is the amount of
memory available to SQL Server (2000 SP3).
I have noticed that when SQL Server uses 600 Mb, the batch
lasts 30 mins, and when SQL Server only uses 300 Mb, it
lasts 5 hours.
How can I document that SQL Server would need say 500 Mb ?
Is there an SQL statement that would show how much memory
is available in the system to be taken by SQL Server?
Can I use the profiler to see when SQL Server is trying to
obtain more memory, and the request is denyied or fails?
The other application suspected of using up memory on the
server is Analysis Services. Is there a way I can force
Analysis Services to relase memory without stopping and
restarting the service?Hi James,
You can find out how much memory SQL Server wants to use by checking the SQL
Server:Memory Manager:Target Server Memory counter in the Windows
Performance Monitor. The SQL Server:Memory Manager:Total Server Memory
counter indicates how much memory SQL Server actually uses, and of course
you'd like these to counter to show very similar values.
You can't check from inside SQL Server how much 'free' memory is available,
so the best thing to do is probably configure your server so that SQL Server
always has the right amount of memory available.
--
Jacco Schalkwijk
SQL Server MVP
"James" <news@.att.com> wrote in message
news:465e01c3ff7c$16dee5c0$a501280a@.phx.gbl...
> We have a batch that takes 30 mins to run, but sometimes
> takes 5 hours. I suspect the problem is the amount of
> memory available to SQL Server (2000 SP3).
> I have noticed that when SQL Server uses 600 Mb, the batch
> lasts 30 mins, and when SQL Server only uses 300 Mb, it
> lasts 5 hours.
> How can I document that SQL Server would need say 500 Mb ?
> Is there an SQL statement that would show how much memory
> is available in the system to be taken by SQL Server?
> Can I use the profiler to see when SQL Server is trying to
> obtain more memory, and the request is denyied or fails?
> The other application suspected of using up memory on the
> server is Analysis Services. Is there a way I can force
> Analysis Services to relase memory without stopping and
> restarting the service?|||There is a 'Server memory change' event that you can track using Profiler or
server side tracing procedures.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"James" <news@.att.com> wrote in message
news:465e01c3ff7c$16dee5c0$a501280a@.phx.gbl...
We have a batch that takes 30 mins to run, but sometimes
takes 5 hours. I suspect the problem is the amount of
memory available to SQL Server (2000 SP3).
I have noticed that when SQL Server uses 600 Mb, the batch
lasts 30 mins, and when SQL Server only uses 300 Mb, it
lasts 5 hours.
How can I document that SQL Server would need say 500 Mb ?
Is there an SQL statement that would show how much memory
is available in the system to be taken by SQL Server?
Can I use the profiler to see when SQL Server is trying to
obtain more memory, and the request is denyied or fails?
The other application suspected of using up memory on the
server is Analysis Services. Is there a way I can force
Analysis Services to relase memory without stopping and
restarting the service?|||Jame
By default SQL Server should use memory dynamically. In most cases this works well. Your comments about the memory available, suggests to me that you are running other applications on the server. You really should try to run SQL Server on a dedicated server, you can get unpredictable results when it shares a server with other applications
Regard
John|||As I wrote, the other application is Analysis Services
We sell a solution including SQL Server and AS, and we cannot ask the customers to run the solution on 2 machines instead of one.|||Thanks, I had missed that one
You guys don't know a way of throttling Analysis Services' memory, do you?|||May be there's a way, but I don't work with AS. You might want to post this
to the analysis services group.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:E96727BB-930E-4BCE-9731-9F8E4BA4C3E8@.microsoft.com...
Thanks, I had missed that one.
You guys don't know a way of throttling Analysis Services' memory, do you?|||Hi James,
I learned that you want to find a way to force Analysis Services to release
memory without stopping and restarting the service. Based on my experience,
there is no good way to force Analysis Services to release memory and have
Analysis Services maintain adequate performance at the same time. It seems
you have to add new memory hardware or release the memory of other
applications to lessen the pressure of lack of memory.
I found the following articles for your reference.
Chapter 26 - Performance Tuning Analysis Services
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/p
art6/c2661.mspx
Thanks & Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Check available memory for SQL 2000

We have a batch that takes 30 mins to run, but sometimes
takes 5 hours. I suspect the problem is the amount of
memory available to SQL Server (2000 SP3).
I have noticed that when SQL Server uses 600 Mb, the batch
lasts 30 mins, and when SQL Server only uses 300 Mb, it
lasts 5 hours.
How can I document that SQL Server would need say 500 Mb ?
Is there an SQL statement that would show how much memory
is available in the system to be taken by SQL Server?
Can I use the profiler to see when SQL Server is trying to
obtain more memory, and the request is denyied or fails?
The other application suspected of using up memory on the
server is Analysis Services. Is there a way I can force
Analysis Services to relase memory without stopping and
restarting the service?Hi James,
You can find out how much memory SQL Server wants to use by checking the SQL
Server:Memory Manager:Target Server Memory counter in the Windows
Performance Monitor. The SQL Server:Memory Manager:Total Server Memory
counter indicates how much memory SQL Server actually uses, and of course
you'd like these to counter to show very similar values.
You can't check from inside SQL Server how much 'free' memory is available,
so the best thing to do is probably configure your server so that SQL Server
always has the right amount of memory available.
Jacco Schalkwijk
SQL Server MVP
"James" <news@.att.com> wrote in message
news:465e01c3ff7c$16dee5c0$a501280a@.phx.gbl...
> We have a batch that takes 30 mins to run, but sometimes
> takes 5 hours. I suspect the problem is the amount of
> memory available to SQL Server (2000 SP3).
> I have noticed that when SQL Server uses 600 Mb, the batch
> lasts 30 mins, and when SQL Server only uses 300 Mb, it
> lasts 5 hours.
> How can I document that SQL Server would need say 500 Mb ?
> Is there an SQL statement that would show how much memory
> is available in the system to be taken by SQL Server?
> Can I use the profiler to see when SQL Server is trying to
> obtain more memory, and the request is denyied or fails?
> The other application suspected of using up memory on the
> server is Analysis Services. Is there a way I can force
> Analysis Services to relase memory without stopping and
> restarting the service?|||There is a 'Server memory change' event that you can track using Profiler or
server side tracing procedures.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"James" <news@.att.com> wrote in message
news:465e01c3ff7c$16dee5c0$a501280a@.phx.gbl...
We have a batch that takes 30 mins to run, but sometimes
takes 5 hours. I suspect the problem is the amount of
memory available to SQL Server (2000 SP3).
I have noticed that when SQL Server uses 600 Mb, the batch
lasts 30 mins, and when SQL Server only uses 300 Mb, it
lasts 5 hours.
How can I document that SQL Server would need say 500 Mb ?
Is there an SQL statement that would show how much memory
is available in the system to be taken by SQL Server?
Can I use the profiler to see when SQL Server is trying to
obtain more memory, and the request is denyied or fails?
The other application suspected of using up memory on the
server is Analysis Services. Is there a way I can force
Analysis Services to relase memory without stopping and
restarting the service?|||James
By default SQL Server should use memory dynamically. In most cases this work
s well. Your comments about the memory available, suggests to me that you ar
e running other applications on the server. You really should try to run SQL
Server on a dedicated serv
er, you can get unpredictable results when it shares a server with other app
lications.
Regards
John|||Thanks, I had missed that one.
You guys don't know a way of throttling Analysis Services' memory, do you?|||As I wrote, the other application is Analysis Services.
We sell a solution including SQL Server and AS, and we cannot ask the custom
ers to run the solution on 2 machines instead of one.|||May be there's a way, but I don't work with AS. You might want to post this
to the analysis services group.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:E96727BB-930E-4BCE-9731-9F8E4BA4C3E8@.microsoft.com...
Thanks, I had missed that one.
You guys don't know a way of throttling Analysis Services' memory, do you?|||Hi James,
I learned that you want to find a way to force Analysis Services to release
memory without stopping and restarting the service. Based on my experience,
there is no good way to force Analysis Services to release memory and have
Analysis Services maintain adequate performance at the same time. It seems
you have to add new memory hardware or release the memory of other
applications to lessen the pressure of lack of memory.
I found the following articles for your reference.
Chapter 26 - Performance Tuning Analysis Services
http://www.microsoft.com/resources/.../reskit/en-us/p
art6/c2661.mspx
Thanks & Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.