Space Management in SQL Database

Question

This question has been moved from Game Development Stackoverflow

I am trying to create a new type of MMORTS game.

Current Situation

Everyday each user deletes and creates :

1. 1,500 rows of data in diff. tables but in 1 database (all tables data are inter-related to data of other tables)
   
2. Each Row estimated size : 1KB
   
3. Max user per database: 100,000 users
   
4. Total Space Consumed everyday : 0.15 TB

Actual Users in the Game : 10,000,000 users (split in different databases)

Problem

According to me if i delete a row in SQL it is not being deleted permanently and will still consume the data in the database. If this happens, then in near future all my server are likely to crash.

Can anyone tell me if i am going wrong anywhere or suggest me any solution to this problem? I know this question cannot be understood easily so please feel free to ask any doubts you have regarding this question.

Answer 1

If the tables in question are not using a clustered index, they are what is referred to as "heaps". Heaps do not automatically release the space consumed by deleted rows.

To determine if your tables actually have clustered indexes, you can right-click the table name from SQL Server Management Studio, then click "Script table ... -> CREATE to -> New Query Editor Window". If you see the word "CLUSTERED" in the definition produced, then the table has a clustered index.

If your tables are not using clustered indexes, I would recommend adding clustered indexes on appropriate key columns (probably the id column), since rows deleted from clustered indexes are automatically freed by the ghost cleanup process referred to in the answer posted by @RLF. Clearly, you're going to do this in the development environment, NOT production, correct?

For further info, I recommend reading Paul Randall's excellent post on the ghost cleanup process. One salient sentence from that post is:

do ghost records occur in heaps? The answer is no, not during normal processing.

Answer 2

If you delete a row in SQL the space it uses is freed. This will make space available within the 8 KB page. Whether the space will be reused automatically depends on how you insert and delete data.

If your CLUSTERED INDEX is an ascending value perhaps using the IDENTITY property for an INT or BIGINT column, then the space will not readily be reused just based on deletions and insertions to the table.

If your CLUSTERED INDEX is based on some other data, such as UserName, then statistically a fair amount of the space will eventually be reused.

That is just based on the behavior of insertion. However, you can schedule a period in which you alter the index to reorganize your data and reclaim space from the empty data.

There are tools, such as https://ola.hallengren.com/ provides.

Or you can create targeted updates by choosing just certain indexes to be reorganized. See the details on your options at: https://msdn.microsoft.com/en-us/library/ms188388(v=sql.100).aspx

A sample from the MSDN post:

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

Review the options that you would like to use from that page.

As Shanky described in his comments, shrinking files is a really bad choice.

Using the REBUILD or the REORGANIZE options of ALTER INDEX will give you better results. However, the ALTER INDEX should not need to be run frequently. Analyze the degree of fragmentation in order to choose the frequency and the window of time you will use.

An ALTER INDEX with either REBUILD or REORGANIZE will order the data into the update pages while reserving the space indicated by the FILLFACTOR. This means that if many of the data pages are fragmented, perhaps due to many deletions, the data will be moved around so as to put data in the CLUSTERED INDEX order.

While the data is being moved into logical order, it will empty pages and extents which will result in recovering space in the database.

EDIT: https://msdn.microsoft.com/en-us/library/ms189858(v=sql.120).aspx (for 2014) explicitly makes the following comments:

"Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction."

"Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value."