3

In SQL Server, dropping a column is a metadata only change; the data remains in the index until the index is rebuilt.

On prem, I use system_internals_partition_columns to determine what indexes have this ghosted column so I can rebuild and reclaim the space. This internal table is not accessible in Azure.

Does anyone know of a dmv or system table in Azure with this information?

Below is an example of what I am talking about. These rows are approx. 2000 bytes so you get 4 rows per page. Dropping a column should make each page about 50% free space but doesn't. Worse, new records are still added at 4/page rather than 8/page.

DROP TABLE IF EXISTS dbo.TEST

GO
CREATE TABLE dbo.TEST (ID INT IDENTITY PRIMARY KEY,
                       a CHAR(995),
                       b CHAR(995))


 DECLARE @i INT = 1
   WHILE @i <= 4000
   BEGIN
      INSERT INTO dbo.Test
      VALUES(@i,@i)

      SET @i +=1
    END   

SELECT t.name, p.page_count, p.avg_fragmentation_in_percent, p.avg_page_space_used_in_percent
  FROM sys.tables t
  CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(),t.object_id,1,NULL,'SAMPLED') p
  WHERE t.name = 'TEST'

/* Result

name    page_count  avg_fragmentation_in_percent    avg_page_space_used_in_percent
TEST    1000    0.3 99.6540647393131


--*/
GO

ALTER TABLE dbo.TEST
DROP COLUMN a

GO

SELECT t.name, p.page_count, p.avg_fragmentation_in_percent, p.avg_page_space_used_in_percent
  FROM sys.tables t
  CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(),t.object_id,1,NULL,'SAMPLED') p
  WHERE t.name = 'TEST'

/* Result - No change from the dropped column

name    page_count  avg_fragmentation_in_percent    avg_page_space_used_in_percent
TEST    1000    0.3 99.6540647393131

--*/


 DECLARE @i INT = 1
   WHILE @i <= 4000
   BEGIN
      INSERT INTO dbo.Test
      VALUES(@i)

      SET @i +=1
    END  


SELECT t.name, p.page_count, p.avg_fragmentation_in_percent, p.avg_page_space_used_in_percent
  FROM sys.tables t
  CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(),t.object_id,1,NULL,'SAMPLED') p
  WHERE t.name = 'TEST'

/* Result - Same amount of space taken despite rows being half the size

name    page_count  avg_fragmentation_in_percent    avg_page_space_used_in_percent
TEST    2000    0.35    99.6540647393131

--*/

--This is how I find columns that are dropped but still being added to the page on prem
--DOES NOT WORK IN AZURE SQL DB
--SELECT tables.name
--  FROM sys.partitions
-- INNER JOIN sys.system_internals_partition_columns ON partitions.partition_id = system_internals_partition_columns.partition_id
-- INNER JOIN sys.tables ON tables.object_id = partitions.object_id
-- WHERE is_dropped = 1

I've continued looking into this off and on, but between not finding anything online and Randi's comment below, this may not be possible.

  • Interesting question, according to this blog, inside the system_internals_partition_columns view, the sys.sysrscols dmv is used to get the is_droppedcolumn (CONVERT(BIT, c.status & 2) AS is_dropped).With a DAC connection to an azure sql db, the sys.sysrscols dmv also does not appear to exist. – Randi Vertongen May 21 at 18:27
0

Since Azure database is MSSQL server then you can execute the following command to find how many ghost process per database

SELECT sum(ghost_record_count) total_ghost_records, db_name(database_id) 
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
group by database_id
order by total_ghost_records desc

And if you are looking for more detailed report then you can execute the following command

SELECT
db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N'db_name'), OBJECT_ID(N'table_name'), NULL, NULL , 'DETAILED');

Looking for long term solution to start recording this data then you can check this post. This will create table that store ghost records in the long term, but I recommend you to create it on test environment first to see if this is what you actually want.

  • This provides the number of rows that have been removed, but not columns. Ghosted records should be removed b the ghost cleanup task so I generally don't worry about them even if I am trying to regain space. – user3593990 May 22 at 12:02

Not the answer you're looking for? Browse other questions tagged or ask your own question.