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

 

 

TechEd2011 Demos.sql

Comments

• Anonymous
  January 01, 2003
  É verdade.. Obrigada pelo comentário André! :)

• Anonymous
  January 01, 2003
  Oi Leonardo! O slide estará disponível em breve no site do TechEd. Quando estiver disponível lá, avisarei vcs com o link para download, ok? Obrigada

• Anonymous
  October 04, 2011
  Olá Renata, tudo bem? Você teria o slide também?

• Anonymous
  October 06, 2011
  Parabens a você Renata e ao Gentile! Realmente as dmvs merecem muito mais atenção pois fazem toda a diferença na hora de um troubleshooting! Pena que neste TechED nao pude comparecer =S Abraço!

• Anonymous
  April 23, 2012
  Pessoal, Este post ficou ótimo, eu que sou novato na ferramenta SQL o entendimento foi de 100%... Gentile e Renata parabéns pelo ótimo trabalho...