none
Can a large log file impact query performance?

    Pertanyaan

  • Can a continuously growing log file impact queries performance? Has it any connection with the overall database performance?
    28 Februari 2012 12:48

Jawaban

  • Hello,

    Q: Can a continuously growing log file impact queries performance?
    A: It depends. As far as SELECT queries are concerned, no. SELECT queries aren't a logged operation and won't cause any changes to the database, they generally depend on a valid and optimized plan. INSERT/UPDATE/DELETE can be impacted. I say can, because they may not necessarily be as it depends if the logging of the operation is what's making the request wait or the growth of the log. The overall SIZE of the log doesn't dictate performance from a query point of view, but a recovery point of view. The only time this isn't the case is if you had a large log file that was set to autogrow by a certain amount, say 10% since that's the default. When a log file grows, all of the new portion of the log must be zeroed out. If we take 10% of a large log, say 50 GB, that's 5 GB which isn't much, but could have a noticeable performance hit.

    Q: Has it any connection with the overall database performance?
    A: It does. Backup and recovery times can take longer with a larger log file, can but not necessarily will. There are many factors that go into it such as number of vlf (virtual log files), amount of data needing to be backed up, transaction log growth, etc. As I noted above, growing the log file will impact performance.

    -Sean

    28 Februari 2012 13:49
  • Hello,

    What recovery model is the database with the transaction log in question?

    Truncating the transaction log doesn't make it physically smaller, it stays the same size. What happens is the active vlfs that can be reused are marked as inactive and can be overwritten and reused. When the log wraps back around it can then write over those vlfs instead of having to grow the file.

    The following links should give you a plethora of explanation about log files and internals.

    Resource: http://msdn.microsoft.com/en-us/library/ms179355.aspx
    Resource: http://www.sqlskills.com/BLOGS/PAUL/category/Transaction-Log.aspx
    Resource: http://www.sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx

    -Sean

    29 Februari 2012 13:23

Semua Balasan

  • It could affect the performance if it is continuously growing. mainly because of the extra I/O on the disk when the LDF file is being expanded. Besides huge LDF files are more complicate to handle.


    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    28 Februari 2012 13:33
  • Hello,

    Q: Can a continuously growing log file impact queries performance?
    A: It depends. As far as SELECT queries are concerned, no. SELECT queries aren't a logged operation and won't cause any changes to the database, they generally depend on a valid and optimized plan. INSERT/UPDATE/DELETE can be impacted. I say can, because they may not necessarily be as it depends if the logging of the operation is what's making the request wait or the growth of the log. The overall SIZE of the log doesn't dictate performance from a query point of view, but a recovery point of view. The only time this isn't the case is if you had a large log file that was set to autogrow by a certain amount, say 10% since that's the default. When a log file grows, all of the new portion of the log must be zeroed out. If we take 10% of a large log, say 50 GB, that's 5 GB which isn't much, but could have a noticeable performance hit.

    Q: Has it any connection with the overall database performance?
    A: It does. Backup and recovery times can take longer with a larger log file, can but not necessarily will. There are many factors that go into it such as number of vlf (virtual log files), amount of data needing to be backed up, transaction log growth, etc. As I noted above, growing the log file will impact performance.

    -Sean

    28 Februari 2012 13:49
  • Hi,

    As previously stated, log file growth consumes resources and can block processes until complete.  Also, depending how you initially sized the log file and its growth properties, you could have hundreds/thousands VLF's, which could certainly impact performance.  If your database is mirrored, for example, the size/growth of the log file *may" also end up affecting performance depending on the mirroring configuration, pipe, etc.



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    28 Februari 2012 13:55
  • How to limit the log file size to keep track of one week? I want to truncate log file every week automatically.
    29 Februari 2012 5:17
  • Hello,

    What recovery model is the database with the transaction log in question?

    Truncating the transaction log doesn't make it physically smaller, it stays the same size. What happens is the active vlfs that can be reused are marked as inactive and can be overwritten and reused. When the log wraps back around it can then write over those vlfs instead of having to grow the file.

    The following links should give you a plethora of explanation about log files and internals.

    Resource: http://msdn.microsoft.com/en-us/library/ms179355.aspx
    Resource: http://www.sqlskills.com/BLOGS/PAUL/category/Transaction-Log.aspx
    Resource: http://www.sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx

    -Sean

    29 Februari 2012 13:23