Diagnosing Memory Use

I've noticed high memory consumption on some of our workstations that eventually cause unacceptable performance problems. Through Task Manager, I see that SQL Server is consuming the bulk of this memory.

A coworker has implemented a system to periodically restart SQL Server (using netstop/netstart). This does hold down memory usage but I'd like to avoid doing this.

I'd like to diagnose SQL Server and find exactly what it is doing that consumes so much memory.

I am aware of three tools to diagnose this:
- Performance Monitor
- Enterprise Manager "Current Activity"
- SQL Server Profiler

Which is the best tool to do this and how would I approach this scenario?The bottleneck may not be the memory consumption. The Task Manager is showing you the max memory sql is allowed to allocate, not the actual memory sql is currently using. You may have something else hosted your system.|||Originally posted by joejcheng
The bottleneck may not be the memory consumption. The Task Manager is showing you the max memory sql is allowed to allocate, not the actual memory sql is currently using. You may have something else hosted your system.

Task Manager shows both Mem Usage (actively paged in) and VM Size (reserved but not necessarily paged in) for the sqlservr.exe process. Those are 651MB/670MB respectively. Using Performance Monitor I see that the disk use is high, and the "Pages/sec" is high.

Shortly after doing a netstop/netstart restart of SQL Server Mem Usage/VM Size = 36MB/44MB respectively and performance is great. Our application has already reconnected to SQL Server and is operating at full speed.

This really has to be SQL Server related.|||Just to make sure, the users all have SQL or MSDE installed on their workstations or laptops, right? You can cap the amount of memory that SQL Server uses, but that will probably increase the amount of disk I/O and may cause even more problems. Anyway, try running this from a command line

osql /S(local) /E /Q"sp_configure 'max server memory (MB)', 256"
osql /S(local) /E /Q"reconfigure with override"

You may need to modify that a bit with the proper workstation\instancename, but it will keep SQL Server from hogging memory. Good luck.|||Originally posted by MCrowley
Just to make sure, the users all have SQL or MSDE installed on their workstations or laptops, right? You can cap the amount of memory that SQL Server uses, but that will probably increase the amount of disk I/O and may cause even more problems. Anyway, try running this from a command line

osql /S(local) /E /Q"sp_configure 'max server memory (MB)', 256"
osql /S(local) /E /Q"reconfigure with override"

You may need to modify that a bit with the proper workstation\instancename, but it will keep SQL Server from hogging memory. Good luck.

Thanks for the feedback!

Actually, there are no users just an internal app that runs 24/7.

We could cut SQL Server's memory allotment but as you said, that will increase disk use which is the main problem. What I want to know is why it is using the memory that it is. Once I identify that, I can redesign to use less resources. I suspect a design problem rather than simply a lack of resources.|||Ahh. Then your problem seems to be that your database has grown to more than probably 500 MB. More likely to more than 1GB. SQL Server (and Oracle, Informix, DB2, etc.) will store information in memory for faster access. You can cut down on the amount of I/O you need to perform by reducing redundancy in the data, or applying indexes on the tables that are queried most often.

The first method may be beyond help, as reconfiguring the database is near impossible without a total re-write of the application. As for the second, open Profiler in the SQL program group, and connect to your server. Under Events, remove Security Audit and Sessions. On the filters page, add a limit under Reads of greater than or equal say 100 reads. 1 read is only a logical I/O rather than a physical one, but you should be able to figure out quickly which queries are your problem children. Good luck.|||Hi,

I had similar doubts and i would like you to see the following link, which is a discussion on the same forum
http://www.dbforums.com/showthread.php?threadid=966886&referrerid=47114

regards,
henry