Sudden performance decrease in SQL Server

I've got a problem with a SQL Server 2008 R2 instance. Last week the performance suddenly broke down. All inserts are very very slow. It takes 4-5 times longer than before. On the hardware we found no defects. We had the same problem a month ago, but after one week the problem disappeared as fast as it came.

I'm not an expert on these things but I noted that the performance on all databases on the instance is poor. CPU and Disks are not at their limits, so it shouldn't be a hardware performance issue.

I'll took a look at the cached pages count with this query:

select
       count(*)as cached_pages_count,
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
    inner join
    (
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.hobt_id
                    and (au.type = 1 or au.type = 3)
        union all
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.partition_id
                    and au.type = 2
    ) as obj
        on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind 
  on  obj.objectid = ind.object_id
 and  obj.index_id = ind.index_id
where bd.database_id = db_id()
  and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc

Here I found very high values, but I don't know if it's bad or not. The count is from 201.236 to 1 on the different tables an indexes. Could this be an issue of the poor performance?

Another thing I noted

Sounds like your statistics are getting out of date, then eventually SQL's auto update statistics will kick in and update the stats, proper execution plans are being generated, then everything starts running faster again.

If you have installed Service Pack 1 for SQL Server 2008 R2 then you can turn on Trace Flag 2371 which will change the algorithm which is used from the static 20%+500 to a changing value depending on the number of rows in the table.

If you don't have service pack 1 installed then I'd recommend setting up a job that runs UPDATE STATISTICS for all your tables weekly or nightly.

StackExchange Database Administrators Q#61587, answer score: 2