How do I diagnose my SQL Server Memory Issues?


I'm currently experience some problems with my SQL Server that runs my Web Application (Slow response, query timeouts, etc).  I'm not much of a database person so I'm really at a loss when there appears to be a problem with the server.  I've been doing some reading and I believe that my problem lies with the usage of server memory.  I believe the person that configured my SQL Server misconfigured a number of settings and now I'm left with the mess.  Here's some info about the server:

* 64-bit Server with Dual Quad Core processors
* Windows Server 2003 (64-bit)
* SQL Server 2005 (64-bit)

I did notice that AWE was enabled for SQL Server, but I didn't really think this was needed for a 64-bit machine.  From what I've read if AWE is enabled on a 64-bit server it will "lock" memory for SQL Server.  I don't know if this is causing my problems or not, hence my post.  I'm afraid to just turn it off and see what happens.

SideNote: the people who set this thing up previously have another app that runs off the same DB Server.  They tried to mess around with Service Broker to do some caching and stuff, but I think the tried to customize the pieces instead of using the standard setup.  I don't know if this could be causing problems also, but figured I'd mention it just in case.  I can try to elaborate if something thinks this could be relevant.

Using as a guide I found the following information which should be helpful to troubleshooting this problem, but honestly I keep getting a little lost in all the numbers.  I can't keep things straight.  Hopefully someone out there will be able to glance at this stuff and maybe see what I'm missing.  Prepare for onslaught of messy server stats.

Performance Tab of Task Manager:
      CPU: 1%
      PF Usage: 4.25 GB
      Totals -
            - Handles: ~15,930
            - Threads: ~794
            - Processes: 46
      Physical Memory (K) -
            - Total: 4,193,360
            - Available: ~191,864
            - System Cache: ~191,324
      Commit Charge (K) -
            - Total: 4,467,708
            - Limit: 10,746,152
            - Peak: 6,934,884
      Kernel Memory (K)
            Total: ~129,040
            Paged: ~93,712
            Nonpaged: ~35,400

Private Bytes for "sqlservr":
      Last = 241,553,408
      Average ~ 241,620,000
      Minimum = 241,553,408
      Maximum = 243,036,160

From sys.dm_os_memory_clerks:
      - AWE is allocated 3.093 GB


      Buffer Counts                  Buffers
      Committed                        388027
      Target                              402761
      Hashed                              240667
      Stolen Potential            309392
      External Reservation      45072
      Min Free                        256
      Visible                              402761
      Available Paging File      791426
      Buffer Distribution            Buffers
      Stolen                              5289
      Free                              4135
      Cached                              131735
      Database (clean)            174818
      Database (dirty)            72050
      I/O                                    0
      Latched                              0
From: "select sum(multi_pages_kb) from sys.dm_os_memory_clerks"
      Result = 26,296
From: "select [type], sum(multi_pages_kb) from sys.dm_os_memory_clerks where multi_pages_kb != 0 group by [type]"

      USERSTORE_OBJPERM                        32
      MEMORYCLERK_SQLSTORENG                  592
      OBJECTSTORE_SNI_PACKET                  96
      MEMORYCLERK_SQLGENERAL                  4576
      CACHESTORE_STACKFRAMES                  16
      CACHESTORE_OBJCP                        1296
      MEMORYCLERK_SOSNODE                        16856
      CACHESTORE_SQLCP                        832
      OBJECTSTORE_LBSS                        192
      CACHESTORE_BROKERTBLACS                  272
      MEMORYCLERK_SNI                              32
From: "select top 10 type, sum(single_pages_kb) as [SPA Mem, Kb] from sys.dm_os_memory_clerks group by type order by sum(single_pages_kb) desc"

      type                                    SPA Mem, Kb
      CACHESTORE_SQLCP                  634488
      CACHESTORE_OBJCP                  49848
      MEMORYCLERK_SQLGENERAL            19904
      MEMORYCLERK_SOSNODE                  13952
      CACHESTORE_PHDR                        13128
      USERSTORE_DBMETADATA            3624
      CACHESTORE_NOTIF                  3296
      MEMORYCLERK_SQLSTORENG            2528
If I'm barking up the wrong tree with all this stuff please let me know.  I'm just sorta feeling around in the dark at this point.

