Why is my SQL Server faster when restarted?

Question

We have a .net winforms application accessing a SQL Server 2008 database over .net web services. Occasionally our application behaves very slow, but after restarting the SQL Server service, the application performs much faster.

We do have automatic scheduled job that runs every night to clear buffer and procedure cache with following commands.

DBCC FREEPROCCACHE,
DBCC DROPCLEANBUFFERS

Can anybody please confirm that running above commands can actually slow down query performance and we should get rid of it?

Why does the application runs comparatively fast after SQL Server is restarted?

Thanks for the help.

Edit : we were looking at few threads here and found that temp tables could be the problem here. when sql server gets rid of all temp tables when restarted and so acts much faster.what exactly are temp tables? we do select into #tables at many places in our queries. are these temp tables and can we do something in procedure itself to drop temp tables once they are not needed.

Thanks.

Answer 1

Since you asked "why", I'm going to give you the full answer :)

Running DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS on a periodic basis is not an effective solution to your problem. Like a restart, if it improves anything, it's only by coincidence, and you are likely to see erratic results. It's entirely possible that if this "fix" corrects your performance problem, it will come back in the future seemingly randomly and with a greater severity than you're seeing now.

When you run DBCC FREEPROCCACHE, SQL Server will recompile each stored procedure or cached query plan it has the next time they are run. If it turns out that SQL Server chooses plans that are better for general operation on this subsequent run, you will generally see increased performance. If SQL Server chooses a plan which generally performs worse, you will see decreased performance. Why would it choose a plan which is generally worse? Well, it depends on the first query that gets executed -- SQL Server will choose a plan for a statement based on the selectivity of the parameters passed. Take a look at this example:

SET NOCOUNT ON;

IF OBJECT_ID('temporary_demo')     IS NOT NULL DROP TABLE temporary_demo;
IF OBJECT_ID('temporary_demoproc') IS NOT NULL DROP PROC  temporary_demoproc;
GO

CREATE TABLE temporary_demo (id int primary key identity(1,1), id2 int, txtval nvarchar(100));

insert temporary_demo (id2, txtval) values (1, 'something highly selective');

declare @i int;
set @i = 1;
while @i < 10000 begin
    insert temporary_demo (id2, txtval) select 2, 'something else ';
    set @i = @i + 1;
end

insert temporary_demo (id2, txtval) select 2, 'needle in a haystack';

create index ix_id2 on temporary_demo(id2);
GO

create proc temporary_demoproc @searchid int, @searchtxtval nvarchar(100) as
    select * from temporary_demo where id2 = @searchid and txtval = @searchtxtval;
GO

--Look at the query plans for both of these procedures.
--Note that the plan chosen depends on which one is called first.
exec temporary_demoproc 1, 'something highly selective';
exec temporary_demoproc 2, 'needle in a haystack';

dbcc freeproccache;

exec temporary_demoproc 2, 'needle in a haystack';
exec temporary_demoproc 1, 'something highly selective';

So you see from the example that if your performance happens to increase because of a call to DBCC FREEPROCCACHE, it's most likely because you are giving your app the chance to get a different query plan for your queries the next time they run. Again -- it's only by chance or coincidence that your application would get faster (or slower).

As far as DBCC DROPCLEANBUFFERS goes, running this periodically will almost always slow down your application. This command forces queries to go to the storage subsystem instead of reading cached pages from memory. The only way I can think of that this might help performance would be if your database server's memory configuration is improper, and it's swapping to the pagefile because it's out of physical memory.

So what's the solution?

The solution is to not look for a general cause or band-aid. You are going to need to run a trace of your poorly performing queries using SQL Server profiler, and identify the individual queries which are problematic. You are going to have to do some "nuts and bolts" analysis on these queries and really understand them. You're going to need to find out if indexes need to be added, changed, or even deleted on a query-by-query basis, and you're going to need to identify problematic queries that may need to be rewritten.

Answer 2

Running DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS will flush the cache that the SQL Server process has built up.

This can be a bad thing - you lose all the stored execution plans, which means, every query that comes along must now be evaluated, parsed, and an execution plan for it must be determined - this will slow down each first time execution of every query.

This can also be a good thing - it gives SQL Server a chance to clean up, to get rid of large buffers that might be bogging down the system performance.

Is there any chance you could turn off this nightly procedure for a week or two and observe if your application behaves better with this turned off??