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 >
