Re: [OT] Sql Server writes causing contention

[Tom P]

Fantastic! Thanks Greg

Regards,
Tom

On Thu, 7 Mar 2019 at 14:10,  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(1) [name]
>
> FROM sys.all_columns;
>
> GO
>
>
>
> INSERT dbo.TomVarCharMax ([Message])
>
> SELECT TOP(1) [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
> 
>  |http://greglow.me
> 
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com  *On
> Behalf Of *Tom P
> *Sent:* Thursday, 7 March 2019 1:20 PM
> *To:* ozDotNet 
> *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,  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
> 
>  |http://greglow.me
> 
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com  *On
> Behalf Of *Tom P
> *Sent:* Thursday, 7 March 2019 11:53 AM
> *To:* ozDotNet 
> *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,  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 

RE: [OT] Sql Server writes causing contention

[greg]

Here’s a blog post:

 

https://blog.greglow.com/2019/02/26/sql-text-vs-varcharmax-in-row-vs-out-of-row-storage/

 

 

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://greglow.me

 

From: g...@greglow.com  
Sent: Thursday, 7 March 2019 2:10 PM
To: 'ozDotNet' 
Subject: RE: [OT] Sql Server writes causing contention

 

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(1) [name]

FROM sys.all_columns;

GO

 

INSERT dbo.TomVarCharMax ([Message])

SELECT TOP(1) [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 | 

 http://greglow.me

 

From: ozdotnet-boun...@ozdotnet.com   
mailto:ozdotnet-boun...@ozdotnet.com> > On 
Behalf Of Tom P
Sent: Thursday, 7 March 2019 1:20 PM
To: ozDotNet 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, 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:  

 www.sqldownunder.com | 

RE: [OT] Sql Server writes causing contention

[greg]

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(1) [name]

FROM sys.all_columns;

GO

 

INSERT dbo.TomVarCharMax ([Message])

SELECT TOP(1) [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 | 

 http://greglow.me

 

From: ozdotnet-boun...@ozdotnet.com  On Behalf 
Of Tom P
Sent: Thursday, 7 March 2019 1:20 PM
To: ozDotNet 
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, 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:  

 www.sqldownunder.com | 

 http://greglow.me

 

From: ozdotnet-boun...@ozdotnet.com   
mailto:ozdotnet-boun...@ozdotnet.com> > On 
Behalf Of Tom P
Sent: Thursday, 7 March 2019 11:53 AM
To: ozDotNet 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, 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:  

Re: [OT] Sql Server writes causing contention

[Tom P]

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,  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
> 
>  |http://greglow.me
> 
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com  *On
> Behalf Of *Tom P
> *Sent:* Thursday, 7 March 2019 11:53 AM
> *To:* ozDotNet 
> *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,  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
> 
>  |http://greglow.me
> 
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com  *On
> Behalf Of *Tom P
> *Sent:* Thursday, 7 March 2019 11:40 AM
> *To:* ozDotNet 
> *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
>

RE: [OT] Sql Server writes causing contention

[greg]

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 | 

 http://greglow.me

 

From: ozdotnet-boun...@ozdotnet.com  On Behalf 
Of Tom P
Sent: Thursday, 7 March 2019 11:53 AM
To: ozDotNet 
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, 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:  

 www.sqldownunder.com | 

 http://greglow.me

 

From: ozdotnet-boun...@ozdotnet.com   
mailto:ozdotnet-boun...@ozdotnet.com> > On 
Behalf Of Tom P
Sent: Thursday, 7 March 2019 11:40 AM
To: ozDotNet 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

Re: [OT] Sql Server writes causing contention

[Tom P]

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,  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
> 
>  |http://greglow.me
> 
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com  *On
> Behalf Of *Tom P
> *Sent:* Thursday, 7 March 2019 11:40 AM
> *To:* ozDotNet 
> *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

RE: [OT] Sql Server writes causing contention

[greg]

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 | 

 http://greglow.me

 

From: ozdotnet-boun...@ozdotnet.com  On Behalf 
Of Tom P
Sent: Thursday, 7 March 2019 11:40 AM
To: ozDotNet 
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

[OT] Sql Server writes causing contention

[Tom P]

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