It’s easy to see. Try executing this:
USE tempdb;
GO
DROP TABLE IF EXISTS dbo.TomText;
GO
DROP TABLE IF EXISTS dbo.TomVarCharMax;
GO
CREATE TABLE dbo.TomText
(
TomTextID bigint identity(1,1) NOT NULL,
[Message] text NOT NULL
);
GO
CREATE TABLE dbo.TomVarCharMax
(
TomVarCharMaxID bigint identity(1,1) NOT NULL,
[Message] varchar(max) NOT NULL
);
GO
INSERT dbo.TomText ([Message])
SELECT TOP(10000) [name]
FROM sys.all_columns;
GO
INSERT dbo.TomVarCharMax ([Message])
SELECT TOP(10000) [name]
FROM sys.all_columns;
GO
SELECT OBJECT_NAME(object_id),
alloc_unit_type_desc,
page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE OBJECT_NAME(object_id) IN (N'TomText', N'TomVarCharMax');
It’ll return something like this:
Note the difference.
Also note that you can’t fix it by just changing the datatype. Rename the
table, create a new one, and move the data over.
Another question: why 90 as a FILLFACTOR if you’re only writing to it in order?
Regards,
Greg
Dr Greg Low
1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax
SQL Down Under | Web:
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274&sdata=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D&reserved=0>
www.sqldownunder.com |
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278&sdata=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D&reserved=0>
http://greglow.me
From: [email protected] <[email protected]> On Behalf
Of Tom P
Sent: Thursday, 7 March 2019 1:20 PM
To: ozDotNet <[email protected]>
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, <[email protected] <mailto:[email protected]> >
wrote:
Might get you to post the actual table schema though, so we can check it. Just
script the whole table, indexes, etc.
First comment is that you shouldn’t be using text at all. Apart from the fact
that it was deprecated back in 2005, the data for that is (by default) stored
out of row, not with the rest of the data. That’s never quick.
Regards,
Greg
Dr Greg Low
1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax
SQL Down Under | Web:
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274&sdata=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D&reserved=0>
www.sqldownunder.com |
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278&sdata=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D&reserved=0>
http://greglow.me
From: [email protected] <mailto:[email protected]>
<[email protected] <mailto:[email protected]> > On
Behalf Of Tom P
Sent: Thursday, 7 March 2019 11:53 AM
To: ozDotNet <[email protected] <mailto:[email protected]> >
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, <[email protected] <mailto:[email protected]> >
wrote:
Hi Tom,
Can you post the table schema?
What rate are the writes coming in at?
Regards,
Greg
Dr Greg Low
1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax
SQL Down Under | Web:
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274&sdata=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D&reserved=0>
www.sqldownunder.com |
<https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278&sdata=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D&reserved=0>
http://greglow.me
From: [email protected] <mailto:[email protected]>
<[email protected] <mailto:[email protected]> > On
Behalf Of Tom P
Sent: Thursday, 7 March 2019 11:40 AM
To: ozDotNet <[email protected] <mailto:[email protected]> >
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
