Fantastic! Thanks Greg Regards, Tom
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
