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 is a very high number of log writes while inserting data. With
SELECT INTO ... it's much faster.
Can anyone give me a hint, or can tell me what this cached_pages_count exactly means?
I also mentioned high waititme during the inserts (up to 300.000 ms) Waittype is
CXPACKET and wait ressource is
e_waitPipeNewRow. The high values occur on
kind regards Thomas