sql performance active transaction

SELECT
    trans.session_id AS [SESSION ID],
    ESes.host_name AS [HOST NAME],login_name AS [Login NAME],
    trans.transaction_id AS [TRANSACTION ID],
    tas.name AS [TRANSACTION NAME],tas.transaction_begin_time AS [TRANSACTION BEGIN TIME],
    tds.database_id AS [DATABASE ID],DBs.name AS [DATABASE NAME]
FROM sys.dm_tran_active_transactions tas
JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
LEFT OUTER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
LEFT OUTER JOIN sys.databases AS DBs
ON tds.database_id = DBs.database_id
LEFT OUTER JOIN sys.dm_exec_sessions AS ESes
ON trans.session_id = ESes.session_id
WHERE ESes.session_id IS NOT NULL

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

SELECT * 
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

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

Are the statistics current?

If your database statistics are not up to date, it will cause the query optimizer to not have the right information to build the best query plan. For example, the optimizer could opt for a parallel plan when a nonparallel plan was the best option making you think that you have something wrong with the max degree of parallelism because you see lots of SOS_SCHEDULER_YIELD or CXPACKET waiting tasks. If this is the case you should update the statistics right away and then execute the DBCC FREEPROCCACHE command to clear the procedure cache so new plans can be built using the updated statistics.

EXEC sp_updatestats
GO
DBCC FREEPROCCACHE() 
GO

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

You can also use the DMVs to get information about blocking.

SELECT session_id, command, blocking_session_id, wait_type, wait_time, wait_resource, t.TEXT
FROM sys.dm_exec_requests 
CROSS apply sys.dm_exec_sql_text(sql_handle) AS t
WHERE session_id > 50 
AND blocking_session_id > 0
UNION
SELECT session_id, '', '', '', '', '', t.TEXT
FROM sys.dm_exec_connections 
CROSS apply sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE session_id IN (SELECT blocking_session_id 
                    FROM sys.dm_exec_requests 
                    WHERE blocking_session_id > 0)

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

Dynamic Management Views

There are some useful Dynamic Management Views (DMVs) to check CPU bottlenecks. The sys.dm_exec_query_stats DMV shows you the currently cached batches or procedures which are using the CPU. The following query can be used to check the CPU consumption per plan_handle.

select plan_handle,
      sum(total_worker_time) as total_worker_time, 
      sum(execution_count) as total_execution_count,
      count(*) as  number_of_statements 
from sys.dm_exec_query_stats
group by plan_handle
order by sum(total_worker_time), sum(execution_count) desc

-----------------------------------------------USE SQLSentry;
GO

;WITH src AS
(
SELECT
[Object] = o.name,
[Type] = o.type_desc,
[Index] = COALESCE(i.name, ''),
[Index_Type] = i.type_desc,
p.[object_id],
p.index_id,
au.allocation_unit_id
FROM
sys.partitions AS p
INNER JOIN
sys.allocation_units AS au
ON p.hobt_id = au.container_id
INNER JOIN
sys.objects AS o
ON p.[object_id] = o.[object_id]
INNER JOIN
sys.indexes AS i
ON o.[object_id] = i.[object_id]
AND p.index_id = i.index_id
WHERE
au.[type] IN (1,2,3)
AND o.is_ms_shipped = 0
)
SELECT
src.[Object],
src.[Type],
src.[Index],
src.Index_Type,
buffer_pages = COUNT_BIG(b.page_id),
buffer_mb = COUNT_BIG(b.page_id) / 128
FROM
src
INNER JOIN
sys.dm_os_buffer_descriptors AS b
ON src.allocation_unit_id = b.allocation_unit_id
WHERE
b.database_id = DB_ID()
GROUP BY
src.[Object],
src.[Type],
src.[Index],
src.Index_Type
ORDER BY
buffer_pages DESC;
=========================================================

-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters 
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';

;WITH src AS
(
SELECT 
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767 
THEN 'Resource DB' 
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3), 
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC; 
=============================================================
SELECT 
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, 
    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB, 
    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB, 
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, 
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count AS sql_page_fault_count,
    memory_utilization_percentage AS sql_memory_utilization_percentage, 
    process_physical_memory_low AS sql_process_physical_memory_low, 
    process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;
