SQL Server DMV ve DMF – 2

Bu yazımızda DMV ve DMF serimizin 2.sine devam edeceğiz. Yazımızda sql server query execution sonuçlarını montior etme ve bunları nasıl anlamlandırabileceğimize bakacağız. Yazı sonunda hazır olarak çoğumuzun kullandığı bu DMV ve DMF’lerin nerede ve ne için kullanıldığını öğrenmiş olacağız. Bu seride Connection, Session ve Request bölümlerini inceleyeceğiz. Task ve Worker bölümleri operating system ile alakalı olduğundan dolayı bir sonraki bölümde onları ele alacağız.

SQL Server bir session’ın process’i aşağıdaki gibi gerçekleşmektedir.

Görsel – 1

Bu süreci detaylandırmak istersek adım adım aşağıda bunları ele alabiliriz.

Sql Server’da connection yapıldıktan sonra unique olarak kendine özel bir session alır.

Görsel – 2

session_id: Session’ın Id’si

connect_time: Connection’ın gerçekleştirildiği tarih

net_transport: SQL Server’a gerçekleştirilen bağlantı türü

auth_scheme: Gerçekleştirilen bağlantının tipi

client_net_address: Bağlantı gerçekleştiren Session’ın IP bilgisi

num_reads: Session’ın yapmış olduğu okuma sayısı

num_writes: Yapmış olduğu yazma sayısı

last_read: Yapılan son okumanın tarihi

last_write: Yapılan son yazmanın tarihi

 

Görsel – 3

ve Sql Server bu session’u tanımak ve takip etmek için bir değer atar biz buna Session Process Id ya da SPID deriz. Query ekranında hangi session’ı aldığımızı görmek istersek;

select @@SPID

komutu ile

 

Görsel – 4

Ya da query ekranından rahatlıkla görebiliriz.

Görsel – 5

SessionId alan connection’umuz yeni bir istekte bulunacaktır. İstekte bulunan session’ların bilgisini aşağıdaki komut yardımı ile öğrenebiliriz.

SELECT session_id, login_time, status

FROM sys.dm_exec_sessions

WHERE host_name IS NULL;

 

Görsel – 6

Connection alan bu sessionId’leri ortak bu dmv’leri joinleyerek bir ekranda sonuca ulaşabilirim.

SELECT DES.session_id,DES.login_name, DB_NAME(DES.database_id) AS DB,

num_reads, num_writes, last_read, last_write status,DES.program_name

FROM sys.dm_exec_sessions AS DES

JOIN sys.dm_exec_connections AS DEC

ON DEC.session_id = DES.session_id;

Görsel – 7

 

Connection gerçekleştirilen sessionlar, sql server üzerinde ihtiyaçları doğrultusunda isteklerde bulunurlar biz bunlara Request diye adlandırırız.

Bu requestleri sys.dm_exec_requests DMV’si ile görebiliriz. Peki bana sys.dm_exec_requests ile neleri görebilirim.

1-) Uzun çalışan sorgularım varsa bunları görebilirim ve bu sorguları görebilmem için sys.dm_exec_sql_text DMF’si ile cross ettiğimde bana sorguya ait text bilgisine ulaşmamı sağlayacaktır.

SELECT session_id, start_time, status, command, total_elapsed_time, text

FROM sys.dm_exec_requests

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

WHERE status = 'running'

ORDER BY total_elapsed_time DESC;

Görsel – 8

 

2-) Wait types ve Wait Times (Bekleme Tipi ve türlerini) bulmama ve analiz etmeme, Bloklayan session ve sorguları bulmama yardımcı olur.

 

SELECT session_id, wait_type, wait_time, blocking_session_id, text

FROM sys.dm_exec_requests

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

WHERE wait_type IS NOT NULL

ORDER BY wait_time DESC;

Görsel – 9

3-) Sorgu bazlı kaynak kullanımını öğrenmemde yardımcı olur.

 

SELECT session_id, command, cpu_time, reads, writes, logical_reads, text

FROM sys.dm_exec_requests

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

ORDER BY cpu_time DESC, reads DESC, logical_reads DESC;

Görsel – 10

 

4-) Komut ve Sorguların planlarını öğrenmeme yardımcı olacaktır. Burada sys.dm_exec_query_plan DMF’si ile CROSS yaparak elde edebilirim.

 

SELECT r.session_id, r.status, r.command, q.query_plan

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) q

Görsel – 11

5-) Başlatmış olduğum işlemlerin ne kadar sürede biteceğini görebilirim.

• ALTER INDEX REORGANIZE
• AUTO_SHRINK option with ALTER DATABASE
• BACKUP DATABASE
• CREATE INDEX
• DBCC CHECKDB
• DBCC CHECKFILEGROUP
• DBCC CHECKTABLE
• DBCC INDEXDEFRAG
• DBCC SHRINKDATABASE
• DBCC SHRINKFILE
• KILL (Transact-SQL)
• RESTORE DATABASE,
• UPDATE STATISTICS

 

SELECT session_id,

start_time,

percent_complete,

estimated_completion_time,

estimated_completion_time/(1000*60) AS EstimatedCompletionTimeInMinutes

FROM sys.dm_exec_requests

WHERE command ='RESTORE DATABASE'-- buraya yukarıdaki başlattığım işlemi command olarak girdiğimde onunla ilgili sonuca ulaşabilirim.

ORDER BY estimated_completion_time

 

SQL Server execution, DMV ve DMF’leri genel olarak birbiri ilen olan ilişkilerini incelediğimizde bize detaylı olarak bilgi verecektir. Son olarak birlikte olan ilişkilerini görmek ve incelemek istersek eğer bize aşağıdaki sorgu yardımcı olacaktır.

 

 

select

DB_NAME(DES.database_id) AS DB,

DEC.session_id,DEC.num_reads,DEC.num_writes,DEC.client_net_address ,DEC.connect_time,DEST.text,

DES.database_id,DES.cpu_time,DES.logical_reads,DES.login_name,

DER.blocking_session_id,DER.command, DER.percent_complete,DER.plan_handle, DES.open_transaction_count

from sys.dm_exec_connections DEC

Cross apply sys.dm_exec_sql_text(DEC.most_recent_sql_handle) DEST

join sys.dm_exec_sessions DES on DEC.session_id = DES.session_id

join sys.dm_exec_requests DER on der.session_id = DES.session_id