The following interesting case came out from our SQL User Group member Ilian Yovchev who works for David Holding company.
They have updated their text filed types to varcharmax data type according to the SQL Server roadmap and deprecation plans. They use update with WRITE to insert data in those new fields.
But they faced time outs during some updates and on query executions against the table with varcharmax field type. They have traced those time outs and found that the reason behind them is sync stats update performing on varcharmax col. Strange, isn’t it? Well, not exactly as synch stats update (Asych Stats Update=OFF in db level) can potentially lead to query time out.
Let’s see the specifics of this case.