Thanks, but tried that and still the same Cheers
On Mon, 25 Mar 2019 at 13:37, Greg Low <[email protected]> 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:* [email protected] on behalf of Tom P < > [email protected]> > *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 <[email protected]> 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:* [email protected] on behalf of Tom P < >> [email protected]> >> *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, <[email protected]> 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:* [email protected] <[email protected]> *On >>> Behalf Of *Tom P >>> *Sent:* Thursday, 7 March 2019 1:20 PM >>> *To:* ozDotNet <[email protected]> >>> *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, <[email protected]> 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:* [email protected] <[email protected]> *On >>> Behalf Of *Tom P >>> *Sent:* Thursday, 7 March 2019 11:53 AM >>> *To:* ozDotNet <[email protected]> >>> *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, <[email protected]> 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:* [email protected] <[email protected]> *On >>> Behalf Of *Tom P >>> *Sent:* Thursday, 7 March 2019 11:40 AM >>> *To:* ozDotNet <[email protected]> >>> *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 >> >
