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: <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> www.sqldownunder.com | <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> http://greglow.me 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 <mailto: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: <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> www.sqldownunder.com | <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> http://greglow.me From: ozdotnet-boun...@ozdotnet.com <mailto:ozdotnet-boun...@ozdotnet.com> <ozdotnet-boun...@ozdotnet.com <mailto:ozdotnet-boun...@ozdotnet.com> > On Behalf Of Tom P Sent: Monday, 25 March 2019 2:48 PM To: ozDotNet <ozdotnet@ozdotnet.com <mailto: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 <mailto: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 <http://www.sqldownunder.com> _____ From: ozdotnet-boun...@ozdotnet.com <mailto:ozdotnet-boun...@ozdotnet.com> on behalf of Tom P <tompbi...@gmail.com <mailto: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 <mailto: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 <mailto:ozdotnet-boun...@ozdotnet.com> on behalf of Tom P <tompbi...@gmail.com <mailto: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 <mailto: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: <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> www.sqldownunder.com | <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> http://greglow.me From: ozdotnet-boun...@ozdotnet.com <mailto:ozdotnet-boun...@ozdotnet.com> <ozdotnet-boun...@ozdotnet.com <mailto:ozdotnet-boun...@ozdotnet.com> > On Behalf Of Tom P Sent: Thursday, 7 March 2019 1:20 PM To: ozDotNet <ozdotnet@ozdotnet.com <mailto: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 <mailto: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: <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> www.sqldownunder.com | <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> http://greglow.me From: ozdotnet-boun...@ozdotnet.com <mailto:ozdotnet-boun...@ozdotnet.com> <ozdotnet-boun...@ozdotnet.com <mailto:ozdotnet-boun...@ozdotnet.com> > On Behalf Of Tom P Sent: Thursday, 7 March 2019 11:53 AM To: ozDotNet <ozdotnet@ozdotnet.com <mailto: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 <mailto: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: <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> www.sqldownunder.com | <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> http://greglow.me From: ozdotnet-boun...@ozdotnet.com <mailto:ozdotnet-boun...@ozdotnet.com> <ozdotnet-boun...@ozdotnet.com <mailto:ozdotnet-boun...@ozdotnet.com> > On Behalf Of Tom P Sent: Thursday, 7 March 2019 11:40 AM To: ozDotNet <ozdotnet@ozdotnet.com <mailto: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
