subject:"Re\: \[OT\] Sql Server writes causing contention"

Re: [OT] Sql Server writes causing contention

2019-03-27 Thread Tom P

I’ve tried and tried to replicate locally so I can provide such a script
but I am unable to reproduce it. It only occurs on our main server with the
data already present in the Log table with the text columns.

As you suggested all I do is create a new table with varchar(max) columns
and run an INSERT INTO NewLog with column values from the OldLog.

If I manually copy and paste the column values from the main server Log
table into the new table within SSMS there is no LOB_DATA but if I transfer
the data using the INSERT INTO ... SELECT then there is LOB_DATA.

Even using an intermediary table sitting between the old log and new log
tables doesn’t help.

Totally baffled.

Cheers


On Thu, 28 Mar 2019 at 10:20,  wrote:

> Can you create a sample script that demonstrates what you’re 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=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com  *On
> Behalf Of *Tom P
> *Sent:* Thursday, 28 March 2019 5:41 AM
> *To:* ozDotNet 
> *Subject:* Re: [OT] Sql Server writes causing contention
>
>
>
> Yes. Also tried casting in the selecting from text to varchar(max) but no
> luck.
>
>
>
> Cheers
>
>
>
> On Wed, 27 Mar 2019 at 15:44,  wrote:
>
> Is it just an INSERT..SELECT for the copy?
>
>
>
> 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=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com  *On
> Behalf Of *Tom P
> *Sent:* Wednesday, 27 March 2019 3:34 PM
> *To:* ozDotNet 
> *Subject:* Re: [OT] Sql Server writes causing contention
>
>
>
> 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
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *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-rela

Re: [OT] Sql Server writes causing contention

2019-03-27 Thread Tom P

Yes. Also tried casting in the selecting from text to varchar(max) but no
luck.

Cheers

On Wed, 27 Mar 2019 at 15:44,  wrote:

> Is it just an INSERT..SELECT for the copy?
>
>
>
> 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=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com  *On
> Behalf Of *Tom P
> *Sent:* Wednesday, 27 March 2019 3:34 PM
> *To:* ozDotNet 
> *Subject:* Re: [OT] Sql Server writes causing contention
>
>
>
> 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
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *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
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *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
>

Re: [OT] Sql Server writes causing contention

2019-03-27 Thread David Rhys Jones

Hello,

Have you tried copying the data in place, by creating a temporary column to
contain the data while you drop and recreate your column?

Davy.


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



On Wed, Mar 27, 2019 at 5:44 AM Tom P  wrote:

> 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
>>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>>>  |http://greglow.me
>>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>>>
>>>
>>>
>>> *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
>>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>>>  |http://greglow.me
>>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>>>
>>>
>>>
>>> *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:
>>>
>>>
>>>
>&

Re: [OT] Sql Server writes causing contention

2019-03-26 Thread 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
>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>>  |http://greglow.me
>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>>
>>
>>
>> *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
>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>>  |http://greglow.me
>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>>
>>
>>
>> *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,
>

Re: [OT] Sql Server writes causing contention

2019-03-26 Thread 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
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *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
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *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

Re: [OT] Sql Server writes causing contention

2019-03-26 Thread 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
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *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
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *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 TEX

RE: [OT] Sql Server writes causing contention

2019-03-26 Thread 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:  
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
 www.sqldownunder.com | 
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
 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:  
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
 www.sqldownunder.com | 
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
 http://greglow.me

 

From: ozdotnet-boun...@ozdotnet.com <mailto: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 <http://www.sqldownunder.com> 

 

  _  

From: ozdotnet-boun...@ozdotnet.com <mailto: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) NOT

Re: [OT] Sql Server writes causing contention

2019-03-26 Thread 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
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *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
>
>

RE: [OT] Sql Server writes causing contention

2019-03-25 Thread greg

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:  
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
 www.sqldownunder.com | 
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
 http://greglow.me

 

From: ozdotnet-boun...@ozdotnet.com <mailto: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 <http://www.sqldownunder.com> 

 

  _  

From: ozdotnet-boun...@ozdotnet.com <mailto: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) 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 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 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, mailto:g...@greglow.com> > 
wrote:

