Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

edit:

I also mentioned high waititme during the inserts (up to 300.000 ms) Waittype is CXPACKET and wait ressource is e_waitPortOpen and e_waitPipeNewRow. The high values occur on e_waitPortOpen.

kind regards Thomas

share|improve this question
2  
Have you checked index fragmentation and stats ? –  Kin Mar 24 at 13:08
3  
Sudden perf changes may be the result of a query plan changing for the worse. I second Kin's suggestion of making sure stats are up-to-date. –  Jon Seigel Mar 24 at 13:36
    
How do I check the stats? They are switched to automatic update. –  Thomas Mar 24 at 14:26
    
EXEC sp_updatestats is a fairly standard way to update stats. It is typically run after a large data load. You can manually check them with sys.stats or sys.stats_columns. technet.microsoft.com/en-us/library/ms177623.aspx –  efesar Mar 24 at 15:57
    
Ok, I did all the updates on all db´s on the instance. But this didn't take any effect. Is there anything else I could check or do? regard Thomas –  user36072 Mar 25 at 18:24

2 Answers 2

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.

share|improve this answer
    
I'll give that a try. Is there a possibility that things will be more worse than before after updating the stats? –  Thomas Mar 25 at 6:53
    
It's not that probable. –  mrdenny May 20 at 21:35

We had a problem with AMD chipset where they would drop into power saving mode, reducing their clock speed by 50%. Worth a look.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.