Table Row Count

Question

Table Row Count

VastSQL

SSC-Dedicated

Points: 36097
More actions
April 8, 2018 at 4:53 am

#328139

Hi Experts,
We have a very Large table which hold almost 185GB of data.
When ran the Count it was giving 159709 records but when ran sys.dm_db_index_physical_stats it gave 23346074 records for LOB data alone and IN_ROW_DATA (Clustered Index) it was giving the same record. I am trying to figure out the per row size ,do i need to consider the LOB record number while calculating the per row size
We are planning to delete data till Dec-17 from this table and the data comes around 130GB .

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

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

VastSQL - Sunday, April 8, 2018 4:53 AM
Hi Experts,
We have a very Large table which hold almost 185GB of data.
When ran the Count it was giving 159709 records but when ran sys.dm_db_index_physical_stats it gave 23346074 records for LOB data alone and IN_ROW_DATA (Clustered Index) it was giving the same record. I am trying to figure out the per row size ,do i need to consider the LOB record number while calculating the per row size
We are planning to delete data till Dec-17 from this table and the data comes around 130GB .

Please post the CREATE TABLE statement for the table in question so the we can help you better.

Also, doing DELETEs on this table would probably NOT be the way to go but we need that CREATE TABLE statement to help you figure out what the best way to do it is. It would also be useful if you posted the output from sys.dm_db_index_physical_stats for this table. Don't use the 'LIMITED' option here... it won't give us the information we need to help.

--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: 36097 More actions · Answer 2

Jeff Moden - Sunday, April 8, 2018 8:33 AM
VastSQL - Sunday, April 8, 2018 4:53 AM
Hi Experts,
We have a very Large table which hold almost 185GB of data.
When ran the Count it was giving 159709 records but when ran sys.dm_db_index_physical_stats it gave 23346074 records for LOB data alone and IN_ROW_DATA (Clustered Index) it was giving the same record. I am trying to figure out the per row size ,do i need to consider the LOB record number while calculating the per row size
We are planning to delete data till Dec-17 from this table and the data comes around 130GB .
Please post the CREATE TABLE statement for the table in question so the we can help you better.
Also, doing DELETEs on this table would probably NOT be the way to go but we need that CREATE TABLE statement to help you figure out what the best way to do it is. It would also be useful if you posted the output from sys.dm_db_index_physical_stats for this table. Don't use the 'LIMITED' option here... it won't give us the information we need to help.

Thanks Jeff.

CREATE TABLE [dbo].[PLM](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [varchar](100) NULL,
    [Message] [nvarchar](max) NULL,
    [Type] [varchar](50) NULL,
    [ToProcess] [int] NULL,
    [CreateDate] [datetime] NULL,
CONSTRAINT [PK_PLM] 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] TEXTIMAGE_ON [PRIMARY]

GO

database_id	object_id	index_id	partition_number	index_type_desc	alloc_unit_type_desc	index_depth	index_level	avg_fragmentation_in_percent	fragment_count	avg_fragment_size_in_pages	page_count	avg_page_space_used_in_percent	record_count	ghost_record_count	version_ghost_record_count	min_record_size_in_bytes	max_record_size_in_bytes	avg_record_size_in_bytes	forwarded_record_count	compressed_page_count
6	994102582	1	1	CLUSTERED INDEX	IN_ROW_DATA	3	0	2.747253	249	7.309237	1820	97.39427	159720	0	0	43	7193	87.85	NULL	0
6	994102582	1	1	CLUSTERED INDEX	IN_ROW_DATA	3	1	0	6	1	6	48.69451	1820	0	0	11	11	11	NULL	0
6	994102582	1	1	CLUSTERED INDEX	IN_ROW_DATA	3	2	0	1	1	1	0.938967	6	0	0	11	11	11	NULL	0
6	994102582	1	1	CLUSTERED INDEX	LOB_DATA	1	0	0	NULL	NULL	23346074	98.9519	23402993	0	0	16	8054	7989.682	NULL	NULL
6	994102582	6	1	NONCLUSTERED INDEX	IN_ROW_DATA	2	0	4.240283	14	20.21429	283	97.59749	159722	2	0	12	12	12	NULL	0
6	994102582	6	1	NONCLUSTERED INDEX	IN_ROW_DATA	2	1	0	1	1	1	69.90363	283	0	0	18	18	18	NULL	0