============================================================

SELECT * FROM SYS.SYSPERFINFO WHERE
OBJECT_NAME='SQLSERVER:BUFFER MANAGER' AND
(COUNTER_NAME='TARGET PAGES' OR
COUNTER_NAME='TOTAL PAGES' OR
COUNTER_NAME='DATABASE PAGES' OR
COUNTER_NAME='STOLEN PAGES' OR
COUNTER_NAME='FREE PAGES')

================================================
Use the following DMV query to determine which SQL Server components are consuming the most amount of memory, and observe how this changes over time:

SELECT TYPE, SUM(MULTI_PAGES_KB) FROM
SYS.DM_OS_MEMORY_CLERKS WHERE
MULTI_PAGES_KB != 0 GROUP BY TYPE

================================================
The sample output shows that the total memory allocated is 18 MB system-level memory consumption and 1358MB allocated to database id of 5. 
Since this database is mapped to a dedicated resource pool, this memory is accounted for in that resource pool.

Sample Output
SELECT type  
     , name  
     , memory_node_id  
     , pages_kb/1024 AS pages_MB   
   FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%' 

================================================

This query will show which SQL Server objects are consuming memory:

SELECT TYPE, PAGES_ALLOCATED_COUNT FROM
SYS.DM_OS_MEMORY_OBJECTS WHERE
PAGE_ALLOCATOR_ADDRESS IN (SELECT TOP 10
PAGE_ALLOCATOR_ADDRESS FROM
SYS.DM_OS_MEMORY_CLERKS ORDER BY
MULTI_PAGES_KB DESC) ORDER BY
PAGES_ALLOCATED_COUNT DESC
=================================================
To get an idea of which individual processes are taking up memory, use the following query:

SELECT TOP 10 SESSION_ID, LOGIN_TIME, HOST_NAME,
PROGRAM_NAME, LOGIN_NAME, NT_DOMAIN, 
NT_USER_NAME, STATUS, CPU_TIME, MEMORY_USAGE, 
TOTAL_SCHEDULED_TIME, TOTAL_ELAPSED_TIME, 
LAST_REQUEST_START_TIME,
LAST_REQUEST_END_TIME, READS, WRITES, 
LOGICAL_READS, TRANSACTION_ISOLATION_LEVEL, 
LOCK_TIMEOUT, DEADLOCK_PRIORITY, ROW_COUNT, 
PREV_ERROR FROM SYS.DM_EXEC_SESSIONS ORDER
BY MEMORY_USAGE DESC
=============================================
Disk:
Processes that are disk intensive typically do not have the appropriate indexes or have poor execution plans. Here is a DMV query that lists the top 25 tables experiencing I/O waits.

SELECT TOP 25 DB_NAME(D.DATABASE_ID) AS
DATABASE_NAME, 
QUOTENAME(OBJECT_SCHEMA_NAME(D.OBJECT_ID, 
D.DATABASE_ID)) + N'.' +
QUOTENAME(OBJECT_NAME(D.OBJECT_ID,
D.DATABASE_ID)) AS OBJECT_NAME, D.DATABASE_ID, 
D.OBJECT_ID, D.PAGE_IO_LATCH_WAIT_COUNT,
D.PAGE_IO_LATCH_WAIT_IN_MS, D.RANGE_SCANS,
D.INDEX_LOOKUPS FROM (SELECT DATABASE_ID, 
OBJECT_ID, ROW_NUMBER() OVER (PARTITION BY
DATABASE_ID ORDER BY
SUM(PAGE_IO_LATCH_WAIT_IN_MS) DESC) AS
ROW_NUMBER, SUM(PAGE_IO_LATCH_WAIT_COUNT) AS
PAGE_IO_LATCH_WAIT_COUNT, 
SUM(PAGE_IO_LATCH_WAIT_IN_MS) AS
PAGE_IO_LATCH_WAIT_IN_MS, 
SUM(RANGE_SCAN_COUNT) AS RANGE_SCANS, 
SUM(SINGLETON_LOOKUP_COUNT) AS
INDEX_LOOKUPS FROM
SYS.DM_DB_INDEX_OPERATIONAL_STATS(NULL, NULL, 
NULL, NULL) WHERE PAGE_IO_LATCH_WAIT_COUNT > 0
GROUP BY DATABASE_ID, OBJECT_ID ) AS D LEFT JOIN
(SELECT DISTINCT DATABASE_ID, OBJECT_ID FROM
SYS.DM_DB_MISSING_INDEX_DETAILS) AS MID ON
MID.DATABASE_ID = D.DATABASE_ID AND
MID.OBJECT_ID = D.OBJECT_ID WHERE
D.ROW_NUMBER>20 ORDER BY
PAGE_IO_LATCH_WAIT_COUNT DESC
=============================================
generate a list of columns that should have indexes on them:

