February 17, 2022 at 3:51 pm
I have a sql2016 (Std) VM box with 16gig of memory when I run the posted query I see this wait type, and wondering how to research
more what I'm seeing and how to fix. The queries are select statements in the "executing SQL".
Sql is using around 9gig and 11gig is allocated of the 16gig total. I haven't set any min/max values for SQL to adhere to, as this box is a dedicated SQL instance.
Thanks.
SELECT r.start_time [Start Time],session_ID [SPID],
DB_NAME(database_id) [Database],
SUBSTRING(t.text,(r.statement_start_offset/2)+1,
CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
END) [Executing SQL],
Status,command,wait_type,wait_time,wait_resource,
last_wait_type
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id != @@SPID -- don't show this query
AND session_id > 50 -- don't show system queries
ORDER BY r.start_time
February 17, 2022 at 5:17 pm
I would start here
https://www.sqlskills.com/help/waits/memory_allocation_ext/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 17, 2022 at 5:35 pm
I looked at that:
Description:
This wait type is when a thread is switching to preemptive mode while allocating memory. The preemptive switch is so that the code being executed does not have to check for quantum exhaustion.
So is it just informative or some action needed?
Thx.
February 17, 2022 at 5:47 pm
What are you trying to determine? Is there an issue you are trying to solve? Or is this simply a learning exercise?
Your query only shows the last wait type for a given SPID. Which may be meaningless.
If you are attempting to diagnose a performance issue, an analysis of the percentage of each wait type would likely be a better place to start.
Glenn Berry has published an extensive set of queries for the various SQL versions here: https://sqlserverperformance.wordpress.com/
From those queries, this code will show you the percentages for each wait type. Be aware that wait statistics are cleared with each restart of SQL. So, if your server was recently restarted, you may see some skewed statistics.
-- Isolate top waits for server instance since last restart or wait statistics clear (Query 40) (Top Waits)
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 WITH (NOLOCK)
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',
N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT',
N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE',
N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE',
N'PARALLEL_REDO_DRAIN_WORKER', N'PARALLEL_REDO_LOG_CACHE', N'PARALLEL_REDO_TRAN_LIST',
N'PARALLEL_REDO_WORKER_SYNC', N'PARALLEL_REDO_WORKER_WAIT_WORK',
N'PREEMPTIVE_HADR_LEASE_MECHANISM', N'PREEMPTIVE_SP_SERVER_DIAGNOSTICS',
N'PREEMPTIVE_OS_LIBRARYOPS', N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_CRYPTOPS',
N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_AUTHENTICATIONOPS',
N'PREEMPTIVE_OS_GENERICOPS', N'PREEMPTIVE_OS_VERIFYTRUST',
N'PREEMPTIVE_OS_FILEOPS', N'PREEMPTIVE_OS_DEVICEOPS', N'PREEMPTIVE_OS_QUERYREGISTRY',
N'PREEMPTIVE_OS_WRITEFILE',
N'PREEMPTIVE_XE_CALLBACKEXECUTE', N'PREEMPTIVE_XE_DISPATCHER',
N'PREEMPTIVE_XE_GETTARGETSTATE', N'PREEMPTIVE_XE_SESSIONCOMMIT',
N'PREEMPTIVE_XE_TARGETINIT', N'PREEMPTIVE_XE_TARGETFINALIZE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_ASYNC_QUEUE',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',
N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP',
N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',
N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'WAIT_XTP_RECOVERY',
N'XE_BUFFERMGR_ALLPROCESSED_EVENT', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_LIVE_TARGET_TVF', N'XE_TIMER_EVENT')
AND waiting_tasks_count > 0)
SELECT
MAX (W1.wait_type) AS [WaitType],
CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec],
CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],
CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],
CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],
MAX (W1.WaitCount) AS [Wait Count],
CAST (N'https://www.sqlskills.com/help/waits/' + W1.wait_type AS XML) AS [Help/Info URL]
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type
HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold
OPTION (RECOMPILE);
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 17, 2022 at 6:43 pm
I'm trying to determine that wait type is something that is hurting SQL process performance .
February 17, 2022 at 7:10 pm
I'm trying to determine that wait type is something that is hurting SQL process performance .
If you are concerned that the MEMORY_ALLOCATION_EXT wait type is hurting your performance, then the query you are using will not tell you that.
Does that wait appear in the top 10 of the query I provided? If not, I would probably pay attention to other things first.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 17, 2022 at 8:09 pm
Thanks for that clarifications on the query I was using....
It's not a wait in the top 10.
#1 is SOS_SCHEDULER_YIELD 55.86
February 17, 2022 at 8:22 pm
Thanks for that clarifications on the query I was using....
It's not a wait in the top 10.
#1 is SOS_SCHEDULER_YIELD 55.86
SOS_SCHEDULER_YIELD is not one I would usually be particularly concerned about. Since it's #1, I might be.
I guess my question is have you done an index analysis? Have you reviewed the worst performing code? Have you determined if your statistics need to be updated?
Generically, SOS_SCHEDULER_YIELD points to scans of pages in memory.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 17, 2022 at 8:26 pm
The code is vendor code looks like Inline sql not stored procedure from my analysis, and Indexes seem inline>
Thx.
February 17, 2022 at 8:32 pm
The code is vendor code looks like Inline sql not stored procedure from my analysis, and Indexes seem inline>
Thx.
Indexes can't be "inline". I would suggest using Brent Ozar's tools as a starting point for your analysis.
https://www.brentozar.com/first-aid/
There is detailed instructions on their usage, and plenty of information as to what to do.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 17, 2022 at 8:36 pm
I'm trying to determine that wait type is something that is hurting SQL process performance .
Can you provide more details? Is the system slow? What specifically is going on?
If this is a third-party app there may be little you can do without violating a license agreement. Your only recourse may be to throw more hardware at it.
Is support from the vendor available? Do they have solutions?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 18, 2022 at 9:46 am
Even if it is a dedicated SQL box, I would configure sql server max memory to ensure there is some ram left for OS , Antivirus, ...
February 19, 2022 at 10:41 pm
Thanks for replies and suggestions... after some more research one table was indexing almost all fields. and was updating those during process. Changed table def and that seem to fix the issues.
Thanks.
December 20, 2023 at 2:45 pm
This was removed by the editor as SPAM
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply