How to Analyze 'Dbcc Memorystatus' Result in SQL Server 2008

SQL Server Express 2008 R2 Memory Issues

The ERRORLOG will contain some important information about the memory allocation pattern when things started to degrade. The lines will look similar to this:

  MEMORYCLERK_SQLGENERAL (node 1)                                  KB 
   ---------------------------------------------------------------- --------------------
   VM Reserved                                                      0
   VM Committed                                                     0
   AWE Allocated                                                    0
   SM Reserved                                                      0
   SM Commited                                                      0
   SinglePage Allocator                                             136
   MultiPage Allocator                                              0

   (7 row(s) affected)

Search your ERRORLOG files for such occurrences, the follow the guidance from How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005, since the output of DBCC MEMORYSTATUS and the output in the ERRORLOG due to OOM status is quite similar.

analyze SQL Server performance issue?

A good start is to use SQL Server Profiler to identify the long-running queries.

Once you have an idea about which ones are problematic, you can use SQL Server Management Studio to examine their query plans to see if you should add any indexes, etc.

SQL query takes too long to render/return the data

Please check and watch carefully onto these points according to your two servers and check both against each other:

1. Configuration of the Database instance
2. Setup of queried database/tables
3. Overall process performance
4. System memory (is it using HDD to extend RAM when the lag occurs?)
5. HDD setup/status (slow/defect/fragmented HDD can cause problems like this, too)
6. Try command "ALTER DATABASE SET PARAMETERIZATION FORCED GO"
7. Use DBCC MEMORYSTATUS for deep analysis

My preferred documentation for possible deep analysis to be used as an overview:
http://sqlserverplanet.com/troubleshooting/sql-server-slowness

You can also have a look here:
How to analyze 'dbcc memorystatus' result in SQL Server 2008

Otherwise, you can search for information (cause I am not allowed to post more links) at mssqltips dot com about how to identify sql server memory/cpu bottlenecks. They also have nice documentations onto that.

Hope this helps you.

How to find queries that used most memory for sorts

sys.dm_exec_query_memory_grants:

• requested_memory_kb: Total requested amount of memory in kilobytes
• granted_memory_kb: Total amount of memory actually granted in kilobytes.

---

Related Topics