High CPU utilization after SQL Server reboot.

[s***@gmail.com]

Hi,

The production database server indexes are rebuilt, usage updated and
the server is rebooted once a month. For about two days after this
maintenance window, performance isn't as good as it used to be and I've
noticed CPU utilization increases by 15-25%. I realize that all SPs
have to be recompiled after a SQL Server restart and this consumes
additional resources. But is it normal to see such marked performance
degradation after a reboot? Is there anything I can do to avoid this
short-term performance dip?

Thanks,
Smitha

[Tony Rogerson]

Hi Smitha,

Check the SQL Server Errorlog, in Query Analyser you can run
master..xp_readerrorlog and check if any databases are still doing recovery.

SP's etc... will only get recompiled when they are called.

You really don't need to reboot servers anymore, i'd suggest you keep it up
and running and only reboot on implementing hot fixes, this will keep the
SQL cache balanced for your load, connection pooling primed etc....

If you want to diagnose the problem then on your next reboot, before
rebooting set the MSSQLServer service to manually start, then on the server
rebooting wait until everything else has loaded, get SQL profiler up and
ready then quickly fire off a trace as soon as you have restarted the
MSSSQLSErver service, you could set up a trace using proc's and set to auto
start.

Hope that helps.

Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Post by s***@gmail.com
Hi,
The production database server indexes are rebuilt, usage updated and
the server is rebooted once a month. For about two days after this
maintenance window, performance isn't as good as it used to be and I've
noticed CPU utilization increases by 15-25%. I realize that all SPs
have to be recompiled after a SQL Server restart and this consumes
additional resources. But is it normal to see such marked performance
degradation after a reboot? Is there anything I can do to avoid this
short-term performance dip?
Thanks,
Smitha

[Michael D'Angelo]

once a month would correspond to when MS released patches :)

Post by Tony Rogerson
Hi Smitha,
Check the SQL Server Errorlog, in Query Analyser you can run
master..xp_readerrorlog and check if any databases are still doing recovery.
SP's etc... will only get recompiled when they are called.
You really don't need to reboot servers anymore, i'd suggest you keep it
up and running and only reboot on implementing hot fixes, this will keep
the SQL cache balanced for your load, connection pooling primed etc....
If you want to diagnose the problem then on your next reboot, before
rebooting set the MSSQLServer service to manually start, then on the
server rebooting wait until everything else has loaded, get SQL profiler
up and ready then quickly fire off a trace as soon as you have restarted
the MSSSQLSErver service, you could set up a trace using proc's and set to
auto start.
Hope that helps.
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Post by s***@gmail.com
Hi,
The production database server indexes are rebuilt, usage updated and
the server is rebooted once a month. For about two days after this
maintenance window, performance isn't as good as it used to be and I've
noticed CPU utilization increases by 15-25%. I realize that all SPs
have to be recompiled after a SQL Server restart and this consumes
additional resources. But is it normal to see such marked performance
degradation after a reboot? Is there anything I can do to avoid this
short-term performance dip?
Thanks,
Smitha

[Jim Underwood]

I am just making a couple of guesses here regarding what would explain this,
but here goes...

When the server first comes up, a couple things are different:

1. Execution plans are no longer cached, so there is a little overhead as
every new SQL statement executes and is cached.

Generating execution plans I would expect to take a very small amount of
overhead and account for only a small amount of what you are seeing.
Nonetheless, some of the extra CPU would be explained by this.

2. Data is no longer cached, so everything needs to be read from disk,
rather than from memory.

Reading from disk data that is normally cached in memory I would expect to
take much more resources. All queries will run slower until the data is
cached in memory, and this means more concurrent processes, thus more CPU.
It is possible that running a couple queries that will read a lot of this
data from disk and place it in cache may make a difference. The more memory
you have, the more I would expect this to make a difference. Of course,
figuring out what data to query for this purpose is a challenge in itself,
assuming that this is really part of the issue.

A possible test would be to try duplicating this on a test machine. If you
can mimic the load well enough to duplicate this after a reboot, you can try
purging the data cache and the execution plans separately then measure the
results of each. I'm not sure of the commands for each of these functions
(or if they can be done separately) but it seems it should confirm or rule
out each of these as an issue.

Post by s***@gmail.com
Hi,
The production database server indexes are rebuilt, usage updated and
the server is rebooted once a month. For about two days after this
maintenance window, performance isn't as good as it used to be and I've
noticed CPU utilization increases by 15-25%. I realize that all SPs
have to be recompiled after a SQL Server restart and this consumes
additional resources. But is it normal to see such marked performance
degradation after a reboot? Is there anything I can do to avoid this
short-term performance dip?
Thanks,
Smitha

[s***@gmail.com]

Tony and Jim,

Thank you for the responses.

I will start a trace next time the server is rebooted to determine what
causes the CPU load and then perhaps see if we can get away with not
rebooting the server. If not, then perhaps compile a list of oft used
SPs (the ones which perform data lookups) and fire them after a reboot
and see if that will help.

[Jim Underwood]

Please post your findings back on the newgroup. I am interested in knowing
the results, and others might have the same issue.

Post by s***@gmail.com
Tony and Jim,
Thank you for the responses.
I will start a trace next time the server is rebooted to determine what
causes the CPU load and then perhaps see if we can get away with not
rebooting the server. If not, then perhaps compile a list of oft used
SPs (the ones which perform data lookups) and fire them after a reboot
and see if that will help.

[s***@gmail.com]

I did not reboot the server this time but instead only performed DBCC
DBREINDEX on all tables, updated all stats and updated usage as part of
monthly maintenance.
Also added 3 additional data files for TempDB to use (this machine has
4 processors) so the number of data files for TempDb = number of
physical processors on the box.

After everything, went into the application and clicked through some of
the most common pages - there was a delay of a second or two the first
time - but everything ran fine the second time around.

Performance seems fine so far (better than normal which I would expect
after performing maintenance on the DB) - no erratic CPU usage spikes.


Because recompiles are forced only after a SQL Server restart - perhaps
this was causing the high CPU utilization. Sorry - did not run the
trace. I realize that traces help identify issues, but it makes me
nervous to add additional load on the production DB server when the
issue is heavy load on the server. (In the past, I've had the trace
run on a second server and stored results on this second server but
still saw a small performance decline in the prod db server which held
steady during the duration of this trace.)

Thanks again for all of your input.

Post by Jim Underwood
Please post your findings back on the newgroup. I am interested in knowing
the results, and others might have the same issue.

Post by s***@gmail.com
Tony and Jim,
Thank you for the responses.
I will start a trace next time the server is rebooted to determine what
causes the CPU load and then perhaps see if we can get away with not
rebooting the server. If not, then perhaps compile a list of oft used
SPs (the ones which perform data lookups) and fire them after a reboot
and see if that will help.

Please post your findings back on the newgroup. I am interested in knowing
the results, and others might have the same issue.

Post by s***@gmail.com
Tony and Jim,
Thank you for the responses.
I will start a trace next time the server is rebooted to determine what
causes the CPU load and then perhaps see if we can get away with not
rebooting the server. If not, then perhaps compile a list of oft used
SPs (the ones which perform data lookups) and fire them after a reboot
and see if that will help.