identify if a SQL server needs more RAM

• MarvinTheAndriod

  SSCarpal Tunnel

  Points: 4972

  More actions

  November 24, 2011 at 8:22 am

  #252544

  SQL seems to grab whatever RAM it wants of the server, irrespective of whether it is under a heavy load or not, so how is is possible to identify if a SQL server needs more RAM ?

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  November 24, 2011 at 8:23 am

  #1412720

  Entire books have been written on the subject.

  Here's something to give you a hint of wether or not you have a problem.

  /*

  Cost threshold for parallelism (CXPACKET) http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/19/tuning-cost-threshold-of-parallelism-from-the-plan-cache.aspx

  Paul White: Understanding parallelism http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/

  Microsoft White Paper on waits http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc

  Next query by Paul Randal http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx

  */

  WITH Waits AS

  (SELECT

  wait_type,

  wait_time_ms / 1000.0 AS WaitS,

  (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,

  signal_wait_time_ms / 1000.0 AS SignalS,

  waiting_tasks_count AS WaitCount,

  100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,

  ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum

  FROM sys.dm_os_wait_stats

  WHERE wait_type NOT IN (

  'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',

  'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',

  'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',

  'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',

  'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',

  'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_RECEIVE_WAITFOR')

  )

  SELECT

  W1.wait_type AS WaitType,

  CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,

  CAST (W1.WaitS * 1000 / W1.WaitCount AS DECIMAL(14, 2)) AS AvgWait_MS,

  CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,

  CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,

  W1.WaitCount AS WaitCount,

  CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage

  FROM Waits AS W1

  INNER JOIN Waits AS W2

  ON W2.RowNum <= W1.RowNum

  GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage

  HAVING SUM (W2.Percentage) - W1.Percentage < 97 -- percentage threshold

  AND W1.WaitCount > 0;

  GO

  --Last execute July 5th 9:33 AM, changed threshold paral to 15 from 5

  --DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);