Problem with a heap table

Question

Problem with a heap table

msimone

SSCommitted

Points: 1846
More actions
May 15, 2020 at 8:04 am

#3751921

I inserted in a heap table millions of rows. When finished, i executed
select t2.name,t1.name,t3.name,t3.rows
from sys.sysobjects as t1 inner join sys.schemas as t2 on t2.schema_id=t1.uid
inner join sys.sysindexes as t3 on t3.id=t1.id
where t1.xtype='U' and t3.status & 64 = 0 and t3.status & 16777216 = 0 and t3.status & 8388608 = 0
and t2.name='dbo' and t1.name='MyTable'
and the table has zero rows.
While it was inserting, I checked with the same select and it reported a number of rows

After to see that the table had zero rows, i executed sys.dm_db_index_physical_stats, reported zero rows... and a size of 120 GB, more or less.
Without deletes after insert, of course.
Any idea? please.
Thanks.

Viewing 12 posts - 1 through 11 (of 11 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

The rows column in the legacy view sysindexes isn't accurate. For a more up-to-date, although not documentedly reliable, count, use the rows column in sys.partitions instead.

John

msimone SSCommitted Points: 1846 More actions · Answer 2

Hello John, "The rows column in the legacy view sysindexes isn't accurate", I know but in this case, is accurate because sys.dm_db_index_physical_stats reported zero rows.

After to execute "Alter Table MyTable Rebuild", it took 15 minutes, I executed sys.dm_db_index_physical_stats and reported zero rows and zero pages!! when before of the execution "alter table", sys.dm_db_index_physical_stats reported a number of pages very high, more or less 120 GB.

I don't understand.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

What parameters did you supply to dm_db_index_physical_stats? Please will you post the result set? Please will you post the DDL for the table and any indexes on it?

John

msimone SSCommitted Points: 1846 More actions · Answer 4

sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.MyTable'),null,null,'sampled')

Before "Alter Table MyTable rebuild", reported a high pages number

After "alter table", reported zero page number

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Did you run it in the context of the database the table is in both times? What happens if you try a DETAILED scan instead of SAMPLED? Please post table and index DDL as requested.

John

msimone SSCommitted Points: 1846 More actions · Answer 6

Yes, both times.

I can´t try "DETAILED" because the "ALTER TABLE REBUILD" did a thing like "truncate table".

How i said

before "ALTER TABLE REBUILD", sys.dm_db_index_physical_stats reported page_count with a high number

after "ALTER TABLE REBUILD", reported page_number = 0

In both times, reported zero for the column record_count

It is the first time that I see something like that.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

msimone wrote:

"ALTER TABLE REBUILD" did a thing like "truncate table"

How do you know that's what happened? Are there now no rows in your table? What happens if you run SELECT COUNT(*) FROM MyTable?

ALTER TABLE REBUILD doesn't truncate the table. Either you've accidentally done so yourself, or you've discovered a serious bug.

Still waiting for table and index DDL.

John

msimone SSCommitted Points: 1846 More actions · Answer 8

Table:

CREATE TABLE [CM].[LogCL](

[Tabla] [nvarchar](128) NOT NULL,

[Fecha] [datetime] NOT NULL,

[Operacion] [nvarchar](3) NOT NULL,

[UserMod] [nvarchar](48) NOT NULL,

[HostMod] [nvarchar](64) NOT NULL,

[UserMod_U] [smallint] NOT NULL,

[HostMod_U] [nvarchar](128) NOT NULL,

[Columnas] [nvarchar](max) NOT NULL

) ON [MYFILEGROUP]

without index

I didn't execute "select count(*)" because sys.dm_db_index_physical_stats reported page_count reported zero record_count

I didn't think to execute "select count(*)" after to execute sys.dm_db_index_physical_stats reported page_count

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Right, so are you saying you inserted millions of rows, but now none of them are there? If that's the case, then everything is working properly - zero rows are being reported because the table is empty. Now, you mentioned TRUNCATE TABLE earlier. How do you know that happened?

John

msimone SSCommitted Points: 1846 More actions · Answer 10

I mentioned TRUNCATE TABLE because "ALTER TABLE REBUILD" had a behavior like truncate table, because sys.dm_db_index_physical_stats reported zero in record_count and page_count.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 11

Step one is to remove all doubt. Do the SELECT COUNT(*) thing on the table.

You also say that you inserted millions of rows into the heap. There are a lot of things that could have happened and some will seem silly but needs to be checked. For example, are you looking in the same database as were you inserted the rows? I've seen a lot of people get balled up there.

Also, was there an error that you might have missed that caused a rollback of the insert?

Seriously... check out which databases you're doing all of this in and check to see if an error occurred like maybe running out of space or even some some simple data violation, etc.

--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)