4

I use the following script to gather data from my database wich is SQL Server on Azure.

-- Script to run against database to gather metrics

CREATE TABLE #SpaceUsed (name sysname,rows bigint,reserved sysname,data sysname,index_size sysname,unused sysname)

DECLARE @Counter int 
DECLARE @Max int 
DECLARE @Table sysname

SELECT  name, IDENTITY(int,1,1) ROWID 
INTO       #TableCollection 
FROM    sysobjects 
WHERE xtype = 'U' 
ORDER BY lower(name)

SET @Counter = 1 
SET @Max = (SELECT Max(ROWID) FROM #TableCollection)

WHILE (@Counter <= @Max) 
    BEGIN 
        SET @Table = (SELECT name FROM #TableCollection WHERE ROWID = @Counter) 
        INSERT INTO #SpaceUsed 
        EXECUTE sp_spaceused @Table 
        SET @Counter = @Counter + 1 
    END

SELECT * FROM #SpaceUsed

DROP TABLE #TableCollection 
DROP TABLE #SpaceUsed

One of my tables is 53 GB in size, with 38 GB in data, 14 GB in unused. The indexes are 11 MB in size. How do I go about reclaiming that 14 GB that is unused? As this is in Azure the extra space is costing money.

After reading various articles I have tried the following to reduce the size of the table.

DBCC DBREINDEX ('myTableName', ' ')
DBCC SHRINKDATABASE (myDatabaseName, 10);

However, after these complete, the size of the table remains unchanged. How can I go about reducing the size of the table and database in order to reclaim unused space?

UPDATE: There are 24 columns in the table with 9 of the fields being varchar of large length (> 4000) or varchar(MAX). Another 8 columns are unique-identifier types.

  • 1
    How wide is the table? By the way this query won't work without a #SpaceUsed table. – Jacob H Jun 13 '17 at 16:16
  • @Jacob, not sure what you mean by #SpaceUsed not being created. It is created in the query as a temp table. – webworm Jun 13 '17 at 16:21
  • Whoops, somehow I scrolled down and missed just the top line. Carry on... – Jacob H Jun 13 '17 at 16:22
8

Whether a heap or a clustered index, you should find that you will be able to reclaim space in a table by rebuilding:

ALTER INDEX ALL ON dbo.myTableName REBUILD;

Note that reclaiming space in a table, and shrinking a database, are two completely separate things. Shrinking a database should be an exceptional thing - don't shrink a database just to free up disk space, since in most cases, the database will just have to grow and use that space again anyway.

As for determining how the table is distributed, sp_spaceused is pretty useless, since it just gives you a single line item with all data. First, check to see if your table is partitioned. You can see this quickly using:

SELECT COUNT(*)
  FROM sys.partitions
  WHERE partition_number > 1
  AND object_id = OBJECT_ID(N'dbo.myTableName');

Next, see how the data is laid out across your seven indexes. If one of these is a lot bigger than it should be, you could try dropping/re-creating (including potentially removing, say, your XML column from the INCLUDE list), as there may be cases where LOB space for rows you've deleted or set to NULL doesn't get fully recovered, even after a rebuild.

 ;WITH x AS
  ( 
    SELECT i.name, type = pa.allocation_unit_type_desc, kb = COUNT(*) * 8
    FROM sys.indexes AS i
    CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), i.object_id, i.index_id, 
      NULL, 'LIMITED') AS pa
    WHERE i.[object_id] = OBJECT_ID(N'dbo.myTableName')
    GROUP BY GROUPING SETS((), (i.name, pa.allocation_unit_type_desc))
  )
  SELECT name, type, kb, 
    [%] = CONVERT(decimal(5,2), kb*100.0/(SELECT kb FROM x WHERE name IS NULL))
    FROM x
    WHERE name IS NOT NULL
    ORDER BY name, type;

If you want to see individual row sizes in the base table, you could do:

SELECT TOP (100) key, rowsize = DATALENGTH(col1) + DATALENGTH(col2) + ...
  FROM dbo.myTableName
  ORDER BY rowsize DESC;

If you have some that are quite large, you might want to make sure you need that data (otherwise set it to NULL, as it may be what's contributing to your size.

  • Thanks, @Aaron, but it didn't really make much of a difference (saved 28 MB). Any idea why 14 GB of space is reserved but unused? The indexes are pretty small compared to the data and the unused space. – webworm Jun 13 '17 at 16:27
  • @webworm need more information than what you're saying sp_spaceused puts out. Do you have "empty" partitions showing in sys.partitions? What does sys.dm_db_database_page_allocations look like? Is it a heap or clustered index? How many of your varchar(max) columns do (or did) have data that pushed row size up over 8kb? – Aaron Bertrand Jun 13 '17 at 16:38
  • Thanks for the help. I have seven indexes, one of which is clustered on the Identity column which is an integer. It is quite possible a number of the varchar columns have pushed the row size over 8kb as they store XML. Is there a command or tool I can run that will show row sizes? How can I view the partition information? – webworm Jun 13 '17 at 16:44
3

You should check the fill factor on your tables and indexes, to make sure that you aren't explicitly reserving a lot of space on your pages. Run the following command:

SELECT t.name as TableName
      ,COALESCE(i.name,'<no index>') as IndexName
      ,i.type_desc
      ,i.Fill_Factor
  FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id
;

Fill factor tells the SQL engine how much free space to leave on each page it creates as it populates a table or index. After the pages are populated, SQL will add information to the page until it gets too full, then will create a new page, and move an appropriate amount (usually about half) of the data to the new page.

If one of your indexes has a low fill factor (under 75, but over 0 (0 actually means fill to 100%, basically)), it's possible that might explain a large amount of unused space in your database.

If you want to change the fill factor on your indexes to (for example) 90%, you could use a slight variation on the ALTER INDEX command from Aaron Bertrand's answer:

ALTER INDEX ALL ON dbo.myTableName REBUILD WITH FILLFACTOR = 90;

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.