Перейти к содержимому

кэш

Как посмотреть кэш план

sELECT TOP 20
	qs.last_execution_time AS Last_execution_time,
	SUBSTRING(qt.text, 
				(qs.statement_start_offset/2) + 1, 
				((CASE qs.statement_end_offset 
						WHEN -1 THEN DATALENGTH(qt.text) 
						ELSE qs.statement_end_offset 
					END - qs.statement_start_offset)/2) + 1) AS Query_text, 
	qp.query_plan AS Query_plan,
	qs.execution_count AS Execution_count
FROM sys.dm_exec_query_stats AS qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
	CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
--WHERE 
WHERE
/* datepart(YEAR,qs.last_execution_time) BETWEEN 2018 AND 2018 and
		datepart(month,qs.last_execution_time) BETWEEN 06 AND 06 and
		datepart(day,qs.last_execution_time) BETWEEN 20 AND 22
	
		 --AND		CONVERT(CHAR(8),last_execution_time,8) BETWEEN '09:00:00' and '23:50:00' */


qs.last_execution_time > '2018-05-20 11:30:00.000' /* 1. Date & Time filter */
	--and qt.text like '%ChequeHead%'	/* 2. SQL query text filter */
	and qt.text not like '%Query Finder%'
… Читать далее

Как обновить статистику и очистить кеш планов.

declare @dt as datetime
set @dt= getdate()-01 -- дата, меньше которой будем обновлять


begin try

      select o.name as [objname],s.name as [shname],o.object_id,st.name as     [stname]

      into #t_stat

    from sys.stats st

        inner join  sys.objects o  on st.object_id=o.object_id

        inner join   sys.schemas  s on o.schema_id=s.schema_id

      where o.type='U'

      and STATS_DATE(st.object_id, st.stats_id)  <@dt    --фильтр по дате

      --and o.name='dbservers'                             --фильтра по обеъкту

end try

begin catch
      print (cast(error_number() as nchar(5))+'_'+ ERROR_MESSAGE())
      drop table #t_stat
end catch

--2 открываем курсом и обновляем статистику
declare cur1 cursor
             for select objname,shname,stname from #t_stat

open cur1
declare @stname sysname
declare @obj sysname
declare @sh sysname

fetch next from cur1 into @obj,@sh,@stname

WHILE
… Читать далее