Have you every experience that you have a stored procedure(SP) that have plenty of parameters that when 1 set of parameter is executed for the very first time the said SPwas created, its fast. Then after you have a new set of parameter passed to the same SP you’ll end up a long execution process?
Then that is because of a so called Execution Plan Caching that MSSQL is doing on your query. In an overview MSSQL check if there is an existing execution plan for the SP. If there is none, it will create an optimized execution plan, compile, and then cache it on memory. So when the same SP is executed with the same number of parameter it will be reused.
So in cases that you have the same number of parameter every time you use the said stored procedure and you know that you have a logic that will change the query result then this optimization may not be on our side.
When does this caching is cleared? It is only cleared when either there are no memory allocation for MSSQL or you force MSSQL to recompile the SP.
How to Recompile your specific SP? Just add OPTION (RECOMPILE) at the end of your query, before the END statement, and you are ready to go. In some cases you may want to force all cache to be removed, you need to use another statement to do that and that is DBCC FREEPROCCACHE.
That’s it. Hope it helped you as it helped me on my project.
Have ever wondering on how can you handle a Single Login Per User on your website? Yes may be you will need to disable multiple login due to some security reasons. So here is the only perfect way that you can do it:
When the user login flag on the database either an IP(for single login on a network) or Session Id of the user (for single login across all network).
Then every time the user visit a page in your website check if the flag has been change.
If the flag has been change then log off the current user
That way you can manage only one login per account depending on what situation you want it to happen. If you are thinking that you don’t want to logoff the current user but disable other login that will be impossible to happen. Because you can’t stop a user from closing the browser or shutting down the computer. In such cases you can’t handle on your script.