TechEd 2011 Abandonando a sysprocesses
Olá!
Boa tarde! Gostaria primeiro de agradecer a todos que puderam assistir a palestra que Gentile e eu demos sobre DMVs no TechEd 
.
Conforme prometido, segue nesse post as demonstrações efetuadas sobre a utilização de algumas das DMVs e DMFs existentes a partir do SQL Server 2005. Lembrando que algumas delas, cf mencionamos, estão disponíveis somente a partir do SQL Server 2008 R2 Service Pack 1. É isso ae, esperam que tenham curtido a palestra e as demos que disponibilizamos.
Obrigada
-- usando a sys.dm_exec_sessions para visualizar a carga da instância
select * from sys.dm_exec_sessions
-- sessões de sistema (kpid <= 50)
select * from sys.dm_exec_sessions where is_user_process <> 1
-- sessões de usuário (kpid > 50)
select * from sys.dm_exec_sessions where is_user_process = 1
-- informações sobre a origem das sessões de usuário
select session_id
, login_time
, login_name
, original_login_name
, nt_user_name
, nt_domain
, host_name
, program_name
from sys.dm_exec_sessions
where is_user_process = 1
-- opções nas sessões de usuário
select session_id
, ansi_defaults
, ansi_null_dflt_on
, ansi_nulls
, ansi_padding
, ansi_warnings
, arithabort
, deadlock_priority
, lock_timeout
, transaction_isolation_level
from sys.dm_exec_sessions where is_user_process = 1
-- informações sobre o consumo de recursos das sessões de usuário
select session_id
, cpu_time
, reads
, logical_reads
, writes
, memory_usage
, row_count
from sys.dm_exec_sessions where is_user_process = 1
-- mapeando as sessões de usuário para conexões de rede
select es.session_id
, ec.connect_time
, ec.net_transport
, ec.num_reads
, ec.num_writes
, ec.last_read
, ec.last_write
, ec.net_packet_size
, ec.client_net_address
, ec.client_tcp_port
, ec.local_net_address
, ec.local_tcp_port
from sys.dm_exec_sessions es join sys.dm_exec_connections ec on es.session_id = ec.session_id
where es.is_user_process = 1
-- mapeando as sessões de usuário para as requisições ativas
select es.session_id
, er.request_id
, er.start_time
, er.status
, er.command
, er.database_id
, er.user_id
, er.executing_managed_code
from sys.dm_exec_sessions es join sys.dm_exec_requests er on es.session_id = er.session_id
where es.is_user_process = 1
-- opções nas requisições ativas
select es.session_id
, er.ansi_defaults
, er.ansi_null_dflt_on
, er.ansi_nulls
, er.ansi_padding
, er.ansi_warnings
, er.arithabort
, er.deadlock_priority
, er.transaction_isolation_level
, er.lock_timeout
from sys.dm_exec_sessions es join sys.dm_exec_requests er on es.session_id = er.session_id
where es.is_user_process = 1
-- informações sobre o consumo de recursos das requisições ativas
select es.session_id
, er.cpu_time
, er.reads
, er.logical_reads
, er.writes
, er.granted_query_memory
, er.row_count
, er.prev_error
, er.open_resultset_count
, er.open_transaction_count
, er.transaction_isolation_level
from sys.dm_exec_sessions es join sys.dm_exec_requests er on es.session_id = er.session_id
where es.is_user_process = 1
-- informações sobre requisições bloqueadas
select es.session_id
, er.request_id
, er.blocking_session_id
, er.command
, er.wait_type
, er.wait_resource
, er.wait_time
from sys.dm_exec_sessions es join sys.dm_exec_requests er on es.session_id = er.session_id
and es.is_user_process = 1 and er.blocking_session_id <> 0
-- informações sobre os comandos das requisições ativas
select es.session_id
, er.request_id
, er.command
, er.sql_handle
, er.plan_handle
, er.statement_start_offset
, er.statement_end_offset
, er.query_hash
, er.query_plan_hash
from sys.dm_exec_sessions es join sys.dm_exec_requests er on es.session_id = er.session_id
where es.is_user_process = 1
-- porcentagem completada dos DBCCs rodando na instalação
select es.session_id
, er.request_id
, er.start_time
, er.status
, er.command
, db_name (er.database_id) as 'db_name'
, er.percent_complete
from sys.dm_exec_sessions es join sys.dm_exec_requests er on es.session_id = er.session_id
where es.is_user_process = 1 and er.command like '%DBCC%'
-- informações sobre sessões sem requisições ativas e com transações pendentes
select es.*
from sys.dm_exec_sessions es
where exists (select * from sys.dm_tran_session_transactions t
where t.session_id = es.session_id)
and not exists (select * from sys.dm_exec_requests as r
where r.session_id = es.session_id)
-- capturando as queries e os planos de execução das requisições ativas
select es.session_id
, er.request_id
, er.command
, er.sql_handle
, st.text
, er.plan_handle
, qp.query_plan
, er.statement_start_offset
, er.statement_end_offset
, er.query_hash
, er.query_plan_hash
from sys.dm_exec_sessions es join sys.dm_exec_requests er on es.session_id = er.session_id
cross apply sys.dm_exec_sql_text (er.sql_handle) st
cross apply sys.dm_exec_query_plan (er.plan_handle) qp
where es.is_user_process = 1
-- capturando os comandos e os planos de execução das requisições ativas
select es.session_id
, er.request_id
, er.command
, er.sql_handle
, st.text
, substring (st.text, (er.statement_start_offset/2)+1,
((case er.statement_end_offset
when -1 then datalength (st.text)
else er.statement_end_offset
end - er.statement_start_offset)/2) + 1) as 'Comando'
, er.plan_handle
, qp.query_plan
, er.statement_start_offset
, er.statement_end_offset
, er.query_hash
, er.query_plan_hash
from sys.dm_exec_sessions es join sys.dm_exec_requests er on es.session_id = er.session_id
cross apply sys.dm_exec_sql_text (er.sql_handle) st
cross apply sys.dm_exec_query_plan (er.plan_handle) qp
where es.is_user_process = 1
-- novas colunas na sys.dm_exec_query_stats
select creation_time
, last_execution_time
, plan_generation_num
, execution_count
, total_rows
, last_rows
, max_rows
, min_rows
, substring (st.text, (qs.statement_start_offset/2)+1,
((case qs.statement_end_offset
when -1 then datalength (st.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2) + 1) 'Comando'
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
-- novas DMVs
-- informações sobre espaço livre nas partições usadas pela instância SQL
select * from sys.dm_os_volume_stats (6, 1)
select * from sys.dm_os_volume_stats (6, 2)
select * from sys.dm_os_volume_stats (7, 1)
select * from sys.dm_os_volume_stats (7, 2)
-- espaço livre nas partições usadas pelo banco de dados corrente
select database_id, f.file_id, total_bytes, available_bytes
from sys.database_files f cross apply sys.dm_os_volume_stats(DB_ID(), f.file_id)
-- informações sobre o sistema operacional
select * from sys.dm_os_windows_info
-- informações sobre as chaves de registry usadas pelas instância SQL
select * from sys.dm_server_registry
-- parâmetros de startup da instância SQL
select * from sys.dm_server_registry
where registry_key like 'HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters'
-- dumps gerados pela instância SQL
select * from sys.dm_server_memory_dumps
select @@SPID
dbcc stackdump (52)
-- serviços usados pela instância SQL
select * from sys.dm_server_services
-- observando onde existe contenção na instância
select *
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')
order by waiting_tasks_count desc
-- capturando as queries e os planos de execução das requisições ativas
select es.session_id
, er.request_id
, er.command
, er.sql_handle
, st.text
, er.plan_handle
, qp.query_plan
, er.statement_start_offset
, er.statement_end_offset
, er.query_hash
, er.query_plan_hash
from sys.dm_exec_sessions es join sys.dm_exec_requests er on es.session_id = er.session_id
cross apply sys.dm_exec_sql_text (er.sql_handle) st
cross apply sys.dm_exec_query_plan (er.plan_handle) qp
where es.is_user_process = 1
-- estatísticas de execução das queries com planos em cache, CPU
select creation_time
, last_execution_time
, execution_count
, plan_generation_num
, total_worker_time
, last_worker_time
, max_worker_time
, min_worker_time
, substring (st.text, (qs.statement_start_offset/2)+1,
((case qs.statement_end_offset
when -1 then datalength (st.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2) + 1) 'Comando'
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
where qs.sql_handle = 0x020000007D48FC13E49DCC58F2B9B99424B54C8FCCCB1DBF
-- estatísticas de execução das queries com planos em cache, leituras lógicas
select creation_time
, last_execution_time
, plan_generation_num
, execution_count
, total_logical_reads
, last_logical_reads
, max_logical_reads
, min_logical_reads
, substring (st.text, (qs.statement_start_offset/2)+1,
((case qs.statement_end_offset
when -1 then datalength (st.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2) + 1) 'Comando'
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
where qs.sql_handle = 0x020000007D48FC13E49DCC58F2B9B99424B54C8FCCCB1DBF
-- estatísticas de execução de stored procedures
select ps.database_id
, ps.object_id
, ps.sql_handle
, st.text
, ps.plan_handle
, qp.query_plan
from sys.dm_exec_procedure_stats ps
cross apply sys.dm_exec_sql_text(ps.sql_handle) st
cross apply sys.dm_exec_query_plan (ps.plan_handle) qp
where database_id not in (1, 2, 3, 4, 32767)
-- identificando os índices não encontrados pelo otimizador
select gs.group_handle, id.*
from sys.dm_db_missing_index_group_stats gs
inner join sys.dm_db_missing_index_groups ig on gs.group_handle = ig.index_group_handle
inner join sys.dm_db_missing_index_details id on ig.index_handle = id.index_handle
where database_id = 7