Post reply

DB Transactions/Sec peak every 10 seconds with no user activity

  • July 25, 2008 at 5:24 am

    #210438

    SQL 2005 (9.0.3215)

    We have many databases on this (test) server and for some of them, not all, every 10 seconds there is a transactions/sec peak. The number is exactly the same every 10 seconds but is different for each database this is happening against.

    Nobody is using these databases, so it must be a background process.

    I believe I have found using SQL profiler that it is due to the GhostCleanupTask.

    However the only info I can found about that says it runs every 5 seconds, not every 10 seconds and it is to remove index entries that have been marked for deletion.

    As some of the DB's are simple and some are full and none of them are being used by users (so no activity) this is very wierd.

    We are experiencing the same on production servers.

    Maybe this is correct functionality, as these database are not being used, on a 70GB database the tran/sec count is 102 and a 28GB has a tra/sec count of 44.

    Maybe this amount of processing is needed to check what work there is to do?

    I am not aware that this is causing problems as such, so it is probably more an understanding of what is happening on the server.

  • July 25, 2008 at 8:15 am

    #848411

    I had never heard of that before, so I did some searching around. You're not the only one to see this. From what I can see out there, it's actually expected behavior. Further, transactions/sec is not a very good counter because of some of the ways that SQL Server 2005 works. You might want to look at batches/sec instead.

    Here's the first discussion I found on the topic that was in any way informative:

    http://www.mydatabasesupport.com/forums/sqlserver-server/203797-radically-different-transactions-sec-behavior-2005-a.html

    Here's a great explanation of the ghost cleanup process.

    I haven't seen this myself, but I'm usually only looking at the CPU and queues to determine if we have production problems.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply