Administration · T-SQL coding

Saving Some Space

Steve Henry

We have a reporting database for our main application. This database contains a series of “flat” (i.e. de-normalized) tables. We use these tables to hold summary and calculated information for reports, and these tables (for the most part) get emptied and re-loaded on a scheduled basis. Because of this these reporting tables do not have any primary keys or indexes defined; the tables are heap tables that are scanned when the reports are executed. Simple enough.

I noticed, however, that our reporting database seemed to always be a bit larger than it needed to be, so I did some digging. I noticed that some of our reporting tables were empty, but SQL Server reports that space is still being used by those tables. Hmmm…Here is what I found.

First, the tables are populated using stored procedures, and those procedures have a DELETE statement to remove all rows from the table. This was done because the SQL user that executes the procedure does not have the elevated permissions needed to execute the TRUNCATE TABLE statement.

Second, since the tables do not have a clustered index, the DELETE statement does not reclaim unused pages for the table. The first link below contains a good explanation of why this happens in SQL Server; read that article if you want to know the technical details.

So, how do I reclaim that space? There are several different ways I can do this:

  1. Add a clustered index to the table.
  2. Force a table lock on the DELETE statement.
  3. Rebuild the table using ALTER TABLE.
  4. Use TRUNCATE TABLE instead of DELETE.

All four of these methods are valid alternatives; I will probably choose method #1 for future (new) tables. I chose to use method #4 for existing tables. However, I had to get around the fact that the SQL user does not have permissions to execute the TRUNCATE TABLE, even when that command is inside a stored procedure. That’s when I remembered that adding a WITH EXECUTE AS (starting with SQL Server 2008) clause to your CREATE PROCEDURE statement allows you to “elevate” the permissions of the procedure without giving the SQL user extra permissions that you don’t want them to have. My updated stored procedure code now looks like this:

CREATE PROCEDURE dbo.USP_RPRT_MY_PROCEDURE

       @FromDate DATETIME = NULL,

       @ToDate DATETIME = NULL

WITH EXECUTE AS OWNER

AS

…

–DELETE FROM DBO.MyReportTable;

TRUNCATE TABLE DBO.MyReportTable;

…

This should help you save some space in your database, and should help your load procedure and your select procedure(s) run more efficiently.

Links Worth Reading:

https://www.red-gate.com/simple-talk/sql/t-sql-programming/delete-operation-sql-server-heaps/

https://social.technet.microsoft.com/wiki/contents/articles/35150.sql-server-inside-a-delete-operation-in-heaps.aspx#Reading_data_pages_in_a_Heap

 

 

Leave a comment