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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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