question

AshifShaikh-9988 avatar image
0 Votes"
AshifShaikh-9988 asked SeeyaXi-msft commented

How can I schedule recompilation of an adhoc query??

Hello,

I have scheduled a recompile for few of my procs like this exec sp_recompile [proc_name].

I want to do the same thing with an adhoc query, currently I manually re-compile it in case of any issues by using dbcc freeproccache(plan_handle) however I want to automate it and regularly clean the plan cache of the adhoc query.


P.S I believe optimisation is a long term resolution however in this case the adhoc query is scheduled to be deprecated in few weeks and I don't want to spend much of my time in optimising it.

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AshifShaikh-9988 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it dosn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
ryochanan avatar image
0 Votes"
ryochanan answered ErlandSommarskog commented

Hi @AshifShaikh-9988

if this is just a temporary solution why not automating the process with this query

select cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where st.text = '<YourAdhocQuery or GUID you plant into the query to help with searching for the query>'

this will help you get the plan handle for your adhoc query and then you can use the dbcc command to evacuate it from plan cache.

HTH,

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I don't think this is what Ashif is asking for. WITH RECOMPILE causes compilation every time. My understanding is that Ashif wants a cached plan, but for some reason want to force a recompilation with some frequency. I am not really sure why this would be a good idea, but I don't know the full story.

The solution go get the plan handle suggested by Ryo seems like the way to go.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

You should just add "WITH RECOMPILE" to your procs, then you don't need to do anything.

See: https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

Do the same with your adhoc query.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @AshifShaikh-9988,

Execute the DBCC FREEPROCCACHE command to release all plan caches, which will cause stored procedures and AdHoc queries to be recompiled and generate new plan caches.
You can add "WITH RECOMPILE" to your procs like Tom mentioned.

The sql script that automatically and forcibly releases memory(DBCC FREEPROCCACHE) is as follows:

 DECLARE @TargetMemory decimal(19,2), @TotalMemory decimal(19,2), @UserMemoryPecent decimal(19,2)
 select @TargetMemory=cntr_value from sys.dm_os_performance_counters
 where counter_name='Target Server Memory (KB)'
 select @TotalMemory=cntr_value from sys.dm_os_performance_counters
 where counter_name='Total Server Memory (KB)'
 set @UserMemoryPecent=@TotalMemory/@TargetMemory
 select @UserMemoryPecent
 IF @UserMemoryPecent>0.1
 BEGIN
 DBCC FREEPROCCACHE
 exec sp_configure 'show advanced options', 1
 --Set the maximum memory value and clear the existing cache space of 1000M (set according to the actual situation, the idea is to first reduce the maximum value, and then set it back to the appropriate value)
 exec sp_configure 'max server memory', 1000 
 EXEC ('RECONFIGURE')
 --Set the waiting time. It takes a while to force the release of memory
 WAITFOR DELAY '00:01:30'
 --Reset the maximum memory value (choose the appropriate value according to the actual situation)
 EXEC  sp_configure 'max server memory', 3000
 EXEC ('RECONFIGURE')
 exec sp_configure 'show advanced options',0
 end

Best regards,
Seeya


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.