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 >
