We faced an inconsistent performance issue with Sql Server due to which a Sql SSIS Job was taking inconsistent time sometimes 25 minutes sometimes 1hour 35 minutes sometimes 2 hours and everybody was confused why this is happening and there was nothing in the logs or other logging software we were using.
So, then suddenly we got an email from a Database Developer that they have applied an emergency patch on the production server and we monitored the issue for a week and wow to my surprise the problem was actually resolved.
When i went to database developer and asked about it she told that she changed two settings
Cost Threshold for Parallelism from 0 to 30
(Each Query or Stored procedure has complexity so Sql server will try to apply Parallelism in Queries with Complexity more then 30 and not below that.
Degree of parallelism from 0 to 4.
(mind it Sql Server automatically spans as many thread it assumes machine can support and clearly it way more and hence the process taking too much time which it taken by complexity involved in thread switching. the best bet is (for example for 4 core and 8 thread machine is to use only 4 as value which will do throtelling on the number of thread it can use for Parallalism.)
