We have a canned product (JIRA) and we recently migrated the database from sql 2008 to 2014. We are now encountering a few deadlocks daily on the database. Now JIRA's Solution was the folowing:
First, please ensure the database is set to READ_COMMITTED_SNAPSHOT ON as per Connecting JIRA to SQL Server.
I'm not familiar with this setting and was wondering what it will do and are there any adverse effects?
It will increase your use of tempdb, so best to monitor that. Also, It may not have any (positive) effect on the deadlocks. Depends on what's causing them
It will increase the size of every table in JIRA, because each row will increase by 14 bytes at some point. Be sure you have enough free space in each block to prevent massive page splits from happening because of that.
It will likely cut down on the deadlocks, although it's not guaranteed, since two modifying tasks can still deadlock each other. But SELECTs should cause vastly fewer deadlocks.