x

Performance issue after restart

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

more ▼

asked Jan 07, 2013 at 05:06 PM in Default

avatar image

paps
20 4 4 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Jan 07, 2013 at 07:21 PM

avatar image

TimothyAWiseman
15.6k 22 51 38

(comments are locked)
10|1200 characters needed characters left

I have found many times that performance is increasing after restart.

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.

more ▼

answered Jan 07, 2013 at 05:18 PM

avatar image

Cyborg
10.8k 37 55 51

This is not strictly accurate. The user could see improvements in performance upon a restart if they are running into problems of parameter sniffing as regards the stored procedures. Restarting SQL and clearing the plan cache means a new plan gets created which may actually help things. I would say that some statement recompiles on those procedures may be a good option here, but without further details who knows.

Jan 07, 2013 at 06:35 PM SirSQL

I think the OP is saying that execution time is increasing, so you could say actual performance is decreasing temporarily after the restart. In general you are right that restarting SQL will not help performance, but SirSQL is right that there are specific expections to that.

Jan 07, 2013 at 07:13 PM TimothyAWiseman

There are lots of situations where a restart improves performance. That's not to say that there weren't any number of other solutions that would also help performance, clearing the cache, updating statistics, etc., etc., etc., but yeah, I have seen a reboot improve performance.

Jan 08, 2013 at 12:28 AM Grant Fritchey ♦♦

@Grant Yes, me too, though I suspect (based on what I've seen, but without thorough testing) that the cases where it improves performance are the exception, not the rule. Unless there was some specific problem that the reboot fixed, performance has generally seemed about the same or even a touch slow right after a reboot.

Jan 08, 2013 at 01:08 AM TimothyAWiseman

Right there with you. On a normal system under normal circumstances, I'd expect to see a slow-down. I've just seen many people with parameter sniffing and other issues reboot and see performance go up. It's not that the reboot is a good solution, but that it does things which brings about good things, rather by accident.

Jan 08, 2013 at 01:44 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x476
x310
x21
x8

asked: Jan 07, 2013 at 05:06 PM

Seen: 5384 times

Last Updated: Jan 08, 2013 at 09:20 AM

Copyright 2017 Redgate Software. Privacy Policy