SQL Server Performance Forum – Threads Archive
Commit Count and Commit Timeout
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
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.
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.
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.
]]>