strange SQL server report performance problem rela

Question

I got a complex report using reporting service, the report connect to a SQl 2005 database, and calling a number of store procedure and functions. it works ok initially, but after a few months(data grows), it run into timeout error.

I created a few indexes to improve the performance, but the strange thing it that it works after the index was created, but throws out the same error the next day. Then I try to update the statistics on the database, it works again (the running time of the query improve 10 times). But again, it stop working the next day.

Now, the temp solution is that I run the update statistic every hour. But I can't find a reasonable explanation for this behaviour. the database is not very busy, there won't be lots of data being updated for one day. how can the update statistics make so much difference?

Answer 1

I suspect you have parameter sniffing. Updating statistics merely forces all query plans to be discarded, so it appears to work for a time

CREATE PROC dbo.MyReport
    @SignatureParam varchar(10),
    ...
AS
...
DECLARE @MaskedParam varchar(10), ...
SELECT @MaskedParam = @SignatureParam, ...

SELECT...WHERE column = @MaskedParam AND ...
...
GO

Answer 2

I've seen this problem when the indexes on the underlying tables need to be adjusted or the SQL needs work.

The rebuild index and the update statistics read the table into the cache, which improves performance. The next day the table has been flushed out of the cache and the performance problems return.

SQL Profiler is very useful in these situations to identify what changes from run to run.