[Solved] SQL server 2012 slow when ram is near max allowed for sql

EverSQL Database Performance Knowledge Base

SQL server 2012 slow when ram is near max allowed for sql

Database type:

Its been a month since this problem started and i haven't found the cause or a solution for the problem.

I have a visual basic program to view our business inteligence quering an sql server 2012.

The host has 48GB of memory, shared with 2 VM's:

When the memory of the VM SQL reaches around 90% a sudden drop of performance is observed. Queries that normally take 2 secs go to 30-40 secs.

If i restart SQL everything goes fine for the next 2-3 days. Editing the max server memory in SSMS for up or down also does the trick.

UPDATE:

WHEN SERVER IS FAST:

sql server sql server settings sql server windows server 2012 SQL VM windows server 2012 SQL VM

    SELECT counter_name, instance_name, mb = cntr_value/1024.0
  FROM sys.dm_os_performance_counters 
  WHERE (counter_name = N'Cursor memory usage' and instance_name <> N'_Total')
  OR (instance_name = N'' AND counter_name IN 
       (N'Connection Memory (KB)', N'Granted Workspace Memory (KB)', 
        N'Lock Memory (KB)', N'Optimizer Memory (KB)', N'Stolen Server Memory (KB)', 
        N'Log Pool Memory (KB)', N'Free Memory (KB)')
  ) ORDER BY mb DESC;

enter image description here

FUTURE UPDATE:

WHEN SERVER IS SLOW: TO BE UPDATED

Any ideas?

Thanks

Pedro

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid OR Conditions By Using UNION (modified query below): In mosts cases, filtering using the OR operator cannot be applied using indexes. A more optimized alternative will be to split the query to two parts combined with a UNION clause, while each query holds one part of the original OR condition.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  3. Use UNION ALL instead of UNION (query line: 24): Always use UNION ALL unless you need to eliminate duplicate records. By using UNION ALL, you'll avoid the expensive distinct operation the database applies when using a UNION clause.
Optimal indexes for this query:
CREATE INDEX dm_performance_idx_instance_name_counter_name ON sys.dm_os_performance_counters (instance_name,counter_name);
CREATE INDEX dm_performance_idx_counter_name_instance_name ON sys.dm_os_performance_counters (counter_name,instance_name);
The optimized query:
SELECT
        sys_dm_os_performance_counters_counter_name,
        sys_dm_os_performance_counters_instance_name,
        sys.dm_os_performance_counters.mb = sys.dm_os_performance_counters.cntr_value / 1024.0 
    FROM
        ((SELECT
            sys.dm_os_performance_counters.counter_name AS sys_dm_os_performance_counters_counter_name,
            sys.dm_os_performance_counters.instance_name AS sys_dm_os_performance_counters_instance_name,
            sys.dm_os_performance_counters.mb = sys.dm_os_performance_counters.cntr_value / 1024.0,
            sys.dm_os_performance_counters.mb AS sys_dm_os_performance_counters_mb 
        FROM
            sys.dm_os_performance_counters 
        WHERE
            (
                sys.dm_os_performance_counters.instance_name = N'' 
                AND sys.dm_os_performance_counters.counter_name IN (
                    N'Connection Memory (KB)', N'Granted Workspace Memory (KB)', N'Lock Memory (KB)', N'Optimizer Memory (KB)', N'Stolen Server Memory (KB)', N'Log Pool Memory (KB)', N'Free Memory (KB)'
                )
            ) 
        ORDER BY
            sys_dm_os_performance_counters_mb DESC) 
    UNION
    (
        SELECT
            sys.dm_os_performance_counters.counter_name AS sys_dm_os_performance_counters_counter_name,
            sys.dm_os_performance_counters.instance_name AS sys_dm_os_performance_counters_instance_name,
            sys.dm_os_performance_counters.mb = sys.dm_os_performance_counters.cntr_value / 1024.0,
            sys.dm_os_performance_counters.mb AS sys_dm_os_performance_counters_mb 
        FROM
            sys.dm_os_performance_counters 
        WHERE
            (
                sys.dm_os_performance_counters.counter_name = N'Cursor memory usage' 
                AND sys.dm_os_performance_counters.instance_name <> N'_Total'
            ) 
        ORDER BY
            sys_dm_os_performance_counters_mb DESC
    )
) AS union1 
ORDER BY
union1.sys_dm_os_performance_counters_mb DESC

Related Articles



* original question posted on StackOverflow here.