This is the final in a series on shrinking and transactions. You can see them all in the Shrinking & Transactions category.
In part 1, we talked about factors that affect transaction log growth. We talked about what a SQL Server transaction is and the various modes SQL interacts with a transaction (Autocommit, Implicit Transactions and Explicit Transactions).
The theme of that post was about managing your transactions so they don’t manage you. In that same theme, let’s continue being in charge of our transactions. Rather than from a physical size perspective, let’s look at it from a performance standpoint.
Database Engine/Administration Factors
When it comes to ensuring that transaction logging and transactions don’t cause performance bottlenecks there are a few things DBAs should remember and look at, I’ll highlight a few of them.
Pay careful attention to the I/O of your transaction log – As I discussed in Part 1, the transaction log record needs to be written first and this is vital to remember. Keep your Transaction log on fast I/O, separate I/O and follow best practices with your log file.
A great blog post by Kimberly Tripp with eight tips here goes through many more details that I have often hinted about in various posts. Some of her tips are best practices for the data file as well.
She mentions things like: Pre-Allocate Your Space!! This is a huge pet peeve of mine. Plan for your capacity and start out with the size you think you will need. This will stop costly autogrowths which can lead to fragmentation and waits (for the growth.)
Or Defragment the physical volume , people spend a lot of time in the database world looking at index fragmentation (at least I hope you do!) What about your physical storage?
When it comes to choosing your RAID, remember the transaction log is going to want fast writes. These writes are sequential. These writes are writes. RAID 5 (Wikipedia article on RAID) may not generally be a horrible solution for your data (.MDF/.NDF) files. For the Log files, it just doesn’t make sense. RAID 5 incurs extra cost for each write (essentially 2 reads and 2 writes for each write because of the maintenance of parity for fault tolerance).
If cost is an issue, look at just mirroring. This will give you some fault tolerance (Remember, the transaction log is key to a happy, consistent database as we talked about in Part 1) and still gives you the same performance as writing to one disk.
If the mirror doesn’t give you great performance for your high end, high throughput applications, consider RAID 10 or RAID 0+1 (both are essentially combining striping and mirroring in various means). These will give you the redundancy with the performance benefit of a stripe.
Don’t get into a shrink/grow. shrink/grow pattern. I have harped on this one. Set it to the size you think you need, manage it’s growth and monitor it. Tibor Karaszi has an excellent post about log shrinking and it’s effect on Virtual Log Files (VLFs).
Pick the right recovery model! As I discussed in Part 1, recovery models are important for log growth. It’s also important to pick the right model for your recovery needs (in fact much more important to pick for recovery needs.. (Remember we do backups for restores in fact it’s the restores that are more important). Let’s presume that you care about point in time recovery and you pick Full Recovery:
That’s great. Now get a strategy to PERFORM LOG BACKUPS. Being in full recovery mode without doing full backups is pointless. This tells me you aren’t performing restore drills (are you?), you are probably truncating and shrinking your log file when you run out of space and you potentially can’t meet SLAs or expectations. This will cause all of the above issues but it will also manifest itself in performance problems. So get with the program and do those log backups on a schedule that meets your required recoverability (one of the “itties” we should all care about) and manages growth/performance best.
These are some of the highlights that quickly come to mind that a DBA can have effect on. There are definitely more, check out Kimberly’s post above and look around her blog, great stuff to keep in mind.
What about:
Things Developers Can Be Mindful Of
KISS – Keep it short stupid – Alright, the stupid part was uncalled for but it worked. The key to remember when developing is if you are holding a transaction open, you are holding locks for the duration of your transaction.
What kind of locks you are holding depends on your operation (Select, DML, DDL, etc) and your transaction isolation level and any lock hints you may be using (Andrew Kelly has a great post about the dangers of NOLOCK or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED). Remember that when you are in a transaction, whatever lock you had to hold to perform your operation is held until that transaction is committed.
If you perform an update within an explicit transaction (BEGIN TRANSACTION) and you are in the default isolation level of SQL Server (Read Committed) you will be holding some sort of exclusive lock even after your update statement is finished. SQL doesn’t know if you are going to rollback or commit your work so it has to lock either the rows, page or table (depending on the grain of lock chosen) until you tell SQL that you are done. This means that you can block other updates, other reads or other operations until you have committed/rolled back your transaction.
This is excellent behavior out of a DBMS. You want to hold a lock until you are done. Otherwise you might as well just throw data integrity out the window. It’s bad when you hold that poor transaction hostage longer than need be. So, the solution is simple:
- Keep your transactions as short as possible
- Properly handle errors and rollback when necessary
- COMMIT as soon as possible
- Don’t try to solve world peace and hunger within one transaction – I cringe when I see a single “BEGIN TRANSACTION” and then multiple page downs of operations, cursors, select statements, crazy logic, etc before you get to the commit or rollback. This means that all of those locks are held and you are probably causing blocking until you are done. Batch out work.
- Even with implicit transactions, don’t try to do too much work in one operation. If you are updating an entire table, and it is a huge table, try and batch it out, allow the work to commit, allow a checkpoint to fire and keep your log from growing.
You tell those Writers, we just want to read!
Sorry, you hold locks also. When you issue a select you are still holding a potentially blocking lock. This depends on isolation level and lock hints but in the default isolation level you are holding a lock. This is to prevent you from reading data that is about to be rolled back. Readers Can (and often do) block writers. If you are in a busy OLTP system and experience a lot of timeouts on update/insert operations look at your reporting queries, your select queries.
Optimize your code for performance. This holds true for updates/inserts and selects but watch your selects, if it takes you 10 seconds to bring back data to a screen, that is 10 seconds (per select) that you are potentially blocking a writer. Even if it takes 20ms but is executed quite frequently, that is a potentially substantial situation. Look at the performance of every piece of code you release! Query tuning doesn’t have to be difficult. One place to start is this post but a quick search will yield a lot of tips. Check out my blogroll, a lot of excellent advice on those blogs.
Think about performance at the beginning of a project rather than the end and your life will be easier in production. DBAs – make performance be an initiative at the beginning. Yes you are too busy for proactive work right now, make time for it anyway. When you pay later you always pay a lot more.
Again, with Part 1, I am hoping that a lot of this was review and simple. At the same time, if it helped anyone, great. There is plenty of advice out there and some careful thought and consideration to managing your transactions and .LDFs will go a long way.
Related Posts At StraightPath SQL
1 thought on “Manage Your Transactions (So They Don’t Manage You – Part 2)”