Strange Write Statistics

Question

Post reply

Strange Write Statistics

VastSQL

SSC-Dedicated

Points: 36069
More actions
June 24, 2022 at 7:45 am

#4054222

Hi Experts,
I ran the below query and for a table I am getting very high writes and its increasing every second. The table here is a master table which is having only 17 records?

TableName Reads Writes
Supplier 13 13460167
SELECT TableName = object_name(s.object_id),
Reads = SUM(user_seeks + user_scans + user_lookups), Writes = SUM(user_updates)
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE
s.database_id > 5
GROUP BY object_name(s.object_id)
ORDER BY writes DESC

TIA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32070 More actions · Answer 1

Could be a counter table? Like new order -> update counter ( write )

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 2

VastSQL wrote:

Hi Experts,
I ran the below query and for a table I am getting very high writes and its increasing every second. The table here is a master table which is having only 17 records?
TableName Reads Writes Supplier 13 13460167
SELECT TableName = object_name(s.object_id), Reads = SUM(user_seeks + user_scans + user_lookups), Writes = SUM(user_updates) FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND i.index_id = s.index_id WHERE s.database_id > 5 GROUP BY object_name(s.object_id) ORDER BY writes DESC
TIA

Please post the CREATE TABLE statement for the table. It would also be helpful if you posted the CREATE INDEX statements for it, as well.

I've got the feeling it could be something like a "NextID" table or something similar.

Hopefully, it's not an "indexed view".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VastSQL SSC-Dedicated Points: 36069 More actions · Answer 3

Jeff Moden wrote:

VastSQL wrote:
Hi Experts,
I ran the below query and for a table I am getting very high writes and its increasing every second. The table here is a master table which is having only 17 records?
TableName Reads Writes Supplier 13 13460167
SELECT TableName = object_name(s.object_id), Reads = SUM(user_seeks + user_scans + user_lookups), Writes = SUM(user_updates) FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND i.index_id = s.index_id WHERE s.database_id > 5 GROUP BY object_name(s.object_id) ORDER BY writes DESC
TIA
Please post the CREATE TABLE statement for the table. It would also be helpful if you posted the CREATE INDEX statements for it, as well.
I've got the feeling it could be something like a "NextID" table or something similar.
Hopefully, it's not an "indexed view".

Thanks Jeff,

Please find below the table structure.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Supplier](

[ID] [varchar](50) NOT NULL,

[SupplierName] [varchar](250) NOT NULL,

[Description] [varchar](500) NOT NULL,

[SupplierNo] [varchar](50) NOT NULL,

[SupplierCode] [varchar](150) NULL,

[Status] [varchar](30) NULL,

[CreatedBy] [varchar](250) NULL,

[CreatedDate] [date] NULL,

[UpdatedBy] [varchar](250) NULL,

[UpdatedDate] [date] NULL,

CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[Supplier] ADD CONSTRAINT [DF_Supplier_ID] DEFAULT (newid()) FOR [ID]

GO