6

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

Improve this question
8
  • 2
    Have you checked index fragmentation and stats ?
    – Kin Shah
    Mar 24, 2014 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, 2014 at 13:36
  • How do I check the stats? They are switched to automatic update.
    – Thomas
    Mar 24, 2014 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, 2014 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, 2014 at 18:24

2 Answers 2

Reset to default
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.

Improve this answer
2
  • 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, 2014 at 6:53
  • It's not that probable.
    – mrdenny
    May 20, 2014 at 21:35
2

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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