We have a DB Server where we found a simple update query going parallel concurrently and causing blockings and deadlock. The cost of the query was around 7. CTFP on the server was set to default value of 5.
Now we have decided to change the value of CTFP from 5 to a higher value.
Before that we just want to know what specific metrics should we consider/capture/track before and after making the change which can confirm that changing the value of CTFP to a higher value has actually improved the overall performance of DB Server. This DB has multiple user databases.
Any help would be appreciated.
Note: This server is having 4 logical processors. Hence we are not going to change the MaxDOP from its default value.