Hi, I have got a very confusing problem. I have a stored procedure which is internally calling a number of child sps. some among them are called in a loop too. The issue i am getting is that the sp takes more than 3 minutes to complete which is not allowable for the application, which gets time out error. I found that if i run the sp for query analyser, for the first time it gives 3 minutes, but if we run it again, the time take will get reduced to 10 seconds only. For my wonder i found that the sp takes 3 minutes for every restart of my server (or restart of sql service). I am totally clueless. Can anybody help me on what could be the root cause of the issue. Statistics or Indexes are not rebuild during restart as per my knowledge. I have found many times that performance is increasing after restart. But this is for the first time i come across such a condition. Please put some lights on this issue.
Thanks in avance.. Paps
I am speculating a bit without more information, but this is probably caused by the operation of the SQL Server cache. When a procedure (or just about any query) is executed, SQL Server places its execution plan in the cache and may cache some or all of the results as well. When you restart SQL Server the cache is entirely lost and becomes "cold."
I would NOT recommend this, but one way to keep the cache "warm" would be to use a SQL Agent job that calls your procedure and discards the results on server startup and occassionally thereafter. You will need to do it occassionally thereafter because SQL Server watches how recently data and execution plans are used and cycles them out as they become stale. (What constitutes occassionally depends on how often that procedure is called for real use and how often other unrelated things are called, how big the cache is, etc.)
What I would recommend is going through the code and trying to optimize it. There are lots of good articles on basic optimization, but mine is here.
answered Jan 07, 2013 at 07:21 PM
This statement is completely wrong, there is no benefit in performance after SQL Server restart. When you restart, SQL Server will clear out its all memory contents including data cache and plan cache. So when a stored procedure is submitted for execution, SQL Server needs a execution plan to execute the sp, It checks the plan cache first, if found its reused and if nothing found, SQL Server will create a new plan this is resource consuming and has -ve impact in performance and is reused next time depending on the nature of the code written and other factors (such as schema change etc, plan aged out, restart etc).
Like wise SQL server checks the buffer pool first(logical read) for the data you try to retrieve, because they are memory objects and are very fast to retrieve when comparing to disk. So when you restart the buffer pool get emptied and SQL Server needs to pull the data from Disk (called physical read), this has high impact in your IO subsystems.
You don't need to do any rebuild or update stats after system restart.
answered Jan 07, 2013 at 05:18 PM