How SQL Server deletes records - DELETE internals

I was surprised with the fact that a lot of developers don't know that SQL Server does not perform immediate delete of their DELETE command, but it is doing an a background thread called GHOST CLEANUP periodically. When row delete is requested by transaction, that row is being just marked as "ghosted" and left to the background process to actually removes data. Thus, performances are enhanced and in case of ROLLBACK less work is done.  In a system table sys.dm_exec_requests you can periodically notice appearance of the GHOST CLEANUP as command. 
So, let's see what SLQ Server actually does when we are executing 


DELETE FROM  MyTable WHERE ... 


At the beginning let's create test table and insert couple of records. 



CREATE TABLE [dbo].[DeleteInternals](
[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] [char](4) NOT NULL
)
GO


insert into DeleteInternals([Name]) values('Row1')
insert into DeleteInternals([Name]) values('Row2')
insert into DeleteInternals([Name]) values('Row3')
insert into DeleteInternals([Name]) values('Row4')

To discover internals of the table structure, we will use undocumented commands DBCC IND and DBCC PAGE which are heavily used internally in SQL Server but they are not documented and not supported which means you are using them at your own risk. 


To find out which page IDs to look at with DBCC PAGE we are executing

DBCC IND(TestDatabase, DeleteInternals, 1)

Some important fileds for us in the output are

• PageFID - the file ID of the page
• PagePID - the page number in the file
• OBjectID - the ID of the object which page is part of
• IndexID - the ID of the index which page is part of
• PageType - where 1 = Data Page, 2 = Index Page
• IndexLevel - as SQL Server uses doubly-linked lists in a B-Tree for indexes, the data are actually leaf level (level 0) of the clustered index.

 In order to get output back to the console from DBCC commands we need to turn on T3604


DBCC TRACEON(3604)
GO
DBCC PAGE(TestDatabase, 1,399,1)


Here is output from this command:

Important stuff for our example here are following records marked in a red square):

• m_slotCnt = 4 - which shows number of records on the page
• m_GhostRecCnt = 0 - count of "ghosted" records
• RecordType = PRIMARY_RECORD for a Slot1 where "Row2" data is located which we will delete later on
• Row - Offset table - offsets for all records. 

After executing DELETE command we will take a look at a page structure again. But, to prevent GHOST CLEANUP process to execute and cleans our page we will enclose DELETE command in a transaction which we will not commit for some time: 



begin transaction 
delete from DeleteInternals where Id = 2


Let's take a look at page content: 


DBCC PAGE(TestDatabase, 1,399,1)

 We still have 4 records (m_slotCnt) but we can notice now that we have one "ghosted" record, m_ghostRecCnt = 1. That record is exactly our "Row2" which is marked now as GHOST_DATA_RECORD (RecordType). Row - Offset table remains unchanged. 


After committing transaction by executing


commit


and inspecting again page structure


DBCC PAGE(TestDatabase, 1,399,1)

 We see now, after committing transaction that number of records is equal 3 (m_slotCnt = 3) and number of "ghosted" records is equal 0 (m_ghostRecCnt = 0) 
Also, Row - Offset table shows all three records, but you can notice that their offset remains as previous, before delete commnad is executed, what means that SQL Server didn't compacted page. About that, we speak some when next time. 

Disk I/O on SQL Server

To get a pending request longer than 10 milliseconds on your SQL Server  use

SELECT * FROM [sys].[dm_io_pending_io_requests] WHERE [io_type] = 'disk' AND [io_pending_ms_ticks] > 10

How to hide form on startup in .NET

If you ever wanted to hide a form on startup in a .NET you figured out that the task is not so obvious. If you just set Visible property to False you will see that it will be ignored.

The solution is to run the form as Minimized, by setting the form property WindowState to minimized and in Form_Load function to set this.Visible = false;

Please note that line

this.WindowState = System.Windows.Forms.FormWindowState.Minimized;

must be in InitializeComponent() function generated by designer. Either you add this line manually there or set the WindowState property in the Form designer. At the very end functions which hides your form on startup looks like

private void InitializeComponent()

{

this.SuspendLayout();

//

// Form1

//

this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);

this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;

this.ClientSize = new System.Drawing.Size(292, 266);

this.ControlBox = false;

