3

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 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

Somedeveloper
  • 817
  • 2
  • 14
  • 31
  • 2
    If it is auto creating statistics that should only be a one off task. Not sure what happens if a timeout happens while it is compiling the query though. That query seems like it shouldn't need any stats on the `PersonDescription` column though. Only on `personid` – Martin Smith Apr 28 '11 at 13:50
  • thanks for replying Martin. i managed to fix this by creating a deployment script which updates the new modified column for 1 single record. this way the stats get updated before first user logs into the system so wont cause any un wanted issues. thanks. – Somedeveloper Apr 29 '11 at 15:35
  • I wonder if this could be related to a bug we discovered in sql server when we changed text to varchar(max) on fields that were previously null. http://stackoverflow.com/questions/10295771/why-is-is-not-null-returning-null-values-for-a-varcharmax-in-sql-server. We fixed it using sql like UPDATE Person SET PersonDescription = PersonDescription – Colin Jan 23 '13 at 14:40
  • I would love an answer on this. the internet is void and I have a table with 100 million rows I *need* to convert. – JoeBrockhaus Jul 15 '13 at 14:45
  • Although nothing official, there is this (which was posted a day after this so maybe same person asking?): http://social.msdn.microsoft.com/Forums/en-US/ac799621-56cf-4a47-a7bc-2eb4b1cf2aba/convert-text-to-varcharmax – JoeBrockhaus Jul 15 '13 at 15:14

1 Answers1

0

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?

Community
  • 1
  • 1
David
  • 1,074
  • 11
  • 12