Andre.iguera: Criou página com ' Versão 1.0 de 06/12/2016 ==Queries== ===Queries mais demoradas=== SELECT --TOP 20 SUM(query_stats.total_worker_time) / SUM(...'

2016-12-06T20:35:59Z

Criou página com ' Versão 1.0 de 06/12/2016 ==Queries== ===Queries mais demoradas=== <syntaxhighlight lang="sql" line="1" > SELECT --TOP 20 SUM(query_stats.total_worker_time) / SUM(...'

Página nova

Versão 1.0 de 06/12/2016

==Queries==

===Queries mais demoradas===

<syntaxhighlight lang="sql" line="1" >
SELECT --TOP 20
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS [Avg CPU Time(us)],
SUBSTRING(CONVERT(CHAR(23), DATEADD(ms,(SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count))/1000,0), 121), 12,23) AS [Avg CPU Time(formatado)],
SUM(query_stats.execution_count) AS [Count Execucoes],
MAX(query_stats.last_execution_time) AS [Ultima Execucao],
MIN(creation_time) AS [Inicio Execucao],
MAX(max_elapsed_time) AS [Tempo Decorrido(us)],
SUBSTRING(CONVERT(CHAR(23), DATEADD(ms,(MAX(max_elapsed_time))/1000,0), 121), 12,23) AS [Tempo Decorrido(formatado)],
SUBSTRING(CONVERT(CHAR(23), DATEADD(ms,(MIN(query_stats.total_worker_time))/1000,0), 121), 12,23) AS [Minimo CPU Time],
SUBSTRING(CONVERT(CHAR(23), DATEADD(ms,(MAX(query_stats.total_worker_time))/1000,0), 121), 12,23) AS [Maximo CPU Time],
MIN(query_stats.databaseId) AS [DatabaseId],
MIN(query_stats.DBNAME) AS [Database],
MIN(query_stats.statement_text) AS [Instrucao Resumida],
MIN(query_stats.FullStatement_Text) AS [Instrucao Completa]
FROM
(SELECT
QS.*
, SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
, ST.text as FullStatement_Text
, COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value as int)),'Resource') AS DBNAME
, pa.value AS databaseId
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
WHERE
pa.attribute = 'dbid'
--informar uma data no formato aaaammdd (ano, mês e dia)
--and QS.last_execution_time >= '20160101'
--informar o nome da base de dados que deseja pesquisar
and COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value as int)),'Resource') = 'OnCorretor'
and (ST.text not like '%sys.%')
and (ST.text not like '%\[sys\].%' escape '\')
) as query_stats
GROUP BY query_stats.query_hash, query_stats.DBNAME
ORDER BY 1 DESC,4 ;
</syntaxhighlight>

===Querie kill===

<syntaxhighlight lang="sql" line="1" >
USE master
GO
declare @spid int
declare @db_name varchar(100)
set @db_name = 'OnCorretor' -- coloque o nome da base aqui
declare spid cursor for
select spid
from master.dbo.sysprocesses(nolock)
where dbid = db_id(@db_name) and spid > 50
union
select distinct request_session_id
from sys.dm_tran_locks (nolock)
where resource_database_id = db_id(@db_name) and request_session_id > 50
open spid
fetch next from spid into @spid
while @@fetch_status = 0
begin
exec ('kill ' + @spid)
fetch next from spid into @spid
end
</syntaxhighlight>

Scripts para teste de desempenho - Histórico de revisão

Andre.iguera: Criou página com ' Versão 1.0 de 06/12/2016 ==Queries== ===Queries mais demoradas=== SELECT --TOP 20 SUM(query_stats.total_worker_time) / SUM(...'