I’ve tried and tried to replicate locally so I can provide such a script but I am unable to reproduce it. It only occurs on our main server with the data already present in the Log table with the text columns.
As you suggested all I do is create a new table with varchar(max) columns and run an INSERT INTO NewLog with column values from the OldLog. If I manually copy and paste the column values from the main server Log table into the new table within SSMS there is no LOB_DATA but if I transfer the data using the INSERT INTO ... SELECT then there is LOB_DATA. Even using an intermediary table sitting between the old log and new log tables doesn’t help. Totally baffled. Cheers On Thu, 28 Mar 2019 at 10:20, <g...@greglow.com> wrote: > Can you create a sample script that demonstrates what you’re 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:* Thursday, 28 March 2019 5:41 AM > *To:* ozDotNet <ozdotnet@ozdotnet.com> > *Subject:* Re: [OT] Sql Server writes causing contention > > > > Yes. Also tried casting in the selecting from text to varchar(max) but no > luck. > > > > Cheers > > > > On Wed, 27 Mar 2019 at 15:44, <g...@greglow.com> wrote: > > Is it just an INSERT..SELECT for the copy? > > > > 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 3:34 PM > *To:* ozDotNet <ozdotnet@ozdotnet.com> > *Subject:* Re: [OT] Sql Server writes causing contention > > > > 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 > -- Thanks Tom