DEFINITIONS

Definitions More Info.
Definition ID946
TitleSQL
CategoryNOTES
Definitionsp ya da sorguda execution FREEPROCCACHE plan sorunu cozme
Definition DescriptionCANSU TIMEOUT DUZELTME
-- STEP 1

SELECT TOP(100) [text], cp.size_in_bytes, usecounts, refcounts
,db_name(dbid) as dbName, 'DBCC FREEPROCCACHE ( ', plan_handle , ')'
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE
cp.cacheobjtype = N'Compiled Plan' AND
cp.objtype = N'Prepared' AND
cp.usecounts > 1 AND
text like '%CandidateSearchLog%'
ORDER BY cp.size_in_bytes DESC;

-- STEP 2
-- istatistikleri update et

update statistics [dbo].[IsAramaLogDB]

--STEP3
--step 2 deki sorgu sonucunu calistir. alttaki ornekgibi

DBCC FREEPROCCACHE ( 0x06000D00FE7F361C40604B216702000001000000000000000000000000000000000000000000000000000000 )

--STEP4
-- sorunlusorguyu ya da stored procedure execution plan oluşması icin sabirlabekle ve monitoret lock var mi?!?

SELECT TOP (10000000) Id, Name FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY MAX(i.Id) DESC) AS RowNumber,
MAX(i.Id) AS Id,
i.Kriter AS Name
FROM IsAramaLogDB AS i (nolock)
WHERE i.LoginId = 6513040
and i.kriter IS NOT NULL
AND i.kriter!= ''
group by i.Kriter
) AS Result
WHERE RowNumber > 0


--Mesaj HareketDB
SELECT TOP(100) [text], cp.size_in_bytes, usecounts, refcounts
,db_name(dbid) as dbName, 'DBCC FREEPROCCACHE ( ', plan_handle , ')'
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE
cp.cacheobjtype = N'Compiled Plan' AND
cp.objtype = N'Prepared' AND
cp.usecounts > 1 AND
text like '%GetCandidateUnReadMessageCount%'
ORDER BY cp.size_in_bytes DESC;

-- STEP 2
-- istatistikleri update et

update statistics [dbo].[MesajHareketDB]


--STEP3
--step 2 deki sorgu sonucunu calistir. alttaki ornekgibi

DBCC FREEPROCCACHE ( 0x06000D000F5B8E39802B36146D02000001000000000000000000000000000000000000000000000000000000 )


--STEP4
-- sorunlusorguyu ya da stored procedure execution plan oluşması icin sabirlabekle ve monitoret lock var mi?!?

USE [AtlantisDB]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[GetCandidateUnReadMessageCount]
@candidateId = 6513040
SELECT 'Return Value' = @return_value
GO
RecordBycunay
Record Date27-06-2019 18:27:54
Düzenle
Kopyala
Sil