I want to reduce the % of the WRITELOG wait(now around 40%).Right now the log file is on its own array and is 2.5 GB. I am planning the next steps: 1. shrink the log to 1 GB(I am thinking that a smaller file will improve the writes) 2. add a second log file on a different disk. Do you think this will make the writes to the log faster?
Heavy rollback activity adds additional log and disk I/O contention, and such activity has been observed as contributing to writelog timeouts. For best performance, attempt to minimize the number of transactions that must be rolled back; as activity on the server increases, using a query timeout that is too short may further exacerbate the problem by causing unnecessary rollbacks. Identify disk bottlenecks using counters, Profiler, ::fn_virtualfilestats, and Showplan. Any of the following will reduce these waits: - Adding additional I/O bandwidth. - Balancing I/O across other drives. - Placing the transaction log on its own drive. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Hi Satya, Thanks for you response. So you don't think the size of the log file is affecting the write duration ? Also can you explain how to achieve this two for the log file : - Adding additional I/O bandwidth. - Balancing I/O across other drives. Best Regards
Did you run Performance Monitor to see physical queue lengh for each drive? With that may be you find heavy I/O over one disk and low over other one. About balancing I/O, I gess Satya means to put data or index in differents drives. BTW: What SQL and SP have you install? Luis Martin Moderator SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
True in the terms of Balancing the I/O the drives. As your hardware seems to be high-spec with no I/O bottlenecks, then leave it as it is. I would suggest to go as per your plan to add another Tlog in order to share the transaction log files. BTW what kind of transactions are held in the database? Any other applications sharing the server resources? Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
If you have more than one database you can identify the most active databases with fn_virtualfilestats as mentioned above. Run: SELECT * FROM ::fn_virtualfilestats(dbid,-1) on each db you have (replace dbid)
After reducing the log size from 2.6 GB to 1 GB the WRITELOG waittype percentage dropped at 22% from 44%. So reducing the size of the log helped in this case. The second in percentage is CXPACKET(24%) and I plan to reduce the max degree of parallelism from 4 to 2(or to 0). Regarding the output of ::fn_virtualfilestats, how you interpret the values ? DbId FileId NumberReads Number Writes BytesRead BytesWritten IOStallMS ----------------------------------------------------------------------------------------- 814.864.959 389.053 153.623.085.056 3.859.742.720 20.981.951 82 1.617 2.196.308 86.789.632 2.494.283.264 532 71 675.155 35.411 21.937.078.272 362.954.752 2.086.094 72 166 96.959 1290.240 130.366.976 377 Is 20.981.951 a high value ? Is the number of Writes for the log file 21.196.308 high(I assume the log file is 2) Best Regards Cristian
Thats good on the logwrite % and BTW what kind of H/w is on the machine. On the terms of Max degree of parallelism, test it before setting. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
What is the log backup strategy that you have implemented in the system? If there are operations in the system which need the log to be 2 GB then again the log file will come back to the original state. In any case, I generally recommend having regular transaction log backups so that both the log file size and the size of active log in it does not keep on increasing. Gaurav Moderator Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Then you can try reducing the log the maximum possible isze and let it grow to the level it needs. That way you'll know how muvh log space you typicallu need. Gaurav Moderator Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Yes I agree with Gaurav about the interval of the log backups, which will take care of logical sizes for the TLog. During DBCC checks try to enable it atleast once in 15 mins. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
