How do I diagnose my SQL Server Memory Issues?

Posted on 2007-11-20
Medium Priority
Last Modified: 2010-04-21

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 http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx 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.

Question by:perksdev
  • 2
  • 2
LVL 10

Accepted Solution

pai_prasad earned 800 total points
ID: 20323571
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 ..run sp_readerrorlog

have you stopped Service Broker to check if the problem doesnt arise?
LVL 35

Assisted Solution

by:David Todd
David Todd earned 1200 total points
ID: 20323900

Author Comment

ID: 20328739
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="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><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.
LVL 35

Assisted Solution

by:David Todd
David Todd earned 1200 total points
ID: 20329562

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?


Author Closing Comment

ID: 31410159
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.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question