Who is Participating?
pai_prasadConnect With a Mentor Commented:
it seems u have assumed your problems are due to memory !...they can be,,
have you checked what processes are consuming the most memory in the server other than SQL Server?

what exactly is the error u r facing when u mention
>>Slow response, query timeouts, etc

is there a system on which the same queries work fine than this server?
whats the reading for Buffer Manager: Cache Hit Ration on perfmon? is it below 80?
is there heavy paging in the system?
are there any info msgs/errors in sql log sp_readerrorlog

have you stopped Service Broker to check if the problem doesnt arise?
David ToddConnect With a Mentor Senior DBACommented:
perksdevAuthor Commented:
Thanks for the info.  You are right, I may have jumped the gun on blaming memory.  I'm really not a dba or a server admin, so I'm not even sure where to begin.  I did find out that the firewall between my Web Server and my DB server has it's CPU usage maxed out.  The people at the place where my servers are hosted are trying to work on it.  When my two servers are removed from the firewall the CPU usage drops, but rises again when they are plugged back in.  Apparently I have something on my servers that is causing a huge amount of traffic between them.  There are two applications running on my web server, My Application that I've developed and another application developed by another group of people.  The application that is not mine was pretty poorly designed.  It drags way more data back from the DB than it needs and it uses some custom SqlDependency stuff that may or may not be working.  I guess it's possible that this app is just using up way too much bandwidth.  The guys that developed it are long gone, but it still supports a large number of users so I can't just turn it off and see.

In response to your questions from the previous post:

Top Memory Users -
      - ReportingServicesService.exe (104,648 K)
      - sqlservr.exe (81,652 K) {plus AWE locked memory}
      - DatabaseMail90.exe (36,952 K)
      - svchost.exe (30,496 K)
      - explorer.exe (16,828 K)
      - SQLAGENT90.EXE (14,908 K)

The symptoms I'm experiencing are that the site takes a long time to load pages.  Quite frequently I will receive errors of the type "A transport-level error has occurred when sending the request to the server".  These errors occur whenever I try to access the database from my application.
Buffer cach hit ratio -
      - Average: 993686
      - Minimum: 99.592
      - Maximum: 99.745
      - Duration: 1:40
These numbers look fine and I don't notice a lot of paging happening.
When I ran sp_readerrorlog I noticed a lot of entries like the following:      
The query notification dialog on conversation handle '{FEDF3CBB-C192-DC11-87CF-0013724D69ED}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns=""><Code>-8490</Code><Description>Cannot find the remote service &apos;SqlQueryNotificationService-17654a95-473f-4f7c-8a85-7b086b053775&apos; because it does not exist.</Description></Error>'.
From what I've read, this error message is just a normal part of using SqlDependency and Service Broker.

As far as "stopping the service broker", I'm not exactly sure how to do that.  I read a little and it looks like you can stop the individual queues from processing messages, but the only way to stop the whole service is to Enable or Disable it, but I'm not sure what that would do to the application that is using it.

Sorry to keep running in circles, but my app is creeping along and I can't figure out what is slowing it down.

Thanks for the help.  I don't know if I can provide enough information to find a solution though.  I think I'm just gonna have to keep digging till I figure it out.
David ToddConnect With a Mentor Senior DBACommented:

While your system doesn't seem to have memory problems, adding memroy is perhaps the cheapest and easiest thing you can try. 4GB is not much memory for 64bit systems. If you have some around that you can try ...

How big are your database(s)?

You are currently suggesting that network traffic is too high or too slow - do double check that the network cards have selected the correct speed and duplex setting, in fact a recommendation for servers is to manually set it to the correct setting.

Any chance on splitting the database server, for testing if nothing else. What I mean is, restore a backup of database A on another server, and get the application/webpage etc working to Server2. Does the network traffic stay with the server or move. That is, is it database A or database B that has the network traffic issue?

perksdevAuthor Commented:
Thank you guys for all your help.  You definitely got me on the right path.  I think I've narrowed it down to either a firewall problem or simply a problem with that other application going wild.  I'm still working on tracking down the exact solution, but I've been able to increase bandwidth between the servers as a temporary fix until I can fix the problem.  Thanks Again.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.