Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: 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?

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Diagnosing Memory Use

    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.

  3. #3
    Join Date
    Oct 2003
    Posts
    268

    Re: Diagnosing Memory Use

    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.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  5. #5
    Join Date
    Oct 2003
    Posts
    268
    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.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  7. #7
    Join Date
    Nov 2003
    Posts
    15

    Re: Diagnosing Memory Use

    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.p...ferrerid=47114

    regards,
    henry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •