SQL Server Performance

Commit Count and Commit Timeout

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Righteousman, Jun 20, 2007.

  1. Righteousman New Member

    Hello,

    Oracle and DB2 have options you can set to determine how often transactions are flushed from the log buffer. This greatly improves performance when you're doing thousands of transactions a second (that for development reasons cannot be rolled into one single transaction). Unfotunately I can't find anything like this in SQL Server. Does anyone know of a way to get this going? Possibly a trace flag. I know that any transactions that haven't been flushed to the log will be lost if the server goes down but that's something we're ok with.

    Anyone have any ideas?


    Thanks
  2. satya Moderator

    CHECKPOINT in SQL Server on the log records, refer to the books online in this case ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/ccdfc689-ad4e-44c0-83f7-0f2cfcfb6406.htm.
    CHECKPOINT - Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. Righteousman New Member

    Hi Satya,

    The checkpoint process refers to data pages, not the write-ahead transaction log. My problem is that after every insert operations that's its own implicit transaction, it's writing to the transaction log, so if I do 10,000 inserts in a second the overhead of the transaction logging is enormous. If I was able to tell SQL Server to only issue a transaction log commit every 1 second, I'd be set -- much less overhead. DB2 and Oracle have this, but SQL Server unforuntatly doesn't seem to.
  4. satya Moderator

    Performing frequent transaction log backups or keeping the smaller transaction will help in this case.
    Yes, SQL 2005 do have such feature:
    https://msdn2.microsoft.com/en-us/library/ms186259.aspx
    http://support.microsoft.com/kb/86903

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page