Scripts para teste de desempenho

Versão 1.0 de 06/12/2016

Queries

Queries mais demoradas

 1 SELECT --TOP 20 
 2        SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS [Avg CPU Time(us)],
 3        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)],
 4        SUM(query_stats.execution_count) AS [Count Execucoes],
 5        MAX(query_stats.last_execution_time) AS [Ultima Execucao],
 6        MIN(creation_time) AS [Inicio Execucao],
 7        MAX(max_elapsed_time) AS [Tempo Decorrido(us)],
 8        SUBSTRING(CONVERT(CHAR(23), DATEADD(ms,(MAX(max_elapsed_time))/1000,0), 121), 12,23) AS [Tempo Decorrido(formatado)],
 9        SUBSTRING(CONVERT(CHAR(23), DATEADD(ms,(MIN(query_stats.total_worker_time))/1000,0), 121), 12,23) AS [Minimo CPU Time],
10        SUBSTRING(CONVERT(CHAR(23), DATEADD(ms,(MAX(query_stats.total_worker_time))/1000,0), 121), 12,23) AS [Maximo CPU Time],
11        MIN(query_stats.databaseId) AS [DatabaseId],
12        MIN(query_stats.DBNAME) AS [Database],
13        MIN(query_stats.statement_text) AS [Instrucao Resumida], 
14        MIN(query_stats.FullStatement_Text) AS [Instrucao Completa]
15 FROM 
16        (SELECT 
17              QS.*
18              , SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
19                     ((CASE statement_end_offset
20                            WHEN -1 THEN DATALENGTH(st.text)
21                            ELSE QS.statement_end_offset END
22                            - QS.statement_start_offset)/2) + 1) AS statement_text 
23              , ST.text as FullStatement_Text         
24              , COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value as int)),'Resource') AS DBNAME
25              , pa.value AS databaseId 
26        FROM sys.dm_exec_query_stats AS QS
27        CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST 
28        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa 
29        WHERE 
30              pa.attribute = 'dbid' 
31              --informar uma data no formato aaaammdd (ano, mês e dia)
32              --and QS.last_execution_time >= '20160101' 
33              --informar o nome da base de dados que deseja pesquisar 
34              and COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value as int)),'Resource') = 'OnCorretor' 
35              and (ST.text not like '%sys.%') 
36              and (ST.text not like '%\[sys\].%' escape '\') 
37        ) as query_stats 
38 GROUP BY query_stats.query_hash, query_stats.DBNAME 
39 ORDER BY 1 DESC,4 ;

Querie kill

 1 USE master
 2 GO
 3 declare @spid int
 4 declare @db_name varchar(100)
 5 set @db_name = 'OnCorretor' -- coloque o nome da base aqui
 6 declare spid cursor for
 7 select spid
 8 from master.dbo.sysprocesses(nolock)
 9 where dbid = db_id(@db_name) and spid > 50
10 union
11 select distinct request_session_id
12 from sys.dm_tran_locks (nolock)
13 where resource_database_id = db_id(@db_name) and request_session_id > 50
14 open spid
15 fetch next from spid into @spid
16 while @@fetch_status = 0
17 begin
18 exec ('kill ' + @spid)
19 fetch next from spid into @spid
20 end