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.