Hello, Have you tried copying the data in place, by creating a temporary column to contain the data while you drop and recreate your column?
Davy. *... .. / .... --- -.-. / .-.. . --. . .-. . / ... -.-. .. ... / -. .. -- .. ..- -- / . .-. ..- -.. .. - .. --- -. .. ... / .... .- -... . ... .-.-.-* On Wed, Mar 27, 2019 at 5:44 AM Tom P <tompbi...@gmail.com> wrote: > A further update: moving a specific text column from the old table seems > to cause the LOB data to be present on the new table. The specific text > column holds callstack values. > > > On Wed, 27 Mar 2019 at 15:34, Tom P <tompbi...@gmail.com> wrote: > >> Still problematic and with LOB data. >> >> I ran another test and by not copying the text values from the old table >> to the new it successfully works and no LOB data. Including the text >> columns from the old table in the copy seems to be where the problem is. I >> even tried casting during the move but no luck. >> >> On Wed, 27 Mar 2019 at 14:48, <g...@greglow.com> wrote: >> >>> Sorry, does that mean it ran the same, or also has the LOB data >>> separated? >>> >>> >>> >>> >>> >>> Regards, >>> >>> >>> >>> Greg >>> >>> >>> >>> Dr Greg Low >>> >>> >>> >>> 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 >>> fax >>> >>> SQL Down Under | Web: www.sqldownunder.com >>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274&sdata=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D&reserved=0> >>> |http://greglow.me >>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278&sdata=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D&reserved=0> >>> >>> >>> >>> *From:* ozdotnet-boun...@ozdotnet.com <ozdotnet-boun...@ozdotnet.com> *On >>> Behalf Of *Tom P >>> *Sent:* Wednesday, 27 March 2019 2:46 PM >>> *To:* ozDotNet <ozdotnet@ozdotnet.com> >>> *Subject:* Re: [OT] Sql Server writes causing contention >>> >>> >>> >>> Same result unfortunately >>> >>> >>> >>> Cheers >>> >>> >>> >>> On Wed, 27 Mar 2019 at 12:54, <g...@greglow.com> wrote: >>> >>> Can you change the object names in the script below and see if it >>> returns the same results on that system? (At least to isolate something >>> system-related as a starting point) >>> >>> >>> >>> Regards, >>> >>> >>> >>> Greg >>> >>> >>> >>> Dr Greg Low >>> >>> >>> >>> 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 >>> fax >>> >>> SQL Down Under | Web: www.sqldownunder.com >>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274&sdata=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D&reserved=0> >>> |http://greglow.me >>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278&sdata=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D&reserved=0> >>> >>> >>> >>> *From:* ozdotnet-boun...@ozdotnet.com <ozdotnet-boun...@ozdotnet.com> *On >>> Behalf Of *Tom P >>> *Sent:* Wednesday, 27 March 2019 10:20 AM >>> *To:* ozDotNet <ozdotnet@ozdotnet.com> >>> *Subject:* Re: [OT] Sql Server writes causing contention >>> >>> >>> >>> Hi Greg >>> >>> >>> >>> When I run this on my local Sql install and insert dummy data all works >>> as expected like you. However on the actual server when I MOVE the data >>> from the original table to the new one then the problems comes up and the >>> new table has LOB_DATA. >>> >>> >>> >>> I am not sure where to go from here. Any ideas? >>> >>> >>> >>> Cheers >>> >>> >>> >>> On Tue, 26 Mar 2019 at 09:49, <g...@greglow.com> wrote: >>> >>> Hi Tom, >>> >>> >>> >>> Got to try it and now puzzled. If I run this: >>> >>> >>> >>> CREATE TABLE [dbo].[Log_New]( >>> >>> [LogID] [bigint] IDENTITY(1,1) NOT NULL, >>> >>> [DateTime] [datetime] NOT NULL, >>> >>> [Type] [varchar](30) NOT NULL, >>> >>> [Message] [varchar](max) NULL, >>> >>> CONSTRAINT [PK_Log_New] PRIMARY KEY CLUSTERED >>> >>> ( >>> >>> [LogID] ASC >>> >>> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = >>> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] >>> >>> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] >>> >>> GO >>> >>> >>> >>> ALTER TABLE [dbo].[Log_New] ADD CONSTRAINT [DF_Log_New_DateTime] DEFAULT >>> (getdate()) FOR [DateTime] >>> >>> GO >>> >>> >>> >>> SELECT OBJECT_NAME(object_id), >>> >>> alloc_unit_type_desc, >>> >>> page_count >>> >>> FROM sys.dm_db_index_physical_stats >>> >>> (DB_ID(), NULL, NULL, NULL, 'DETAILED') >>> >>> WHERE OBJECT_NAME(object_id) = N'Log_New'; >>> >>> >>> >>> >>> >>> What I see is this: >>> >>> >>> >>> >>> >>> If I add 10,000,000 rows (10 million rows), still the same: >>> >>> >>> >>> WITH Counts >>> >>> AS >>> >>> ( >>> >>> SELECT TOP(10000000) ROW_NUMBER() OVER(ORDER BY (ac1.object_id)) AS >>> RowNumber >>> >>> FROM sys.all_columns AS ac1 >>> >>> CROSS JOIN sys.all_columns AS ac2 >>> >>> ) >>> >>> INSERT dbo.Log_New (Type, Message) >>> >>> SELECT CAST(RowNumber AS varchar(20)), >>> >>> 'Messsage number ' + CAST(RowNumber AS varchar(20)) >>> >>> FROM Counts >>> >>> ORDER BY RowNumber; >>> >>> >>> >>> >>> >>> And even if I add a long value: >>> >>> >>> >>> INSERT dbo.Log_New (Type, Message) >>> >>> VALUES ('Long Row', REPLICATE('Hello', 10000)); >>> >>> >>> >>> Still the same: >>> >>> >>> >>> >>> >>> Am struggling to think what could be different at your end. >>> >>> >>> >>> What are you seeing? >>> >>> >>> >>> Regards, >>> >>> >>> >>> Greg >>> >>> >>> >>> Dr Greg Low >>> >>> >>> >>> 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 >>> fax >>> >>> SQL Down Under | Web: www.sqldownunder.com >>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274&sdata=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D&reserved=0> >>> |http://greglow.me >>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278&sdata=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D&reserved=0> >>> >>> >>> >>> *From:* ozdotnet-boun...@ozdotnet.com <ozdotnet-boun...@ozdotnet.com> *On >>> Behalf Of *Tom P >>> *Sent:* Monday, 25 March 2019 2:48 PM >>> *To:* ozDotNet <ozdotnet@ozdotnet.com> >>> *Subject:* Re: [OT] Sql Server writes causing contention >>> >>> >>> >>> Thanks, but tried that and still the same >>> >>> >>> >>> Cheers >>> >>> >>> >>> On Mon, 25 Mar 2019 at 13:37, Greg Low <g...@greglow.com> wrote: >>> >>> Am traveling so can’t test but try removing the TEXTIMAGE ON clause >>> >>> >>> >>> Regards, >>> >>> >>> >>> Greg >>> >>> >>> >>> Dr Greg Low >>> >>> 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 >>> fax >>> >>> SQL Down Under | Web: www.sqldownunder.com >>> >>> >>> ------------------------------ >>> >>> *From:* ozdotnet-boun...@ozdotnet.com on behalf of Tom P < >>> tompbi...@gmail.com> >>> *Sent:* Monday, March 25, 2019 1:04 pm >>> *To:* ozDotNet >>> *Subject:* Re: [OT] Sql Server writes causing contention >>> >>> >>> >>> I only changed the data type from text to varchar(max) in the table and >>> removed the FILLFACTOR 90. I then selected all the rows from the old table >>> and inserted into the new one. >>> >>> >>> >>> CREATE TABLE [dbo].[Log_New]( >>> >>> [LogID] [bigint] IDENTITY(1,1) NOT NULL, >>> >>> [DateTime] [datetime] NOT NULL, >>> >>> [Type] [varchar](30) NOT NULL, >>> >>> [Message] [varchar](max) NULL, >>> >>> CONSTRAINT [PK_Log_New] PRIMARY KEY CLUSTERED >>> >>> ( >>> >>> [LogID] ASC >>> >>> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = >>> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] >>> >>> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] >>> >>> GO >>> >>> >>> >>> ALTER TABLE [dbo].[Log_New] ADD CONSTRAINT [DF_Log_New_DateTime] DEFAULT >>> (getdate()) FOR [DateTime] >>> >>> GO >>> >>> Cheers >>> >>> >>> >>> On Mon, 25 Mar 2019 at 12:49, Greg Low <g...@greglow.com> wrote: >>> >>> The number of rows is unrelated. Can you show the script you used? In >>> particular, how you recreated the table. I presume you didn’t have the LOB >>> options in that? If you just create the table with the new data types and >>> don’t specify anything else, it defaults to in-row storage. >>> >>> >>> >>> Regards, >>> >>> >>> >>> Greg >>> >>> >>> >>> Dr Greg Low >>> >>> SQL Down Under Pty Ltd >>> >>> Mobile: +61419201410 Office: 1300775775 >>> >>> >>> ------------------------------ >>> >>> *From:* ozdotnet-boun...@ozdotnet.com on behalf of Tom P < >>> tompbi...@gmail.com> >>> *Sent:* Monday, March 25, 2019 10:47 am >>> *To:* ozDotNet >>> *Subject:* Re: [OT] Sql Server writes causing contention >>> >>> >>> >>> Hi Greg >>> >>> >>> >>> I have done as you suggested below but it seems the new table also has >>> LOB_DATA and not only in row data after moving all the data. >>> >>> >>> >>> There are 5mil records which I suspect is the issue. Any thoughts? >>> >>> >>> >>> Cheers >>> >>> >>> >>> >>> >>> On Thu, 7 Mar 2019 at 14:10, <g...@greglow.com> wrote: >>> >>> It’s easy to see. Try executing this: >>> >>> >>> >>> USE tempdb; >>> >>> GO >>> >>> >>> >>> DROP TABLE IF EXISTS dbo.TomText; >>> >>> GO >>> >>> DROP TABLE IF EXISTS dbo.TomVarCharMax; >>> >>> GO >>> >>> >>> >>> CREATE TABLE dbo.TomText >>> >>> ( >>> >>> TomTextID bigint identity(1,1) NOT NULL, >>> >>> [Message] text NOT NULL >>> >>> ); >>> >>> GO >>> >>> >>> >>> CREATE TABLE dbo.TomVarCharMax >>> >>> ( >>> >>> TomVarCharMaxID bigint identity(1,1) NOT NULL, >>> >>> [Message] varchar(max) NOT NULL >>> >>> ); >>> >>> GO >>> >>> >>> >>> INSERT dbo.TomText ([Message]) >>> >>> SELECT TOP(10000) [name] >>> >>> FROM sys.all_columns; >>> >>> GO >>> >>> >>> >>> INSERT dbo.TomVarCharMax ([Message]) >>> >>> SELECT TOP(10000) [name] >>> >>> FROM sys.all_columns; >>> >>> GO >>> >>> >>> >>> SELECT OBJECT_NAME(object_id), >>> >>> alloc_unit_type_desc, >>> >>> page_count >>> >>> FROM sys.dm_db_index_physical_stats >>> >>> (DB_ID(), NULL, NULL, NULL, 'DETAILED') >>> >>> WHERE OBJECT_NAME(object_id) IN (N'TomText', N'TomVarCharMax'); >>> >>> >>> >>> It’ll return something like this: >>> >>> >>> >>> >>> >>> Note the difference. >>> >>> >>> >>> Also note that you can’t fix it by just changing the datatype. Rename >>> the table, create a new one, and move the data over. >>> >>> >>> >>> Another question: why 90 as a FILLFACTOR if you’re only writing to it in >>> order? >>> >>> >>> >>> Regards, >>> >>> >>> >>> Greg >>> >>> >>> >>> Dr Greg Low >>> >>> >>> >>> 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 >>> fax >>> >>> SQL Down Under | Web: www.sqldownunder.com >>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274&sdata=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D&reserved=0> >>> |http://greglow.me >>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278&sdata=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D&reserved=0> >>> >>> >>> >>> *From:* ozdotnet-boun...@ozdotnet.com <ozdotnet-boun...@ozdotnet.com> *On >>> Behalf Of *Tom P >>> *Sent:* Thursday, 7 March 2019 1:20 PM >>> *To:* ozDotNet <ozdotnet@ozdotnet.com> >>> *Subject:* Re: [OT] Sql Server writes causing contention >>> >>> >>> >>> Very interesting about the storage of the text type. Do you have a >>> Microsoft link by any chance which says this? >>> >>> >>> >>> Create script: >>> >>> >>> >>> CREATE TABLE [dbo].[Log]( >>> >>> [LogID] [bigint] IDENTITY(1,1) NOT NULL, >>> >>> [DateTime] [datetime] NOT NULL, >>> >>> [Type] [varchar](30) NOT NULL, >>> >>> [Message] [text] NULL, >>> >>> CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED >>> >>> ( >>> >>> [LogID] ASC >>> >>> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = >>> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON >>> [PRIMARY] >>> >>> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] >>> >>> GO >>> >>> >>> >>> ALTER TABLE [dbo].[Log] ADD CONSTRAINT [DF_Log_DateTime] DEFAULT >>> (getdate()) FOR [DateTime] >>> >>> GO >>> >>> >>> >>> Cheers >>> >>> Tom >>> >>> >>> >>> On Thu, 7 Mar 2019 at 11:58, <g...@greglow.com> wrote: >>> >>> Might get you to post the actual table schema though, so we can check >>> it. Just script the whole table, indexes, etc. >>> >>> >>> >>> First comment is that you shouldn’t be using text at all. Apart from the >>> fact that it was deprecated back in 2005, the data for that is (by default) >>> stored out of row, not with the rest of the data. That’s never quick. >>> >>> >>> >>> Regards, >>> >>> >>> >>> Greg >>> >>> >>> >>> Dr Greg Low >>> >>> >>> >>> 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 >>> fax >>> >>> SQL Down Under | Web: www.sqldownunder.com >>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274&sdata=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D&reserved=0> >>> |http://greglow.me >>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278&sdata=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D&reserved=0> >>> >>> >>> >>> *From:* ozdotnet-boun...@ozdotnet.com <ozdotnet-boun...@ozdotnet.com> *On >>> Behalf Of *Tom P >>> *Sent:* Thursday, 7 March 2019 11:53 AM >>> *To:* ozDotNet <ozdotnet@ozdotnet.com> >>> *Subject:* Re: [OT] Sql Server writes causing contention >>> >>> >>> >>> Logid bigint >>> >>> Datetime >>> >>> Type varchar >>> >>> Message text >>> >>> >>> >>> I don’t have the stats yet but some of the apps are services that run >>> batches of things and have busy periods during the day where they could >>> write maybe 10,000 thousand rows. Possibly several times per second maybe. >>> >>> >>> >>> >>> >>> On Thu, 7 Mar 2019 at 11:44, <g...@greglow.com> wrote: >>> >>> Hi Tom, >>> >>> >>> >>> Can you post the table schema? >>> >>> >>> >>> What rate are the writes coming in at? >>> >>> >>> >>> Regards, >>> >>> >>> >>> Greg >>> >>> >>> >>> Dr Greg Low >>> >>> >>> >>> 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 >>> fax >>> >>> SQL Down Under | Web: www.sqldownunder.com >>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274&sdata=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D&reserved=0> >>> |http://greglow.me >>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278&sdata=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D&reserved=0> >>> >>> >>> >>> *From:* ozdotnet-boun...@ozdotnet.com <ozdotnet-boun...@ozdotnet.com> *On >>> Behalf Of *Tom P >>> *Sent:* Thursday, 7 March 2019 11:40 AM >>> *To:* ozDotNet <ozdotnet@ozdotnet.com> >>> *Subject:* [OT] Sql Server writes causing contention >>> >>> >>> >>> Hi folks >>> >>> >>> >>> *Scenario:* >>> >>> Multiple apps writing to a Log table in the same Sql Server database. >>> Some apps hitting the table A LOT. No app reads from this table. No updates >>> also. Just plain writes adding new rows. >>> >>> >>> >>> *Problem:* >>> >>> Contention causing timeout errors. >>> >>> >>> >>> Can anybody explain why this could be case? >>> >>> >>> >>> Cheers >>> >>> Tom >>> >>> -- >>> >>> Thanks >>> >>> Tom >>> >>> -- >>> >>> Thanks >>> >>> Tom >>> >>> -- >>> >>> Thanks >>> >>> Tom >>> >>> -- >>> >>> Thanks >>> >>> Tom >>> >>> -- >>> >>> Thanks >>> >>> Tom >>> >> -- >> Thanks >> Tom >> > -- > Thanks > Tom >
