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