SQL Server Architecture

Last month i was taking interview for the post of DBA. I think, DBA should have knowledge of SQL Server Architecture. I had asked so many questions related to SQL Server Architecture but none of the DBAs had given me proper answer. That is why i want to explore SQL Server Architecture information to new DBAs and Developers.

SQL Server Architecture

We can divide SQL Server into three parts.

1. External Protocols
2. Database Engine
3. SQLOS API

External Protocols

There are four parts of External protocols of SQL Server

1. Shared Memory
2. Named Pipes
3. TCP/IP
4. Virtual Interface Adapter(VIA)

Database Engine

We can divide Database engine into two parts

1. Storage Engine
2. Relational Engine(Query Processor)

Storage Engine

The Storage Engine contains some managers to manger storage engine as well as some Utilities and Access Methods

Mangers

1. Transaction Services-
2. File Manager-
3. Buffer Manager-The buffer manager manages disk I/O functions for bringing data and index pages into the data cache so data can be shared among users.
4. Lock Manager- To under stand Lock Manager. We need to understand the SQL Server Lock.

Locks are granted in a relaxed first-in, first-out (FIFO) fashion. Although the order is not strict FIFO, it preserves desirable properties such as avoiding starvation and works to reduce unnecessary deadlocks and blocking. New lock requests where the requestor does not yet own a lock on the resource become blocked if the requested mode is incompatible with the union of granted requests and the modes of pending requests.

A conversion request becomes blocked only if the requested mode is incompatible with the union of all granted modes, excluding the mode in which the conversion request itself was originally granted. A couple exceptions apply to these rules; these exceptions involve internal transactions that are marked as compatible with some other transaction.

Requests by transactions that are compatible with another transaction exclude the modes held by the transactions with which they are compatible from the unions just described. The exclusion for compatible transactions means that it is possible to see what look like conflicting locks on the same resource (for example, two X locks held by different transactions).

The FIFO grant algorithm was significantly relaxed in SQL Server 2005 compared to SQL Server 2000. This relaxation affected requests that are compatible with all held modes and all pending modes. In these cases, the new lock could be granted immediately by passing any pending requests. Because it is compatible with all pending requests, the newly requested mode would not result in starvation. In SQL Server 2000, the new request would not be granted, because, under its stricter FIFO implementation, new requests could not be granted until all previously made requests had been granted. In the following example, connections 1 and 3 would be granted when run against SQL Server 2005 in the specified order. In SQL Server 2000, only connection 1 would be granted:
Utilities

• Bulk Load
• DBCC
• Backup/ Restore

Access Methods

• ROWS
• INDEXES
• VERSIONS
• PAGES
• ALLOCATIONS

Relation Engine(

Query Processor)

In simple words,

It is used to parse, compiles and optimized the Query.

SQL statements are the only commands sent from applications to SQL Server 2005. All the work performed by an instance of SQL Server is the result of accepting, interpreting, and executing SQL statements. The processes by which SQL statements are executed by SQL Server include the following:

• Single SQL Statement Processing
• Stored Procedure and Trigger Execution
• Execution Plan Caching and Reuse
• Parallel Query Processing

Relational Engine(Query Processor) further divided in to five parts.

• Parser, Optimizer-If we execute any query then there are various steps involved in executing the query such as Step1-

  Lexical phase - Here Code break up will happen at this stage. All the code breaks up as individual tokens. like int a. it will get divided into "int" and "a" Step-2>

  Syntax analysis- Here my code gets checked for its syntax.Step 3->

  Semantic phase - Here the semantics will be checked. Step4->

  Intermediate code generation - Its the fourth stage where an intermediate code will be generated by the compiler its like converting the high level code to machine level code.Step5->

  Code Optimization - My compiler will holds some optimization technique to optimize the code so that we can achieve effective execution of our code. Step6->

  Optimized code generation - After code optimization a final code will be generated which can be used to retrieve the data. In sql server this optimized code will be stored and used frequently to fetch the data from the database.Code execution - The optimized code gets executed against the database engine.
• SQL Manager-
• Database Manager
• Query Executer

SQLOS API

We can devide SQLOS API into two parts

1. SQLOS
2. External Components(Hosting API)

SQL OS

There are seven major components of SQLOS

1. Lock Manager
2. Synchronization Services
3. Thread Scheduler
4. Buffer Pool
5. Memory Manager
6. I/O Manger
7. Workers Threads

Buffer Pool-All memory not used by another memory component remains in the buffer pool to be used as a data cache for pages read in from the database files on disk. The buffer manager manages disk I/O functions. When other components require memory, they can request a buffer from the buffer pool. A buffer is a page in memory that's the same size as a data or index page. You can think of it as a page frame that can hold one page from a database. Most of the buffers taken from the buffer pool for other memory components go to other kinds of memory caches, the largest of which is typically the cache for procedure and query plans, which is usually called the procedure cache.

Top 10 SQL Server 2008 Features for the Database Administrator

• Activity Monitor-. SQL Server 2008 Activity Monitor consolidates information about what processes are executing and where is the problem by detailing running and recently executed processes

• [SQL Server] Audit- SQL Server Audit provides automation that allows the DBA and others to enable, store, and view audits on various server and database components

• Backup Compression

• Central Management Servers-Central Management Servers allow the DBA to register a group of servers and apply functionality to the servers

• Data Collector and Management Data Warehouse

• Data Compression-The ability to easily manage a database can greatly enhance the opportunity for DBAs to accomplish their regular task lists.

• Policy-Based Management-Policy-Based Management (PBM) provides DBAs with a wide variety of options in managing their environment. Policies can be created and checked for compliance.

• Predictable Performance and Concurrency-SQL Server 2008 provides a few feature changes that can help provide more predictable performance.

• Resource Governor-SQL Server 2008 introduces a new feature called Resource Governor, which helps address this issue by enabling users to differentiate workloads and allocate resources as they are requested.

• Transparent Data Encryption (TDE)-

  SQL Server 2008 offers a solution to this problem by way of transparent data encryption (TDE). TDE performs real-time I/O encryption and decryption of the data and log files by using a database encryption key (DEK). The DEK is a symmetric key secured by using a certificate stored in the master database of the server, or an asymmetric key protected by an Extensible Key Management (EKM) module.