Select TOP 1 Runs forever
-
I have 2 identical tables that we just recently deleted about 15M rows on 2 separate databases.
After the delete I rebuilt the index on both tables:
ALTER INDEX REBUILD
Tables both show fragmentation (from the following query):
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'<DBNAME>');
SET @object_id = OBJECT_ID(N'<TABLENAME>');
IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');
END;
GO
Table 1:
database_idobject_idindex_idpartition_numberindex_type_descalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentfragment_countavg_fragment_size_in_pagespage_countavg_page_space_used_in_percentrecord_countghost_record_countversion_ghost_record_countmin_record_size_in_bytesmax_record_size_in_bytesavg_record_size_in_bytesforwarded_record_count
20128557961801HEAPIN_ROW_DATA1013.95457074922854173653.166834387579122189710.136384976525819454000490149574917.5410
Table 2:
database_idobject_idindex_idpartition_numberindex_type_descalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentfragment_countavg_fragment_size_in_pagespage_countavg_page_space_used_in_percentrecord_countghost_record_countversion_ghost_record_countmin_record_size_in_bytesmax_record_size_in_bytesavg_record_size_in_bytesforwarded_record_count
17128557961801HEAPIN_ROW_DATA1016.1383818693764128045.336967054263618715100.8204348900420062485800490249714920.6130
The query:
SELECT TOP 1 * FROM <TABLE>
Runs for almost 15 minutes in Table 1 and 3 seconds in Table 2.
I can fix the issue if I drop Table 1 and reload it but can anyone point me somewhere else to figure out why this was happening?
Both tables have approx. 370 columns on them...
I realize you can't reproduce the data, I'm just looking for general ideas.
Thanks
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply