How to troubleshoot really high memory waits?

Question

How to troubleshoot really high memory waits?

ricardo_chicas

SSCertifiable

Points: 5024
More actions
November 3, 2011 at 9:05 am

#259532

Hello all
Right now I see a huge wait time on memory something in the range of <25477139 ms/sec, how can i find the culprit?
My server is a 16 cpu 64 bit, sql server 2008 r2 machine with 48 gb of ram ( 42 are dedicated to sql )
Thanks in advance

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

Take a look at this: http://blog.sqlauthority.com/2011/02/01/sql-server-wait-stats-wait-types-wait-queues-day-0-of-28-2/

Thanks,

Jared

Jared
CE - Microsoft

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

What's the wait type? What's the wait resource?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 3

Hi

It is on the activity monitor the Activity monitor, under resource waits/Memory

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Ok, but that doesn't tell us anything useful. You're going to have to dig in further if you want to investigate this.

sys.dm_os_waiting_tasks, sys.dm_os_wait_stats are the two DMVs to start with here (maybe sys.dm_exec_requests too)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kaahie SSC Veteran Points: 282 More actions · Answer 5

I would use sys.dm_os_waiting_tasks DMV as starting point. It will list all threats that are waiting and what they are waiting for. That will tell you at least what is causing the slowness right now. and dig deeper from there.

Thanks

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 6

Thanks, this is what I get with

select * from sys.dm_os_wait_stats order by 3 desc

Wait type--------------------Waiting task count--Wait time ms-----max wait------signal wait time ms

SOS_SCHEDULER_YIELD-------2580613123-------12811631972-------17322-------12809029011

CXPACKET--------------------955460838---------6623920160-----7045715--------1082202989

BROKER_RECEIVE_WAITFOR------1981445---------3992879800------599993-----------12265241

PAGEIOLATCH_SH--------------72526907---------2632302999-------20873-----------10017518

The result from select * from sys.dm_os_waiting_tasks order by wait_duration_ms desc is ( the other columns were null):

Wait_duration_ms----wait_type

933164947-----------KSOURCE_WAKEUP

26388613------------BROKER_TRANSMITTER

26388599------------BROKER_TRANSMITTER

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

None of those are memory-related.

SOS_Scheduler_yield suggests you've got a lot of queries that use a lot of CPU, possible optimisation opportunities.

All three of the waits you got from waiting tasks are system waits and should be ignored.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jared SSC Guru Points: 61793 More actions · Answer 8

ricardo_chicas (11/3/2011)
Thanks, this is what I get with
select * from sys.dm_os_wait_stats order by 3 desc
Wait type--------------------Waiting task count--Wait time ms-----max wait------signal wait time ms
SOS_SCHEDULER_YIELD-------2580613123-------12811631972-------17322-------12809029011
CXPACKET--------------------955460838---------6623920160-----7045715--------1082202989
BROKER_RECEIVE_WAITFOR------1981445---------3992879800------599993-----------12265241
PAGEIOLATCH_SH--------------72526907---------2632302999-------20873-----------10017518
The result from select * from sys.dm_os_waiting_tasks order by wait_duration_ms desc is ( the other columns were null):
Wait_duration_ms----wait_type
933164947-----------KSOURCE_WAKEUP
26388613------------BROKER_TRANSMITTER
26388599------------BROKER_TRANSMITTER

If you go to the link I sent you and click on the "introduction" There is a script that will filter out waits that are ok. It will also order by amount of waits. However, you may want to run this first and gather stats for some time: DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR)

Or just run that script from the link and post the results.

Thanks,

Jared

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 9

Here:

-- Isolate top waits for server instance since last restart or statistics clear

WITH Waits AS

(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

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

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', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))

SELECT W1.wait_type,

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

INNER JOIN Waits AS W2

ON W2.rn <= W1.rn

GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold

GO

Jared

Jared
CE - Microsoft

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 10

Hello

This is what I get with that query:

SOS_SCHEDULER_YIELD14335845.6532.2232.22

CXPACKET7263604.7416.3348.55

BROKER_RECEIVE_WAITFOR4317801.949.7158.26

PAGEIOLATCH_SH2793680.546.2864.54

RESOURCE_SEMAPHORE2313094.755.2069.74

BROKER_TRANSMITTER1889177.544.2573.98

WRITELOG1853443.944.1778.15

PAGEIOLATCH_EX1508445.863.3981.54

ASYNC_NETWORK_IO1161229.532.6184.15

OLEDB971904.572.1886.33

BROKER_EVENTHANDLER966467.532.1788.51

ONDEMAND_TASK_QUEUE961469.282.1690.67

PAGELATCH_EX878936.031.9892.64

LATCH_EX434981.770.9893.62

SLEEP_BPOOL_FLUSH330735.770.7494.36

PAGELATCH_SH249026.220.5694.92

LCK_M_X223864.300.5095.43

LCK_M_S223578.960.5095.93

IO_COMPLETION170426.110.3896.31

BACKUPBUFFER149666.190.3496.65

PREEMPTIVE_OS_AUTHENTICATIONOPS147068.050.3396.98

LCK_M_IX139609.950.3197.29

BACKUPIO130742.120.2997.59

ASYNC_IO_COMPLETION127503.020.2997.87

PREEMPTIVE_OS_REVERTTOSELF103287.270.2398.11

LCK_M_IS84086.940.1998.29

LCK_M_U75274.170.1798.46

PREEMPTIVE_OS_SECURITYOPS66652.980.1598.61

TRACEWRITE59115.150.1398.75

PREEMPTIVE_OS_LOOKUPACCOUNTSID49788.280.1198.86

PREEMPTIVE_OS_DELETESECURITYCONTEXT45821.170.1098.96

LCK_M_SCH_M44636.990.1099.06

Jared SSC Guru Points: 61793 More actions · Answer 11

Great, now go back to the same link I gave earlier and look at days 6, 7, and 8 in the series. Focus on day 8 first. You will see that he explains how to troubleshoot the different waits. Just to be a little more specific, you may want to clear the wait stats and run the first query again to get an accurate representation. However, I would start with looking at SOS_SCHEDULER_YIELD.

Thanks,

Jared

Jared
CE - Microsoft