SQL Server Secondary Data File Access Failure

The estimated reading time for this post is 5 minutes

 

SQL Server Query Execution Overview

The database component of Microsoft SQL Server is a Structured Query Language (SQL)–based, scalable, relational database with integrated Extensible Markup Language (XML) support for extendable applications. The following describes a fundamental part of the query execution of the SQL Server database engine:

Read Request

query execution 1

As above figure illustrates, below functions are executed in SQL Server (Overview):

1- TDS goes to the SNI component in SQL Server to be decoded into ‘Language Event’;

2- Command Parser parses the TSQL Query to validate the syntax, and uses Algebrizer component to generate ‘Query Tree’.

3- Query Tree been sent to ‘Optimizer’ to generate proper and good enough execution plan.

4- The ‘Query Plan’ sent to ‘Query Executor’ to queue up the sessions and execute the plan.

5- Access Methods component from Storage Engine gets the query plan and asks Buffer Manager to check whether the data pages are already loaded into memory, if it is not loaded yet, Buffer manager reads the data pages from data file(s) and load them into memory.

6- Then the result goes all the way back to SNI component to be encoded into TDS format and send to the client.

Modification Request

query execution 2

As above figure illustrates, below functions are executed in SQL Server (Overview):

1- TDS goes to the SNI component in SQL Server to be decoded into ‘Language Event’;

2- Command Parser parses the TSQL Query to validate the syntax, and uses Algebrizer component to generate ‘Query Tree’.

3- Query Tree been sent to ‘Optimizer’ to generate proper and good enough execution plan.

4- The ‘Query Plan’ sent to ‘Query Executor’ to queue up the sessions and execute the plan.

5- Access Methods component from Storage Engine gets the query plan and asks Buffer Manager to check whether the data pages are already loaded into memory, if it is not loaded yet, Buffer manager reads the data pages from data file(s) and load them into memory and changes the data, then mark the data page as dirty.

6- Access Methods sends the Transaction Records to the Transaction Manager to make the Transaction durable by writing it into the LDF file.

7- Then the result goes all the way back to SNI component to be encoded into TDS format and send to the client.

You may view the number of Dirty pages by executing below script;

SELECT DB_NAME(database_id) AS 'Database', 
COUNT(page_id) AS 'Dirty Pages' 
FROM sys.dm_os_buffer_descriptors WHERE is_modified =1 
GROUP BY DB_NAME(database_id)
ORDER BY COUNT(page_id) DESC

 

The Relational Engine is also sometimes called the query processor because its primary function is query optimization and execution. It contains a Command Parser to check query syntax and pre- pare query trees, a Query Optimizer that is arguably the crown jewel of any database system, and a Query Executor responsible for execution. The Storage Engine is responsible for managing all I/O to the data, and contains the Access Methods code, which handles I/O requests for rows, indexes, pages, allocations and row versions, and a Buffer Manager, which deals with SQL Server’s main memory consumer, the buffer pool. It also contains a Transaction Manager, which handles the locking of data to maintain Isolation (ACID properties) and manages the transaction log.

The other major component you need to know about before getting into the query life cycle is the buffer pool, which is the largest consumer of memory in SQL Server. The buffer pool contains all the different caches in SQL Server, including the plan cache and the data cache, which is covered as the sections follow the query through its life cycle.

 

SQL Server Database Physical Design

A database is similar to a data file in that it is a storage place for data. Like a data file, a database does not present information directly to a user; the user runs an application that accesses data from the database and presents it to the user in an understandable format.

Database systems are more powerful than data files in that data is more highly organized. In a well-designed database, there are no duplicate pieces of data that the user or application must update at the same time. Related pieces of data are grouped together in a single structure or record, and relationships can be defined between these structures and records. When working with data files, an application must be coded to work with the specific structure of each data file. In contrast, a database contains a catalog that applications use to determine how data is organized. Generic database applications can use the catalog to present users with data from different databases dynamically, without being tied to a specific data format.

Microsoft SQL Server data is stored in databases. The data in a database is organized into the logical components visible to users. A database is also physically implemented as two or more files on disk. When using a database, you work primarily with the logical components such as tables, views, procedures, and users. The physical implementation of files is largely transparent. Typically, only the database administrator needs to work with the physical implementation.

db structure 1

Secondary Data File Hard Disk Failure

In some cases, based on the hard disk failure or secondary data file corruption, respective database needs to go into ‘Suspected’ status, which causes the database to be down and the consequences are the business downtime.

basically MDF file must keep system tables and user tables should be on NDF or secondary files to provide higher availability in case of hard disk failure. I would like to continue this blog post based on my experiment about Transaction Log and Secondary Data File Access Failure.

Step 1: Add a Removable Disk to Place Secondary File.

Below scripts create a secondary file into the removable disk. Be informed that this experiment is only for INSERT and SELECT DML operations.

USE [DiskFailureDB]
GO
 
/****** Object:  Table [dbo].[TestTable]    Script Date: 1/24/2016 2:43:22 PM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[TestTable](
	[ID] [BIGINT] IDENTITY(1,1) NOT NULL,
	[Padding] [BINARY](20) NOT NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Secondary]
) ON [Secondary]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[TestTable] ADD  CONSTRAINT [DF_TestTable_Padding]  DEFAULT (0x0FFF) FOR [Padding]
GO

 

 

Step 2: Insert Few Million Records into the TestTable.

INSERT INTO TestTable DEFAULT VALUES
Go 100000000000

 

Step 3: Unplug the Removable Drive and Observe the DML Operation

After you unplug the removable drive, the SQL Server still committing transaction because it only flushes it into the secondary file once the internal recovery fires. You can disable checkpoint process by enabling the ‘-k’ global trace flag. (It is not recommended in production server).

 

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz