Mssql de server performansına bakmak için aşağıdaki script çalıştırmak yeterli olur.
SELECT SPID = er.session_id,
STATUS = ses.status,
[Login] = ses.login_name,
Host = ses.host_name,
BlkBy = er.blocking_session_id,
DBName = DB_NAME(er.database_id),
CommandType = er.command,
SQLStatement = SUBSTRING(
qt.text,
er.statement_start_offset / 2,
(
CASE
WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END – er.statement_start_offset
) / 2
),
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid, dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid),
ClientAddress = con.client_net_address,
ElapsedMin = (er.total_elapsed_time / (60 * 1000)),
ElapsedSec = er.total_elapsed_time / 1000,
ElapsedMS = er.total_elapsed_time,
CPUTime = er.cpu_time,
IOReads = er.logical_reads + er.reads,
IOWrites = er.writes,
LastWaitType = er.last_wait_type,
StartTime = er.start_time,
Protocol = con.net_transport,
transaction_isolation = CASE ses.transaction_isolation_level
WHEN 0 THEN ‘Unspecified’
WHEN 1 THEN ‘Read Uncommitted’
WHEN 2 THEN ‘Read Committed’
WHEN 3 THEN ‘Repeatable’
WHEN 4 THEN ‘Serializable’
WHEN 5 THEN ‘Snapshot’
END,
ConnectionWrites = con.num_writes,
ConnectionReads = con.num_reads,
AUTHENTICATION = con.auth_scheme
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE er.session_id > 50
ORDER BY
–er.blocking_session_id DESC,
er.session_id
