Async Await.

[David Rhys Jones]

Hello all,

I've started a new post, and one of the applications here uses Async Await
for nearly every method call, even for simple calls that just create an
object and return it.

Are there any pitfalls for using this many await methods in an application?

Davy.



*... .. /  --- -.-. / .-.. . --. . .-. . / ... -.-. .. ... / -. .. --
.. ..- -- / . .-. ..- -.. .. - .. --- -. .. ... /  .- -... . ... .-.-.-*

Re: Async Await.

[Greg Keogh]

> I've started a new post, and one of the applications here uses Async Await
> for nearly every method call, even for simple calls that just create an
> object and return it.
>

How on earth is that sort of thing coded? How are intrinsically synchronous
methods forcibly turned into async ones? Is it like this?... (I'm just
guessing)

var foo = await Task.Run(() => return new Foo());

*Greg K*

>

Re: Async Await.

[Preet Sangha]

*forking* hell!

regards,
Preet, in Auckland NZ



On Tue, 26 Mar 2019 at 22:52, Greg Keogh  wrote:

>
> I've started a new post, and one of the applications here uses Async Await
>> for nearly every method call, even for simple calls that just create an
>> object and return it.
>>
>
> How on earth is that sort of thing coded? How are intrinsically
> synchronous methods forcibly turned into async ones? Is it like this?...
> (I'm just guessing)
>
> var foo = await Task.Run(() => return new Foo());
>
> *Greg K*
>
>>

Re: Async Await.

[David Rhys Jones]

Hi Greg,

This is pretty typical for the entire project.
var legalEntity = await CreateLegalEntityObjectAsync(...);

var billingAccount = await CreateBillingAccountObjectAsync(...);
var billingAccountUid = billingAccount.UserName;
var billingAccountTaxServiceAddressPcode =
billingAccount.InternalView.TaxServiceAddressPcode;

var primaryGroup = await CreatePrimaryGroupObjectAsync();


Davy

*... .. /  --- -.-. / .-.. . --. . .-. . / ... -.-. .. ... / -. .. --
.. ..- -- / . .-. ..- -.. .. - .. --- -. .. ... /  .- -... . ... .-.-.-*



On Tue, Mar 26, 2019 at 11:10 AM Preet Sangha  wrote:

> *forking* hell!
>
> regards,
> Preet, in Auckland NZ
>
>
>
> On Tue, 26 Mar 2019 at 22:52, Greg Keogh  wrote:
>
>>
>> I've started a new post, and one of the applications here uses Async
>>> Await for nearly every method call, even for simple calls that just create
>>> an object and return it.
>>>
>>
>> How on earth is that sort of thing coded? How are intrinsically
>> synchronous methods forcibly turned into async ones? Is it like this?...
>> (I'm just guessing)
>>
>> var foo = await Task.Run(() => return new Foo());
>>
>> *Greg K*
>>
>>>

Re: Async Await.

[David Gardiner]

Presumably inside these methods are other calls to additional async
methods?

This sounds like an example of how "viral" the async/await stuff can be, in
that once you call an async method at the lowest level, everything further
up ends up needing to become "async/awaited" too.

David

On Tue, 26 Mar 2019 at 06:45, David Rhys Jones  wrote:

> Hi Greg,
>
> This is pretty typical for the entire project.
> var legalEntity = await CreateLegalEntityObjectAsync(...);
>
> var billingAccount = await CreateBillingAccountObjectAsync(...);
> var billingAccountUid = billingAccount.UserName;
> var billingAccountTaxServiceAddressPcode =
> billingAccount.InternalView.TaxServiceAddressPcode;
>
> var primaryGroup = await CreatePrimaryGroupObjectAsync();
>
>
> Davy
>
> *... .. /  --- -.-. / .-.. . --. . .-. . / ... -.-. .. ... / -. .. --
> .. ..- -- / . .-. ..- -.. .. - .. --- -. .. ... /  .- -... . ... .-.-.-*
>
>
>
> On Tue, Mar 26, 2019 at 11:10 AM Preet Sangha 
> wrote:
>
>> *forking* hell!
>>
>> regards,
>> Preet, in Auckland NZ
>>
>>
>>
>> On Tue, 26 Mar 2019 at 22:52, Greg Keogh  wrote:
>>
>>>
>>> I've started a new post, and one of the applications here uses Async
 Await for nearly every method call, even for simple calls that just create
 an object and return it.

>>>
>>> How on earth is that sort of thing coded? How are intrinsically
>>> synchronous methods forcibly turned into async ones? Is it like this?...
>>> (I'm just guessing)
>>>
>>> var foo = await Task.Run(() => return new Foo());
>>>
>>> *Greg K*
>>>

Re: Async Await.

[David Rhys Jones]

Yes David, there are whole chains of method calls, all awaiting for
something to complete, the only part that is not async is the database call!



*... .. /  --- -.-. / .-.. . --. . .-. . / ... -.-. .. ... / -. .. --
.. ..- -- / . .-. ..- -.. .. - .. --- -. .. ... /  .- -... . ... .-.-.-*



On Tue, Mar 26, 2019 at 2:34 PM David Gardiner 
wrote:

> Presumably inside these methods are other calls to additional async
> methods?
>
> This sounds like an example of how "viral" the async/await stuff can be,
> in that once you call an async method at the lowest level, everything
> further up ends up needing to become "async/awaited" too.
>
> David
>
> On Tue, 26 Mar 2019 at 06:45, David Rhys Jones 
> wrote:
>
>> Hi Greg,
>>
>> This is pretty typical for the entire project.
>> var legalEntity = await CreateLegalEntityObjectAsync(...);
>>
>> var billingAccount = await CreateBillingAccountObjectAsync(...);
>> var billingAccountUid = billingAccount.UserName;
>> var billingAccountTaxServiceAddressPcode =
>> billingAccount.InternalView.TaxServiceAddressPcode;
>>
>> var primaryGroup = await CreatePrimaryGroupObjectAsync();
>>
>>
>> Davy
>>
>> *... .. /  --- -.-. / .-.. . --. . .-. . / ... -.-. .. ... / -. .. --
>> .. ..- -- / . .-. ..- -.. .. - .. --- -. .. ... /  .- -... . ... .-.-.-*
>>
>>
>>
>> On Tue, Mar 26, 2019 at 11:10 AM Preet Sangha 
>> wrote:
>>
>>> *forking* hell!
>>>
>>> regards,
>>> Preet, in Auckland NZ
>>>
>>>
>>>
>>> On Tue, 26 Mar 2019 at 22:52, Greg Keogh  wrote:
>>>

 I've started a new post, and one of the applications here uses Async
> Await for nearly every method call, even for simple calls that just create
> an object and return it.
>

 How on earth is that sort of thing coded? How are intrinsically
 synchronous methods forcibly turned into async ones? Is it like this?...
 (I'm just guessing)

 var foo = await Task.Run(() => return new Foo());

 *Greg K*

>

Re: Async Await.

[Greg Keogh]

> This is pretty typical for the entire project.
> var legalEntity = await CreateLegalEntityObjectAsync(...);
> var billingAccount = await CreateBillingAccountObjectAsync(...);
>

But what's inside all the awaited methods? Are they actually doing anything
asynchronously (web service calls, overlapped file IO)? Got the source code
or look in ILSpy? --* GK*

>

Re: Async Await.

[David Rhys Jones]

Some call APIs  but the vast majority are just mapping calls. Changing one
object into another.

Davy

On Tue, 26 Mar 2019, 21:51 Greg Keogh,  wrote:

>
> This is pretty typical for the entire project.
>> var legalEntity = await CreateLegalEntityObjectAsync(...);
>> var billingAccount = await CreateBillingAccountObjectAsync(...);
>>
>
> But what's inside all the awaited methods? Are they actually doing
> anything asynchronously (web service calls, overlapped file IO)? Got the
> source code or look in ILSpy? --* GK*
>
>>

Re: [OT] Sql Server writes causing contention

[Tom P]

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,  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(1000) 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', 1));
>
>
>
> 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
> 
>  |http://greglow.me
> 
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com  *On
> Behalf Of *Tom P
> *Sent:* Monday, 25 March 2019 2:48 PM
> *To:* ozDotNet 
> *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  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  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] 

Re: Async Await.

[David Gardiner]

If the bottom-level methods don't contain any async code, then I'd expect
the compiler would be generating warnings about marking a method as async
(unless those warnings are being suppressed)

I wonder if the compiler just discards the async bit in that case?

On Tue, 26 Mar 2019 at 16:57, David Rhys Jones  wrote:

> Some call APIs  but the vast majority are just mapping calls. Changing one
> object into another.
>
> Davy
>
> On Tue, 26 Mar 2019, 21:51 Greg Keogh,  wrote:
>
>>
>> This is pretty typical for the entire project.
>>> var legalEntity = await CreateLegalEntityObjectAsync(...);
>>> var billingAccount = await CreateBillingAccountObjectAsync(...);
>>>
>>
>> But what's inside all the awaited methods? Are they actually doing
>> anything asynchronously (web service calls, overlapped file IO)? Got the
>> source code or look in ILSpy? --* GK*
>>
>>>

RE: [OT] Sql Server writes causing contention

[greg]

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:  

 www.sqldownunder.com | 

 http://greglow.me

 

From: ozdotnet-boun...@ozdotnet.com  On Behalf 
Of Tom P
Sent: Wednesday, 27 March 2019 10:20 AM
To: ozDotNet 
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, 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(1000) 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', 1));

 

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 | 

 http://greglow.me

 

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

 

  _  

From: ozdotnet-boun...@ozdotnet.com   on 
behalf of Tom P 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

Re: [OT] Sql Server writes causing contention

[Tom P]

Same result unfortunately

Cheers

On Wed, 27 Mar 2019 at 12:54,  wrote:

> 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: www.sqldownunder.com
> 
>  |http://greglow.me
> 
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com  *On
> Behalf Of *Tom P
> *Sent:* Wednesday, 27 March 2019 10:20 AM
> *To:* ozDotNet 
> *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,  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(1000) 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', 1));
>
>
>
> 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
> 
>  |http://greglow.me
> 
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com  *On
> Behalf Of *Tom P
> *Sent:* Monday, 25 March 2019 2:48 PM
> *To:* ozDotNet 
> *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  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
> an

Re: [OT] Sql Server writes causing contention

[Tom P]

Still problematic and with LOB data.

I ran another test and by not copying the text values from the old table to
the new it successfully works and no LOB data. Including the text columns
from the old table in the copy seems to be where the problem is. I even
tried casting during the move but no luck.

On Wed, 27 Mar 2019 at 14:48,  wrote:

> Sorry, does that mean it ran the same, or also has the LOB data separated?
>
>
>
>
>
> 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:* Wednesday, 27 March 2019 2:46 PM
> *To:* ozDotNet 
> *Subject:* Re: [OT] Sql Server writes causing contention
>
>
>
> Same result unfortunately
>
>
>
> Cheers
>
>
>
> On Wed, 27 Mar 2019 at 12:54,  wrote:
>
> 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: www.sqldownunder.com
> 
>  |http://greglow.me
> 
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com  *On
> Behalf Of *Tom P
> *Sent:* Wednesday, 27 March 2019 10:20 AM
> *To:* ozDotNet 
> *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,  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(1000) 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', 1));
>
>
>
> 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
> 

Re: [OT] Sql Server writes causing contention

[Tom P]

A further update: moving a specific text column from the old table seems to
cause the LOB data to be present on the new table. The specific text column
holds callstack values.


On Wed, 27 Mar 2019 at 15:34, Tom P  wrote:

> Still problematic and with LOB data.
>
> I ran another test and by not copying the text values from the old table
> to the new it successfully works and no LOB data. Including the text
> columns from the old table in the copy seems to be where the problem is. I
> even tried casting during the move but no luck.
>
> On Wed, 27 Mar 2019 at 14:48,  wrote:
>
>> Sorry, does that mean it ran the same, or also has the LOB data separated?
>>
>>
>>
>>
>>
>> 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:* Wednesday, 27 March 2019 2:46 PM
>> *To:* ozDotNet 
>> *Subject:* Re: [OT] Sql Server writes causing contention
>>
>>
>>
>> Same result unfortunately
>>
>>
>>
>> Cheers
>>
>>
>>
>> On Wed, 27 Mar 2019 at 12:54,  wrote:
>>
>> 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: www.sqldownunder.com
>> 
>>  |http://greglow.me
>> 
>>
>>
>>
>> *From:* ozdotnet-boun...@ozdotnet.com  *On
>> Behalf Of *Tom P
>> *Sent:* Wednesday, 27 March 2019 10:20 AM
>> *To:* ozDotNet 
>> *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,  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(1000) 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', 1));
>>
>>
>