Issue After Converting Table Column From "Text" to Varchar(max)

I had a table called Person with column PersonDescription which was of type "text". I had issues with updating this column so i ran the script

ALTER TABLE dbo.Person ALTER COLUMN PersonDescription VARCHAR(max)

to change the column to be varchar(max). This was all fine and ran instantly. However now i have noticed that anytime i try to update this column then It is taking up to 3-4 mins to execute. Query is

Update Person set PersonDescription ='persons description' where personid=18

After this update is ran then it executes instantly. This is all fine but when this change goes to production then this table has a million records so every person that logs in is going to timeout when this runs. Can anyone tell me how i 开发者_JAVA技巧can prevent this. Is there another script etc that i need to run. After running the update i saw that Statman runs on sqlserver which is what is taking the time.

thanks Niall

---

It is my opinion that the issue is due to SQL server calculating if it should store the data in a blob or the row.

See:Using varchar(MAX) vs TEXT on SQL Server

"The VARCHAR(MAX) type is a replacement for TEXT. The basic difference is that a TEXT type will always store the data in a blob whereas the VARCHAR(MAX) type will attempt to store the data directly in the row unless it exceeds the 8k limitation and at that point it stores it in a blob."

Are also you using a full-text index? Disable indexes, update, rebuild indexes.

Have you tried creating a new table and copying records to it? Do you still have the same performance issues?