May 15, 2020 at 8:04 am
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.
May 15, 2020 at 9:38 am
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
May 15, 2020 at 9:47 am
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.
May 15, 2020 at 9:56 am
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
May 15, 2020 at 10:36 am
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
May 15, 2020 at 10:41 am
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
May 15, 2020 at 10:52 am
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.
May 15, 2020 at 11:01 am
"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
May 15, 2020 at 11:21 am
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
May 15, 2020 at 11:36 am
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
May 15, 2020 at 12:40 pm
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.
May 15, 2020 at 1:55 pm
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply