Solved

How do I diagnose my SQL Server Memory Issues?

Posted on 2007-11-20
5
2,924 Views
Last Modified: 2010-04-21
Hello,

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
* 4GB RAM
* 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

From DBCC MEMORYSTATUS:

      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]"

      MEMORYCLERK_SQLOPTIMIZER            112
      USERSTORE_OBJPERM                        32
      MEMORYCLERK_SQLSTORENG                  592
      OBJECTSTORE_SNI_PACKET                  96
      MEMORYCLERK_SQLGENERAL                  4576
      CACHESTORE_STACKFRAMES                  16
      MEMORYCLERK_SQLSERVICEBROKER      320
      MEMORYCLERK_SQLBUFFERPOOL            1072
      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
      CACHESTORE_BROKERREADONLY      6136
      OBJECTSTORE_LOCK_MANAGER      4000
      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.

Thanks!
0
Comment
Question by:perksdev
  • 2
  • 2
5 Comments
 
LVL 10

Accepted Solution

by:
pai_prasad earned 200 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?
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 300 total points
ID: 20323900
0
 

Author Comment

by:perksdev
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.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 300 total points
ID: 20329562
Hi,

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?

HTH
  David
0
 

Author Closing Comment

by:perksdev
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now