SQL QUERY To Identify CPU Use for Query Executing

Hello Friends,

I want share some of my favorite SQL Query to identify SQL Per-formation.

-- Requests by CPU consumption.

SELECT der.session_id ,

DB_NAME(der.database_id) AS database_name ,

deqp.query_plan ,

SUBSTRING(dest.text, der.statement_start_offset / 2,

( CASE WHEN der.statement_end_offset = -1

THEN DATALENGTH(dest.text)

ELSE der.statement_end_offset

END - der.statement_start_offset ) / 2)

AS [statement executing] ,

der.cpu_time

--der.granted_query_memory

--der.wait_time

--der.total_elapsed_time

--der.reads

FROM sys.dm_exec_requests der

INNER JOIN sys.dm_exec_sessions des

ON des.session_id = der.session_id

CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest

CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp

WHERE des.is_user_process = 1

AND der.session_id <> @@spid

ORDER BY der.cpu_time DESC ;

-----------------------------------------------------------------------------------------------------------------------

SELECT TOP 50

SUBSTRING(eqt.TEXT,(

eqs.statement_start_offset/2) +1

,((CASE eqs.statement_end_offset

WHEN -1 THEN DATALENGTH(eqt.TEXT)

ELSE eqs.statement_end_offset

END - eqs.statement_start_offset)/2)+1)

,eqs.execution_count

,eqs.total_logical_reads

,eqs.last_logical_reads

,eqs.total_logical_writes

,eqs.last_logical_writes

,eqs.total_worker_time

,eqs.last_worker_time

,eqs.total_elapsed_time/1000000 AS Total_elapsed_time_Secs

,eqs.last_elapsed_time/1000000 AS Last_elapsed_time_Secs

,eqs.last_execution_time

,eqp.query_plan

FROM sys.dm_exec_query_stats eqs

CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) eqt

CROSS APPLY sys.dm_exec_query_plan(eqs.plan_handle) eqp

ORDER BY eqs.total_worker_time DESC

SELECT TOP 50

SUBSTRING(eqt.TEXT,(

eqs.statement_start_offset/2) +1

,((CASE eqs.statement_end_offset

WHEN -1 THEN DATALENGTH(eqt.TEXT)

ELSE eqs.statement_end_offset

END - eqs.statement_start_offset)/2)+1)

,eqs.execution_count

,eqs.total_logical_reads

,eqs.last_logical_reads

,eqs.total_logical_writes

,eqs.last_logical_writes

,eqs.total_worker_time

,eqs.last_worker_time

,eqs.total_elapsed_time/1000000 AS Total_elapsed_time_Secs

,eqs.last_elapsed_time/1000000 AS Last_elapsed_time_Secs

,eqs.last_execution_time

,eqp.query_plan

FROM sys.dm_exec_query_stats eqs

CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) eqt

CROSS APPLY sys.dm_exec_query_plan(eqs.plan_handle) eqp

ORDER BY eqs.total_worker_time DESC

--------------------------------------------------------------------------------------------------------------------------

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 (N'CLR_SEMAPHORE',N'LAZYWRITER_SLEEP',N'RESOURCE_QUEUE',N'SQLTRACE_BUFFER_FLUSH',N'SLEEP_TASK',N'SLEEP_SYSTEMTASK',

N'WAITFOR',N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',N'XE_TIMER_EVENT',N'XE_DISPATCHER_JOIN',

N'LOGMGR_QUEUE',N'FT_IFTS_SCHEDULER_IDLE_WAIT','BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',N'CLR_AUTO_EVENT',N'DISPATCHER_QUEUE_SEMAPHORE',

N'TRACEWRITE',N'XE_DISPATCHER_WAIT',N'BROKER_TO_FLUSH',N'BROKER_EVENTHANDLER',N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',

N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP'))

SELECT [W1].[wait_type] AS [Wait_Type],[W1].[WaitCount] AS [Wait_Count],CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Wait_Time_%],

CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_Time_Sec],CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_wait_Sec],

CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_wait_Sec],GETDATE() AS [Current_Date]

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] < 95

order by Wait_Count desc

--------------------------------------------------------------------------------------------------------------------------

--Transaction log impact of active transactions.

SELECT DTST.[session_id],

DES.[login_name] AS [Login Name],

DB_NAME (DTDT.database_id) AS [Database],