SELECT * FROM SYS.DM_DB_MISSING_INDEX_GROUPS
G JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS GS
ON GS.GROUP_HANDLE = G.INDEX_GROUP_HANDLE
JOIN SYS.DM_DB_MISSING_INDEX_DETAILS D ON
G.INDEX_HANDLE = D.INDEX_HANDLE

==============================================
CPU. One of the most frequent contributors to high CPU consumption is stored procedure recompilation. 
Here is a DMV that displays the list of the top 25 recompilations:
SELECT TOP 25 SQL_TEXT.TEXT, SQL_HANDLE, 
PLAN_GENERATION_NUM, EXECUTION_COUNT, DBID, 
OBJECTID FROM SYS.DM_EXEC_QUERY_STATS A
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) 
AS SQL_TEXT WHERE PLAN_GENERATION_NUM >1
ORDER BY PLAN_GENERATION_NUM DESC
============================================
Top CPU consumers:

SELECT TOP 50 SUM(QS.TOTAL_WORKER_TIME) AS
TOTAL_CPU_TIME, SUM(QS.EXECUTION_COUNT) AS
TOTAL_EXECUTION_COUNT, COUNT(*) AS
NUMBER_OF_STATEMENTS, SQL_TEXT.TEXT, 
QS.PLAN_HANDLE FROM SYS.DM_EXEC_QUERY_STATS
QS CROSS APPLY
SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) AS SQL_TEXT
GROUP BY SQL_TEXT.TEXT,QS.PLAN_HANDLE ORDER
BY SUM(QS.TOTAL_WORKER_TIME) DESC
=============================================

look for memory bottlenecks, then disk and finally CPU

=============================================
When searching for bottlenecks, look for memory bottlenecks, then disk and finally CPU. Capture a baseline using System Monitor, 
SQL Profiler and DMVs to determine what is causing the bottleneck and if it can be solved by a hardware upgrade. Once you have a baseline,
 you are ready to start diagnosing the problem. In most cases, the solution will involve query tuning, query rewrites or re-architecting your solution. 
Many times, throwing hardware at the problem will not have the performance gains of simple index placement.
=================================================
In our database, run a query that we have created to find indexes that we have created but are not being used.

SELECT TOP 25
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.'
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id 
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO


=============================================================

update statistics:
To find statistics on database level:

USE AdventureWorks2012;
GO
DBCC SHOW_STATISTICS ('Sales.Currency', AK_Currency_Name) WITH STAT_HEADER
GO

=====
to find on object level:

USE AdventureWorks2012;
GO

SELECT OBJECT_NAME(object_id) AS [Objects], 
MAX(STATS_DATE([object_id], [stats_id])) AS [StatisticsUpdatedOn] 
FROM sys.stats GROUP BY OBJECT_NAME(object_id) 
HAVING OBJECT_NAME(object_id) NOT LIKE 'sys%' 
ORDER BY [StatisticsUpdatedOn] DESC


==========
to find the app quries 

SELECT
session_id,status,
command,sql_handle,database_id
,(SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS query_text 
FROM sys.dm_exec_requests r
WHERE session_id >= 51


select s.session_id, s.login_name, s.host_name, s.status,
s.program_name, s.cpu_time, s.last_request_start_time,
(SELECT text FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) AS query_text 
from sys.dm_exec_sessions s, sys.dm_exec_connections c
where s.session_id = c.session_id and
s.session_id > 50 
order by s.last_request_start_time desc

===========================

to find wait stats:

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

DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR)

----