High Memory Usage by Sql server and Very Slow

Question

Hi I have a serious performance issue with my Sql Server database. The database is dead slow. It is using 85% of the memory continuously and the CPU is fluctuating between 75 aned 100 percentage. I have 12 GB memory installed on the machine and Sql server is allowed to use a maximum of 8 GB.Any suggestions to improve this would be most appreciated.

Answer 1

Hi Karon,

I suggest you to follow the link below to identify Memory Bottle necks:

http://www.mssqltips.com/sqlservertip/2304/how-to-identify-microsoft-sql-server-memory-bottlenecks/

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

You can also execute the following two query to track top 10 queries using the CPU:

-- Find queries that take the most CPU overall  
SELECT TOP 50  
    ObjectName          = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
    ,TextData           = qt.text  
    ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads  
    ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads  
    ,Executions         = qs.execution_count  
    ,TotalCPUTime       = qs.total_worker_time  
    ,AverageCPUTime     = qs.total_worker_time/qs.execution_count  
    ,DiskWaitAndCPUTime = qs.total_elapsed_time  
    ,MemoryWrites       = qs.max_logical_writes  
    ,DateCached         = qs.creation_time  
    ,DatabaseName       = DB_Name(qt.dbid)  
    ,LastExecutionTime  = qs.last_execution_time  
 FROM sys.dm_exec_query_stats AS qs  
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
 ORDER BY qs.total_worker_time DESC 
   
-- Find queries that have the highest average CPU usage  
SELECT TOP 50  
    ObjectName          = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
    ,TextData           = qt.text  
    ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads  
    ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads  
    ,Executions         = qs.execution_count  
    ,TotalCPUTime       = qs.total_worker_time  
    ,AverageCPUTime     = qs.total_worker_time/qs.execution_count  
    ,DiskWaitAndCPUTime = qs.total_elapsed_time  
    ,MemoryWrites       = qs.max_logical_writes  
    ,DateCached         = qs.creation_time  
    ,DatabaseName       = DB_Name(qt.dbid)  
    ,LastExecutionTime  = qs.last_execution_time  
 FROM sys.dm_exec_query_stats AS qs  
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
 ORDER BY qs.total_worker_time/qs.execution_count DESC 

You can also execute the following query to track the top queries utilizing your memory using the follow script:

/**********************************************************  
*   top procedures memory consumption per execution  
*   (this will show mostly reports & jobs)  
***********************************************************/  
SELECT TOP 100 *  
FROM 
(  
    SELECT 
         DatabaseName       = DB_NAME(qt.dbid)  
        ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
        ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
        ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
        ,Executions         = SUM(qs.execution_count)  
        ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
        ,CPUTime            = SUM(qs.total_worker_time)  
        ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
        ,MemoryWrites       = SUM(qs.max_logical_writes)  
        ,DateLastExecuted   = MAX(qs.last_execution_time)  
   
    FROM sys.dm_exec_query_stats AS qs  
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
    GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
   
) T  
ORDER BY IO_Per_Execution DESC 
   
/**********************************************************  
*   top procedures memory consumption total  
*   (this will show more operational procedures)  
***********************************************************/  
SELECT TOP 100 *  
FROM 
(  
    SELECT 
         DatabaseName       = DB_NAME(qt.dbid)  
        ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
        ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
        ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
        ,Total_IO_Reads     = SUM(qs.total_physical_reads + qs.total_logical_reads)  
        ,Executions         = SUM(qs.execution_count)  
        ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
        ,CPUTime            = SUM(qs.total_worker_time)  
        ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
        ,MemoryWrites       = SUM(qs.max_logical_writes)  
        ,DateLastExecuted   = MAX(qs.last_execution_time)  
   
    FROM sys.dm_exec_query_stats AS qs  
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
    GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
) T  
ORDER BY Total_IO_Reads DESC 
   
/**********************************************************  
*   top adhoc queries memory consumption total  
***********************************************************/  
SELECT TOP 100 *  
FROM 
(  
    SELECT 
         DatabaseName       = DB_NAME(qt.dbid)  
        ,QueryText          = qt.text  
        ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
        ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
        ,Total_IO_Reads     = SUM(qs.total_physical_reads + qs.total_logical_reads)  
        ,Executions         = SUM(qs.execution_count)  
        ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
        ,CPUTime            = SUM(qs.total_worker_time)  
        ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
        ,MemoryWrites       = SUM(qs.max_logical_writes)  
        ,DateLastExecuted   = MAX(qs.last_execution_time)  
   
    FROM sys.dm_exec_query_stats AS qs  
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
    WHERE OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) IS NULL 
    GROUP BY DB_NAME(qt.dbid), qt.text, OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
) T  
ORDER BY Total_IO_Reads DESC 
   