It’s easy to see. Try executing this:

 

U

Re: [OT] Sql Server writes causing contention

2019-03-24 Thread Greg Low

Will be home in a few hours and will try it.

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 
Sent: Monday, March 25, 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 
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 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 
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 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, 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(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:

[cid:169b2f5dcb54cff311]

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=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
 
|http://greglow.me<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>

From: ozdotnet-boun...@ozdotnet.com<mailto: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](

Re: [OT] Sql Server writes causing contention

2019-03-24 Thread Tom P

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] 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,  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?
>>>
>>>
>>>
>

Re: [OT] Sql Server writes causing contention

2019-03-24 Thread Greg Low

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 
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 
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 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, 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(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:

[cid:169b29461494cff311]

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=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
 
|http://greglow.me<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>

From: ozdotnet-boun...@ozdotnet.com<mailto: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

Re: [OT] Sql Server writes causing contention

2019-03-24 Thread Tom P

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] 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,  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
>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>>  |http://greglow.me
>> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>>
>>
>>
>> *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?
>>
>&

Re: [OT] Sql Server writes causing contention

2019-03-24 Thread Greg Low

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 
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, 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(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:

[cid:169b1f501a6ad7999131]

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=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
 
|http://greglow.me<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>

From: ozdotnet-boun...@ozdotnet.com<mailto: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<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
 
|http://greglow.me<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>

From: ozdotnet-boun...@ozdotnet.com<mailto: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@ozd

Re: [OT] Sql Server writes causing contention

2019-03-24 Thread Tom P

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,  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
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *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
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A

Re: [OT] Sql Server writes causing contention

2019-03-06 Thread 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
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *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
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *From:* ozdotnet-boun...@ozdotnet.com  *On
> Behalf Of *Tom P
> *Sent:* Thursday, 7 March 2019 11:53 AM
>

RE: [OT] Sql Server writes causing contention

2019-03-06 Thread 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:  
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
 www.sqldownunder.com | 
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
 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:  
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
 www.sqldownunder.com | 
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
 http://greglow.me

 

From: ozdotnet-boun...@ozdotnet.com <mailto: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:  
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
 www.sqldownunder.com | 
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZ

RE: [OT] Sql Server writes causing contention

2019-03-06 Thread 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:  
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
 www.sqldownunder.com | 
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
 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:  
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
 www.sqldownunder.com | 
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
 http://greglow.me

 

From: ozdotnet-boun...@ozdotnet.com <mailto: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:  
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011d

Re: [OT] Sql Server writes causing contention

2019-03-06 Thread 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
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *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
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
>  |http://greglow.me
> <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
>
>
>
> *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

2019-03-06 Thread 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:  
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
 www.sqldownunder.com | 
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
 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:  
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D=0>
 www.sqldownunder.com | 
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D=0>
 http://greglow.me

 

From: ozdotnet-boun...@ozdotnet.com <mailto: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

2019-03-06 Thread 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

2019-03-06 Thread 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

Re: [OT] Sql Server writes causing contention

Re: [OT] Sql Server writes causing contention

Re: [OT] Sql Server writes causing contention

Re: [OT] Sql Server writes causing contention

Re: [OT] Sql Server writes causing contention

Re: [OT] Sql Server writes causing contention

RE: [OT] Sql Server writes causing contention

Re: [OT] Sql Server writes causing contention

RE: [OT] Sql Server writes causing contention

Re: [OT] Sql Server writes causing contention

Re: [OT] Sql Server writes causing contention

Re: [OT] Sql Server writes causing contention

Re: [OT] Sql Server writes causing contention

Re: [OT] Sql Server writes causing contention

Re: [OT] Sql Server writes causing contention

Re: [OT] Sql Server writes causing contention

RE: [OT] Sql Server writes causing contention

RE: [OT] Sql Server writes causing contention

Re: [OT] Sql Server writes causing contention

RE: [OT] Sql Server writes causing contention

Re: [OT] Sql Server writes causing contention

RE: [OT] Sql Server writes causing contention

22 matches

Site Navigation

Mail list logo

Footer information