One of my forum members was experiencing a strange issue; whenever their SQL server slows down, their DBA increases the sql server memory buffer by 1 MB and immediately after their queries start working faster; and then they reduce the memory by 1MB again to go back to their original configuration.
This seems to be tricky, first I didn’t have any clue why this strange behaviour, then I realised sp_configure will call DBCC PROCCACHE () statement to clear the execution plan; Why it does that we will check that later; so after setting the memory by 1MB, all the plans are gone and SQL has to make fresh plans for the next call; that means, his issue is parameter sniffing; i asked him to update his stored procedures by adding local variables which holds the value of the parameters or to add a recompile at the statement level.
You might wonder why sp_configure calls DBCC FREEPROCCACHE. You can invalidate procedure plans by changing options that sp_configure supports. For example, if you change the max degree of parallelism option, SQL Server has to treat all procedure plans as invalid. Issuing DBCC FREEPROCCACHE is a convenient and simple way for SQL Server to ensure that all plans are consistent with sp_configure's current settings.
Technorati Tags: Impact,ProcCache,forum,server,memory,configuration,clue,behaviour,DBCC,statement,execution,plans,parameter,procedures,FREEPROCCACHE,procedure,supports,example,degree,option,members,parameters,options,sp_ConfigureWindows Live Tags: Impact,ProcCache,forum,server,memory,configuration,clue,behaviour,DBCC,statement,execution,plans,parameter,procedures,FREEPROCCACHE,procedure,supports,example,degree,option,members,parameters,options,sp_Configure
WordPress Tags: Impact,ProcCache,forum,server,memory,configuration,clue,behaviour,DBCC,statement,execution,plans,parameter,procedures,FREEPROCCACHE,procedure,supports,example,degree,option,members,parameters,options,sp_Configure
Blogger Labels: Impact,ProcCache,forum,server,memory,configuration,clue,behaviour,DBCC,statement,execution,plans,parameter,procedures,FREEPROCCACHE,procedure,supports,example,degree,option,members,parameters,options,sp_Configure