DTDT.[database_transaction_begin_time] AS [Begin Time],

-- DATEDIFF(ms,DTDT.[database_transaction_begin_time], GETDATE()) AS [Duration ms],

CASE DTAT.transaction_type

WHEN 1 THEN 'Read/write'

WHEN 2 THEN 'Read-only'

WHEN 3 THEN 'System'

WHEN 4 THEN 'Distributed'

END AS [Transaction Type],

CASE DTAT.transaction_state

WHEN 0 THEN 'Not fully initialized'

WHEN 1 THEN 'Initialized, not started'

WHEN 2 THEN 'Active'

WHEN 3 THEN 'Ended'

WHEN 4 THEN 'Commit initiated'

WHEN 5 THEN 'Prepared, awaiting resolution'

WHEN 6 THEN 'Committed'

WHEN 7 THEN 'Rolling back'

WHEN 8 THEN 'Rolled back'

END AS [Transaction State],

DTDT.[database_transaction_log_record_count] AS [Log Records],

DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used],

DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd],

DEST.[text] AS [Last Transaction Text],

DEQP.[query_plan] AS [Last Query Plan]

FROM sys.dm_tran_database_transactions DTDT

INNER JOIN sys.dm_tran_session_transactions DTST

ON DTST.[transaction_id] = DTDT.[transaction_id]

INNER JOIN sys.[dm_tran_active_transactions] DTAT

ON DTST.[transaction_id] = DTAT.[transaction_id]

INNER JOIN sys.[dm_exec_sessions] DES

ON DES.[session_id] = DTST.[session_id]

INNER JOIN sys.dm_exec_connections DEC

ON DEC.[session_id] = DTST.[session_id]

LEFT JOIN sys.dm_exec_requests DER

ON DER.[session_id] = DTST.[session_id]

CROSS APPLY sys.dm_exec_sql_text (DEC.[most_recent_sql_handle]) AS DEST

OUTER APPLY sys.dm_exec_query_plan (DER.[plan_handle]) AS DEQP

ORDER BY DTDT.[database_transaction_log_bytes_used] DESC;

-- ORDER BY [Duration ms] DESC;

-------------------------------------------------------------------------------------------------------------------------

--Tempdb usage

WITH version_store ( [rowset_id], [bytes consumed] )

AS ( SELECT TOP 1

[rowset_id] ,

SUM([record_length_first_part_in_bytes]

+ [record_length_second_part_in_bytes])

AS [bytes consumed]

FROM sys.dm_tran_version_store

GROUP BY [rowset_id]

ORDER BY SUM([record_length_first_part_in_bytes]

+ [record_length_second_part_in_bytes])

)

SELECT VS.[rowset_id] ,

VS.[bytes consumed] ,

DB_NAME(DTVS.[database_id]) AS [database name] ,

DTASDT.[session_id] AS session_id ,

DES.[login_name] AS [session login] ,

DEST.text AS [session command]

FROM version_store VS

INNER JOIN sys.[dm_tran_version_store] DTVS

ON VS.rowset_id = DTVS.[rowset_id]

INNER JOIN sys.[dm_tran_active_snapshot_database_transactions]

DTASDT

ON DTVS.[transaction_sequence_num] =

DTASDT.[transaction_sequence_num]

INNER JOIN sys.dm_exec_connections DEC

ON DTASDT.[session_id] = DEC.[most_recent_session_id]

INNER JOIN sys.[dm_exec_sessions] DES

ON DEC.[most_recent_session_id] = DES.[session_id]

CROSS APPLY sys.[dm_exec_sql_text](DEC.[most_recent_sql_handle])

DEST ;

------------------------------------------------------------------

Copy Data from One Table to Another Table on same SQL Server Instance

INSERT INTO [NAVDB_2013R2].[dbo].[ComapnyName_ India$Purch_ Comment Line]
([Document Type]
,[No_]
,[Document Line No_]
,[Line No_]
,[Date]
,[Code]
,[Comment])
(SELECT
[Document Type]
,[No_]
,[Document Line No_]
,[Line No_]
,[Date]
,[Code]
,[Comment]

FROM [NAVDB_2013].[dbo].[ComapnyName_$Purch_ Comment Line])

Nilesh Gajjar - Business Central & NAV

Pages

Sunday, June 11, 2017

SQL QUERY To Identify CPU Use for Query Executing

2 comments: