Way to see SQL Server memory usage in QA?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ian Boyd - 16 Jul 2008 14:21 GMT

Is there a way to see the memory (RAM) usage by an SQL Server instance, and
get that value by executing a statement in Query Analyzer?

SQL Server 6.5
SQL Server 7
SQL Server 2000
SQL Server 2005
SQL Server 2008

Reply to this Message

Ian Boyd - 16 Jul 2008 14:39 GMT

> Is there a way to see the memory (RAM) usage by an SQL Server instance,
> and get that value by executing a statement in Query Analyzer?

*the memory currently used by an instance of an SQL Server engine*

> SQL Server 6.5
> SQL Server 7
> SQL Server 2000
> SQL Server 2005
> SQL Server 2008

* query can be different between different versions of SQL Server
** query can be non-existant if a given version of SQL Server doesn't
support such a query
*** required level of access (dbo, public, sysadmin, fixed sys admin, backup
operator, etc) not important

Reply to this Message

TheSQLGuru - 16 Jul 2008 21:59 GMT

1) Use xp_cmdshell to fire a batch file that uses wscript to hit WMI to find
the memory used by each sqlserver.exe process.

2) dbcc memorystatus

3) For 2005+, search BOL for the DMV(s) that expose memory counters and
usage.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

>> Is there a way to see the memory (RAM) usage by an SQL Server instance,
>> and get that value by executing a statement in Query Analyzer?
[quoted text clipped - 12 lines]
> *** required level of access (dbo, public, sysadmin, fixed sys admin,
> backup operator, etc) not important

Reply to this Message

Ian Boyd - 17 Jul 2008 14:12 GMT

> 2) dbcc memorystatus

Excellent! Thank you.

Reply to this Message

Andrew J. Kelly - 16 Jul 2008 23:21 GMT

Other than the MemtoLeave memory on 32 bit you can see the Target and Total
Server memory usage with the sys.dm_os_performance_counters dmv along with
several other memory counters. What is the goal? A typical SQL Server will
max out the memory available to it after a short term anyway and it does not
release memory unless the OS specifically asks for it. Then there are
certain conditions that must be met and idiosyncrasies.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

Reply to this Message

Ian Boyd - 17 Jul 2008 14:17 GMT

> What is the goal?

Customer's live SQL 2005 machine is running queries with poor execution plan
choices. i wanted to limit our development machine to the same amount. But i
don't have access to the server's desktop, performance counters, task
manager, etc. But we could talk to SQL Server through SQL Server.

In the end, i starved our SQL Server to 30MB of RAM, as opposed to the
normal 300MB that it normally sits on. The query still used the 'proper'
execution plan, with the 'proper' number of logical reads. So memory
starving wasn't able to reproduce the problem.

...starting new thread shortly... so tired.

Reply to this Message