/**********************************************************  
*   top adhoc queries memory consumption per execution  
***********************************************************/  
SELECT TOP 100 *  
FROM 
(  
    SELECT 
         DatabaseName       = DB_NAME(qt.dbid)  
        ,QueryText          = qt.text  
        ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads  
        ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads  
        ,Total_IO_Reads     = SUM(qs.total_physical_reads + qs.total_logical_reads)  
        ,Executions         = SUM(qs.execution_count)  
        ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count)  
        ,CPUTime            = SUM(qs.total_worker_time)  
        ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time)  
        ,MemoryWrites       = SUM(qs.max_logical_writes)  
        ,DateLastExecuted   = MAX(qs.last_execution_time)  
   
    FROM sys.dm_exec_query_stats AS qs  
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
    WHERE OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) IS NULL 
    GROUP BY DB_NAME(qt.dbid), qt.text, OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)  
) T  
ORDER BY IO_Per_Execution DESC 

Once you identity the query, Look at its execution plan and then optimise the query as necessrary by creating the missing indexes etc.

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 3

Hi,

What is Available Memory on system? Check if available memory is constnatly high(more then 2 GB), you can increease the MAX server Memory. This will help to reduce some of the memory pressure as well less pages needs to pulled from Disk. This will help in reducing the CPU utilization as well to some extent.

Next, you can check for the missing indexes using Mssing Index DMVs to see if there are any big improvement can be made. You can also query plan catch to find out misssing indexes.

---

- Chintak (My Blog)

Answer 4

Jus additional note: Check the fragmentation of your indexes and if the fragmentation is high then SQL will perform poorly. You can check the fragmentation of the indexes using the following query:

--- Hide quoted text -
SELECT
             SCHEMA_NAME(o.schema_id) AS SchemaName               
            ,OBJECT_NAME(o.object_id) AS TableName
            ,i.name  AS IndexName
            ,i.type_desc AS IndexType
            ,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned
            ,COALESCE(fg.name ,fgp.name) AS FileGroupName
            ,p.partition_number AS PartitionNumber
            ,p.rows AS PartitionRows
            ,dmv.Avg_Fragmentation_In_Percent
            ,dmv.Fragment_Count
            ,dmv.Avg_Fragment_Size_In_Pages
            ,dmv.Page_Count 
            ,prv_left.value  AS PartitionLowerBoundaryValue
            ,prv_right.value AS PartitionUpperBoundaryValue
            ,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange
            ,pf.name        AS PartitionFunction
            ,ds.name AS PartitionScheme
FROM sys.partitions AS p WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
            ON i.object_id = p.object_id
            AND i.index_id = p.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
            ON o.object_id = i.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') dmv
            ON dmv.OBJECT_ID = i.object_id
            AND dmv.index_id = i.index_id
            AND dmv.partition_number  = p.partition_number
LEFT JOIN sys.data_spaces AS ds WITH (NOLOCK)
      ON ds.data_space_id = i.data_space_id
LEFT JOIN sys.partition_schemes AS ps WITH (NOLOCK)
      ON ps.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_functions AS pf WITH (NOLOCK)
      ON pf.function_id = ps.function_id
LEFT JOIN sys.destination_data_spaces AS dds WITH (NOLOCK)
      ON dds.partition_scheme_id = ps.data_space_id
      AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups AS fg WITH (NOLOCK)
      ON fg.data_space_id = i.data_space_id
LEFT JOIN sys.filegroups AS fgp WITH (NOLOCK)
      ON fgp.data_space_id = dds.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left WITH (NOLOCK)
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right WITH (NOLOCK)
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0  
ORDER BY
            SchemaName
    ,TableName
    ,IndexName
    ,PartitionNumber

If indexes are not fragmented then it might be your query statistics are out of date. Execute sp_updatestats to update the statistics that needs updating.

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 5

Hi I have a serious performance issue with my Sql Server database. The database is dead slow. It is using 85% of the memory continuously and the CPU is fluctuating between 75 aned 100 percentage. I have 12 GB memory installed on the machine and Sql server is allowed to use a maximum of 8 GB.Any suggestions to improve this would be most appreciated.

Follow the optimization guidelines in the following article:

http://www.sqlusa.com/articles/query-optimization/

A single frequent query may kill your system!

Optimization 1-2-3: REBUILD indexes every weekend, UPDATE statistics every night, eliminate missing indexes.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING