Exibir código-fonte para Scripts para teste de desempenho

 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>