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> 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: 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:* Monday, 25 March 2019 2:48 PM
> *To:* ozDotNet <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> 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:* ozdotnet-boun...@ozdotnet.com on behalf of Tom P <
> 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> 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
>
> --
Thanks
Tom