this.Name = "Form1";

this.Text = "Form1";

this.WindowState = System.Windows.Forms.FormWindowState.Minimized;

this.Load += new System.EventHandler(this.Form1_Load);

this.ResumeLayout(false);

}

private void Form1_Load(object sender, EventArgs e)

{
this.Visible = false;

}

Database Full Text Search Tool

If you need to search for a particular word(s) or number(s) in a whole database you can use DBTYP.NET Database Full Text Search module which has been just added to the DBTYP.NET Studio.



It performs searches on all data types except binary, xml and date/time values. So you are free to search for whole words, part of words using SQL LIKE wild-cards or even for the numbers.

SQL Server Configuration Options

If you need to review configuration options of some SQL Server instance, you can get them out of the SYS.CONFIGURATIONS catalog view. So the select statement like

select * from sys.configurations

order by name

will give you back all configuration options of SQL Server instance.

Prevent Row Deletions/Updates on SQL Server

If you reach a requirements to prevent some table for deletions or/and updates you may wonder how to reach that. The simple answer is to use INSTEAD OF  triggers. INSTEAD OF triggers override the standard action of triggering statement: INSERT, UPDATE or DELETE. On that way, INSTEAD OF trigger can ignore parts of the batch, not process part of the batch or taking an alternative action. The major difference to the AFTER triggers is that INSTEAD OF triggers can be defined on a views as well as on a tables.

So, a solution of the problem, preventing a row deletions on the table can be the following INSTEAD OF trigger:

CREATE TRIGGER trgPreventRowDeletion ON Test_table INSTEAD OF DELETE
AS
BEGIN
REISEERROR ('Deletions are not allowed from the Test_table', 16, 1)
END

On a similar way it can be defined INSTEAD OF INSERT and INSTEAD OF UPDATE triggers.

Identifying Index Fragmentation on SQL Server

The first thing you are doing when identifying poor query performances is to take a look at execution plan and indexes usage. Even that you see, everything is fine, your index can be fragmented so that SQL Server needs to make unnecessary data reads to execute query. At the end, your query is slow.
At the moment when index is created, little or no fragmentation is present. During the time, when updates, inserts and deletes occur indexes get fragmented what is a real bottleneck in a SQL Server performances.

There are two ways how to fix fragmented indexes: reorganizing or rebuilding them. Which operation is necessary depends on the level of fragmentation. Reorganization of index is suggested if fragmentation level is less than 30%. If it is more than 30% than rebuilding index is better choice.

Identifying fragmented indexes

Starting from version 2005, SQL Server contains a number of DMVs and DMFs qhich allow us to retrieve informations about SQL Server health and performances and identifying problems. One of them, allow us to take a look at the index fragmentation level - sys.dm_db_index_physical_stats DMF. It is important to said here that it places intent shared lock (IS) on the affected tables during execution.

sys.dm_db_index_physical_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | NULL | 0 | -1 | DEFAULT }
, { partition_number | NULL | 0 | DEFAULT }
, { mode | NULL | DEFAULT }
)

The following query will get fragmentation of all indexes in the database

DECLARE @dbId int

SET @dbId = db_id('YOUR_DB_NAME')

SELECT s.[name] AS SchemaName, t.[name] AS TableName, i.[name] AS IndexName, p.[index_type_desc], p.[avg_fragmentation_in_percent]
FROM [sys].[dm_db_index_physical_stats](@dbId, NULL, NULL, NULL , 'DETAILED') p
INNER JOIN [sys].[tables] t  ON p.[object_id] = t.[object_id]
INNER JOIN [sys].[schemas] s  ON t.[schema_id] = s.[schema_id]
INNER JOIN [sys].[indexes] i  ON p.[object_id] = i.[object_id] AND p.index_id = i.index_id
WHERE t.[is_ms_shipped] = 0

To get more proper candidates for rebuilding or reorganizing indexes it is necessary to consult other fields returned back from [sys].[dm_db_index_physical_stats] like avg_page_space_used_in_percent which indicates on average how full each page in index is. The higher number is better while but it is necessary here to balance fullness against the
number of inserts into index pages in order to keep the number of page splits to the absolute minimum. This exceeds the topic of this blog and requires adjustments of index fillfactor and monitoring of page splits.