SQL Server DBA Interview Questions and Answers – SQL Server Services

Hi friend posted questions related to SQL SERVER  services in this section I have mentioned all services.

1) List out various services which are important for SQL Server and its 
components functionality?

• SQL Server Service
• SQL Server Agent service
• SQL Server Analysis Service
• SQL Server Browser service
• SQL Server Integration Service
• SQL Server Reporting Service
• SQL Server VSS Writer service
• Distributed Transaction Coordinator
• SQL Server Active Directory Helper
• SQL Full-Text Filter Daemon Launcher
• SQL Server Distributed Replay Client service
• SQL Server Distributed Replay Client service

2) What is SQL Server service and its importance?
SQL Server service is core of SQL Server instance. It runs the Database Engine and 
executes the client requests related to data processing. If this service is not 
running, no users can connect to the any of the database, hence users will not be 
able to fetch, insert, update or delete the data.

3) What is SQL Server Agent service and its importance?
SQL Server Agent is the primary scheduling engine in SQL Server. This is used to 
execute scheduled administrative tasks like SSIS Packages, T-SQL Scripts, Batch 
Files and Subscriptions etc. which are referred to as Jobs. It uses msdb database 
to store the configuration, processing, and metadata information. Apart from SQL Server Agent related information, msdb database also stores similar information 
related to Backup, Restore, Log Shipping, SSIS Packages etc.

4) What is SQL Server Analysis service and its importance?
Microsoft SQL Server Analysis Services (SSAS) delivers online analytical 
processing (OLAP) and data mining functionality for business intelligence 
applications. Analysis Services supports OLAP by letting you design, create, and 
manage multidimensional structures that contain data aggregated from other 
data sources, such as relational databases. For data mining applications, Analysis 
Services lets you design, create, and visualize data mining models that are 
constructed from other data sources by using a wide variety of industry-standard 
data mining algorithms.

5) What is SQL Server Integration service and its importance?
SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server 
database software that can be used to perform a broad range of data migration 
tasks. SSIS is a platform for data integration and workflow applications. It features 
a fast and flexible data warehousing tool used for data extraction, transformation, 
and loading (ETL). The tool may also be used to automate maintenance of SQL 
Server databases and updates to multidimensional cube data.

6) What is SQL Server Browser?
This service acts as a listener for the incoming requests for Microsoft SQL Server 
resources. It provides information about the list of installed SQL Server instances on the computer to the client computers/applications. It helps in browsing the list 
of servers, locating and connecting to the correct server.
This listener service responds to client requests with the names of the installed 
instances, and the ports or named pipes used by the instance.

7) What is SQL Server Reporting Services?
This service is primarily used by SQL Server Reporting Services (SSRS) for 
browsing and viewing the reports on Reports Server, through Report Server or 
Report Manager interface. It is used to manage the shared data sources, reports, 
shared data sets, report parts, folder, etc. hosted on the Report Server. Reporting 
services are managed using the Reporting Services Configuration Manager.

8) What is SQL Server VSS Writer?
The SQL Writer Service provides added functionality for backup and restore of 
SQL Server through the Volume Shadow Copy Service framework. When running, 
Database Engine locks and has exclusive access to the data files. When the SQL 
Writer Service is not running, backup programs running in Windows do not have 
access to the data files, and backups must be performed using SQL Server 
backup.

Use the SQL Writer Service to permit Windows backup programs to copy SQL 
Server data files while SQL Server is running. It must be running when the Volume 
Shadow Copy Service (VSS) application requests a backup or restore. To 
configure the service, use the Microsoft Windows Services applet. The SQL Writer 
Service installs on all operating systems.

9) Which types of backups are supported by SQL Write Service?
SQL Writer supports:

• Full database backup and restore including full-text catalogs
• Differential backup and restore
• Restore with move
• Copy-only backup
• Auto-recovery of database snapshot

10) Which types of backups are not supported by SQL Write Service?
SQL Writer does not support:

• Log backups
• File and filegroup backup
• Page restore

11) What is Full-Text Search service?
This service is used by the full-text search feature of SQL Server. It helps in 
starting the filter daemon host process, which manages the full-text indexing, 
querying, search filtering and word processing as part of the full-text search 
feature.

12) What is SQL Server Active Directory Helper?
This service enables the integration with the Active Directory. Irrespective of 
number of instances of SQL Servers installed on a computer, there is always only 
one instance of SQL Server Active Directory Helper service. This service is 
automatically started by SQL Server when required and is stopped once the 
operation is completed. This service is required whenever an SQL Server object 
needs to be created in the Active Directory to register an instance of SQL Server.

13) What is Distributed Transaction Coordinator?
This service coordinates distributed transactions between two or more database 
servers. Client applications use this service to work with data from multiple 
sources in one transaction. There is always only one instance of MSDTC service 
running on a computer irrespective of how many SQL server instances are 
installed. This service should be running on each of the servers which handle 
distributed transactions. This service is not a part of SQL Server installation. This 
service is installed with Windows OS installation.

14) How to check how many SQL Server instances are installed on a Window 
Server?
There are multiple ways through which we can check the No. of SQL Server 
instances which are running on a server like:

• Check the SQL services for different Instances
• SQL Server Configuration Manager Start- all programs – Microsoft SQL Server 
• 2008 R2 -> configuration tools –> Microsoft SQL Server configuration 
• Manager
• List out SQL Services from Net Start command
• Using Powershell commands
• Readging the Registry Keys, Regedit-> HKEY_LOCAL_MACHINE–>SOFTWARE ->Microsoft ->Microsoft SQL Server –> InstalledInstance

15) What are the different ways to start and Stop SQL Server services?
There are different ways through which we can start or stop SQL Server services.

a) Go to Services –> Look for SQL server service related to the Instance
Named Instance: SQL Server(Instance Name)
Default Instance: SQL Server(MSSQLServer)

b) Right Click on the SQL Server instance in management studio and click on 
restart

c) Go to SQL Server Configuration Mananger (SQLServermanager10.msc) and 
right click on the services and click restart.

d) Net stop command
e) use “Net START” command to list all the running services

f) Use “Net STOP MSSQL$Instancename” to stop the SQL Service for a particular 
instance

17) What are the default parameters of SQL Server service start up process 
and from where these parameters can be changed?
master database data and log file and error log files are the default parameters 
which are passed to SQL Server service.
-dC:\Program Files\Microsoft SQL 
Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf;
-eC:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG;
-lC:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
We can add trace flags and other parameters to the startup process from SQL 
Server Configuration manager.

18) How to start the SQL Server with minimal configuration?
If there are any configuration problems that prevent the server from starting, you 
can start an instance of Microsoft SQL Server by using the minimal configuration 
startup option. This is the startup option -f. Starting an instance of SQL Server 
with minimal configuration automatically puts the server in single-user mode.

19) How to start SQL Server with single user model?
Under certain circumstances, we may have to start an instance of SQL Server in 
single-user mode by using the startup option -m. For example, you may want to 
change server configuration options or recover a damaged master database or 
other system database. Both actions require starting an instance of SQL Server in 
single-user mode.

20) What are trace flags and how can we apply trace flags on a SQL Server 
instance?
Trace flags are used to temporarily set specific server characteristics or to switch 
off a particular behavior.

For example, if trace flag 3205 is set when an instance of 
SQL Server starts, hardware compression for tape drivers is disabled. Trace flags 
are frequently used to diagnose performance issues or to debug stored 
procedures or complex computer systems.

21) How the trace flags are enabled?
Trace flags are enabled at different levels.

• Global
• Session

Instance level
We can use the -T option in the startup configuration for the SQL Server Service 
to enable trace at instance level.

Session Level
We can use the DBCC TRACEON and DBCC TRACEOFF commands to enable it on 
a session level.

22) How do I know what Trace Flags are turned on at the moment?
We can use the DBCC TRACESTATUS command

23) Name some of the Important Trace flags and their functionality?
Trace Flag: 1204

This trace flag lets SQL Server to log detailed deadlock information to SQL Server Error Log in text format. In SQL Server 2008, this trace flag is only available at the 
Global Level (i.e. applies to the SQL Server instance). In my experience, it is worth 
turning this trace flag on, only for debugging purposes.

Trace Flag: 1222
Similar to trace flag 1204, this trace flag lets SQL Server to log detailed deadlock 
information to SQL Server Error Log in XML format.

Trace Flag: 3226
In an environment where database backup is frequently performed, it is a good 
idea to turn trace flag 3226 on, to suppress an entry to the SQL Server Error Log 
for each backup. This is beneficial as the bigger the log, the harder it is to find 
other messages. However, you will need to ensure that none of your scripts or 
systems rely on the backup entry detail on the SQL Server Error Log.

24) What are the mandatory databases to bring SQL Services up?
master, model, resource db, tempdb location.

25) Which system database is associated with SQL Server agent service?
msdb

26) What is Protocol is used by SQL Server Browser service?
SQL Server Browser service uses UDP protocol.

27) Which Port no. is used by SQL Server Browser service ?
1434

28) What will happen if SQL Server Browser service is stopped?
If the SQL Server Browser service is not running, the following connections do not 
work:

• If we have just one instance installed on machine and it is running on default port 1433, then status of SQL Server Browser service does not make any difference in our connection parameters.
• If there are more than one instances running on the same machine, in that case either we have to start SQL Server Browser service or provide the port number along with IP (or server name) and instance name, to access any other instance than default
• If SQL Server Browser service is stopped and IP along with port number is not provided then connection will be refused.
• If SQL Server instance is configured using dynamic ports then browser service is required to connect to correct port number.
• Also our named instances will not be published in the list of SQL Server instances on the network (which could be a good thing)

29) What is the high Level SQL Server start up process?

• The service is authenticated by verifying the credentials provided in the logon account and the service is started.
• PID is allocated at windows level
• Authentication mode details are verified i.e either MIXED or WINDOWS
• Information of the startup parameters is captured i.e mdf location of master database, SQL Server error log location and ldf file location
• Some memory and CPU settings done at windows level, this is an informational message only
• Starts the master database
• model is the next database to start
• set the port related information
• Tempdb is recreated each time when we restart sql server
• Start msdb and other user databases based on dbid

30) What is Distributed Replay?
Distributed Replay is a new functionality of Microsoft SQL Server 2012. It helps 
you assess the impact of future upgrades (SQL Server, hardware, OS) or SQL 
Server tunings by replaying a trace captured from a productive SQL Server 
environment to a new SQL Server test environment.

This new functionality is similar to SQL Server Profiler, but with more possibilities: 
e. g. replaying the trace from multiple clients (up to sixteen), use a stress or
synchronization mode, configure options like think time, or connect time etc.

31) What are the various components involved in Distributed Replay 
Concepts?
The following components make up the Distributed Replay environment:

Distributed Replay administration tool: A console application, DReplay.exe, 
used to communicate with the distributed replay controller. Use the 
administration tool to control the distributed replay.

Distributed Replay controller: A computer running the Windows service named 
SQL Server Distributed Replay controller. The Distributed Replay controller 
orchestrates the actions of the distributed replay clients. There can only be one 
controller instance in each Distributed Replay environment.

Distributed Replay clients: One or more computers (physical or virtual) running 
the Windows service named SQL Server Distributed Replay client. The Distributed 
Replay clients work together to simulate workloads against an instance of SQL 
Server. There can be one or more clients in each Distributed Replay environment.

Target server: An instance of SQL Server that the Distributed Replay clients can 
use to replay trace data. We recommend that the target server be located in a 
test environment.

SQL Server DBA Interview Questions and Answers-MS SQL Installation

Hi friend

I posted SQL server installation related question and answer for interview.

SQL Server DBA Interview Questions and Answers-MS SQL Installation

Q1. What are the various Editions available in SQL Server 2012 version?
Below are the various editions available in Microsoft SQL Server 2012

• SQL Server 2012 Standard Edition
• SQL Server 2012 Enterprise Edition
• SQL Server 2012 Business Edition
• SQL Server 2012 Express Editions
• SQL Server 2012 Web and Developer Editions

Q2. What are the major differences between paid editions – Standard,
Enterprise and Business Editions in SQL Server 2012?

SQL Server 2012 Capabilities	Enterprise	Business Intelligence	Standard
Maximum No. of Cores	OS Max*	16 Cores for DB – OS Max for BI	16 Core
Basic Reporting & Analytics	Yes	Yes	Yes
Enterprise data Management (Data Quality Services, Master Data Services)	Yes	Yes	No
Self-Service Business Intelligence (Power View, PowerPivot for SPS)	Yes	Yes	No
Corporate Business Intelligence (Semantic model, advanced analytics)	Yes	Yes	No
Advanced Security (Advanced auditing, transparent data encryption)	Yes	No	No
Data Warehousing (ColumnStore, compression, partitioning)	Yes	No	No
High Availability (Always ON)	Advanced	Basic	Basic
Maximum memory utilized (per instance of SQL Server Database Engine)	OS max	64 GB	64 GB
Server Core support	Yes	Yes	Yes
Backup compression	Yes	Yes	Yes
Database snapshot	Yes	No	No
Online indexing	Yes	No	No
Data compression	Yes	No	No
Resource Governor	Yes	No	No

Q3. What are the minimum Software requirements to install SQL Server
2012?

• Internet Explorer 7 or a later version is required for Microsoft Management Console (MMC), SQL Server Data Tools (SSDT), the Report Designer component of Reporting Services, and HTML Help
• SQL Server 2012 does not install or enable Windows PowerShell 2.0; however Windows PowerShell 2.0 is an installation prerequisite for Database Engine components and SQL Server Management Studio.
• NET 3.5 SP1 is a requirement for SQL Server 2012 when you select Database Engine, Reporting Services, Replication, Master Data Services, Data Quality Services, or SQL Server Management Studio, and it is no longer installed by SQL Server Setup.
• Dot NET 4.0 is a requirement for SQL Server 2012. SQL Server installs .NET 4.0 during the feature installation step.  SQL Server Express does not install .NET 4.0 when installing on the Windows 2008 R2 SP1 Server core operating system. You must install .NET4.0 before you install SQL Server Express on a Windows 2008 R2 SP1 Server core operating system.

SQL Server Setup installs the following software components required by the product:

• Dot NET Framework 4 1
• SQL Server Native Client
• SQL Server Setup support files

Q4. What are the minimum Hardware requirements to install SQL Server
2012 Paid editions?
   

SQL Server Editions	Memory(RAM)	CPU
SQL Server 2012 Enterprise (64-bit) x64	1GB	1.4 GHz AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support
SQL Server 2012 Business Intelligence (64-bit) x64	1GB	1.4GHz AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support
SQL Server 2012 Standard (64-bit)	1GB	1.4GHz AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support
SQL Server 2012 Enterprise (32-bit)	1GB	1GHz Pentium III-compatible processor or faster
SQL Server 2012 Business Intelligence (32-bit)	1GB	1GHz Pentium III-compatible processor or faster
SQL Server 2012 Standard (32-bit)	1GB	1GHz Pentium III-compatible processor or faster

Q5. Where will you find the SQL Server installation related logs?
Installation related logs are stored under the shared feature directory folder which was selected at the time of first SQL Server instance installation.  e.g. If Shared Features were selected to  be placed on the “C:\Program Files\Microsoft SQL Server” then logs will be created under
 %PROGRAMFILES%\MICROSOFT SQL SERVER\110\SETUP BOOTSTRAP\LOG\<YYYYMMDD_HHMM>\
C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20190910_222838 

Q6. What is “ConfigurationFile.ini” file?
SQL Server Setup generates a configuration file named ConfigurationFile.ini, based upon the system default and run-time inputs. The ConfigurationFile.ini file is a text file which contains the set of parameters in name/value pairs along with descriptive comments. Many of the parameter names correspond to the screens and options which you see while installing SQL Server through the wizard.  We can then use the configuration file to install SQL Server with the same configuration instead of going through each of the installation screens.

Q7. What is the location of ConfigurationFile.ini file?
We can find the configuration file in the C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log folder. There will a subfolder based on a timestamp of when the SQL Server 2012 installation was done.

Q8. What is a service account?
Based on the selected components while doing the installation we will find respective service to each component in the Windows Services. e.g. SQL Server, SQL Server Agent, SQL Analysis Services, SQL Server integration Services etc. There will be a user for each and every service through which each service will run. That use is called Service Account of that service.

Mainly we categorize the Service account as below:
Local User Account: This user account is created in the server where SQL Server is installed; this account does not have access to network resources.
Local Service Account: This is a builtin windows account that is available for configuring services in windows. This account has permissions as same as accounts that are in the users group, thus it has limited access to the resources in the server.
Local System Account: This is a builtin windows account that is available for configuring services in windows. This is a highly privileged account that has access to all resources in the server with administrator rights.
Network Service Account: This is a builtin windows account that is available for configuring services in windows. This has permissions to access resources in the network under the computer account.
Domain Account: This account is a part of our domain that has access to network resources for which it is intended to have permission. It is always advised to run SQL Server and related services under a domain account with minimum privilege need to run SQL Server and its related services.

Q9. Do we need to grant Administrator permissions on the Windows
server to SQL Service account to run the services or not, why?
No, it is not required. It’s not mandatory to grant Administrator permissions to the service account.

Q10. What permissions are required to install SQL Server on a server?
User through which we are installing SQL Server must have administrator permissions on the Windows server.

Q11. What are Shared Features Directory and its usages?
This directory contains the common files used by all instances on a single computer e.g. SSMS, sqlcmd, bcp, DTExec etc. These are installed in the folder <drive>:\Program Files\Microsoft SQL Server\110\ , where <drive> is the drive letter where components are installed. The default is usually drive C.

Q12. What is an Instance?
An instance of the Database Engine is a copy of the sqlservr.exe executable that runs as an operating system service. Each instance manages its own system databases and one or more user databases. An instance is a complete copy of an SQL Server installation.


Q13. Type of Instance and maximum no. of instances which can be
installed on a server
There are two types of Instances.

• Default instance
• Named Instance

Each computer can run maximum of 50 instances of the Database Engine.  One instance can be the default instance.
The default instance has no name. If a connection request specifies only the name of the computer, the connection is made to the default instance.
A named instance is one where you specify an instance name when installing the instance. A connection request must specify both the computer name and instance name in order to connect to the instance.

Q14. Can we install multiple instances on the same disk drive?
Yes, we can install multiple instances on the same disk drive because each installation creates its own folder with the below format.

MSSQL11.INSTANCENAME

Q15. What is a collation and what is the default collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.
DEFAULT COLLATION:  SQL_LATIN1_GENERAL_CP1_CI_AS
Q16. What is an RTM setup of SQL Server?
RTM stands for release to manufacturing.

Q17. What is a Service Pack, Patch, Hot fix and its difference?
Service Pack is abbreviated as SP, a service pack is a collection of updates and fixes, called patches, for an operating system or a software program. Many of these patches are often released before the larger service pack, but the service pack allows for an easy, single installation.
Patch – Publicly released update to fix a known bug/issue
Hotfix – update to fix a very specific issue, not always publicly released
Q18. What is the latest Service pack available for SQL Server 2012 in the market?
SQL Server 2012   Service Pack 2 (SP2)
Version: 11.0.5058.0
Release Date: 6/10/2014
Q19. What’s the practical approach of installing Service Pack?
Steps to install Service pack in Production environments:

• First of all raise a change order and get the necessary approvals for the downtime window. Normally it takes around 45-60 minutes to install Service pack if there are no issues.
• Once the downtime window is started, take a full backup of the user databases and system databases including the Resource database.
• List down all the Startup parameters, Memory Usage, CPU Usage etc and save it in a separate file.
• Install the service pack on SQL Servers.
• Verify all the SQL Services are up and running as expected.
• Validate the application functionality.

Note: There is a different approach to install Service pack on SQL Server cluster instances. That will be covered in SQL Server cluster.
Q20. Is it mandatory to restart the Windows server after installing SQL server service pack?
No, it’s not mandatory to restart Windows server after installing SQL Server service pack but it is always a good practice to do so.
Q21. How to check the SQL Server version and Service pack installed on the server?
select convert(varchar(50),SERVERPROPERTY('productversion')) ,
convert(varchar(50),SERVERPROPERTY ('productlevel')) ,
convert(varchar(50),SERVERPROPERTY ('edition'))
Or
select @@VERSION
Q22. How to check SQL Server name?
Select @@Servername


Q23. What is a slip stream installation and its usages?
SQL Server 2008 introduced a concept that’s called “Slipstream Installation”. This is a way to deploy a SQL Server instance with all the needed Service pack as part of the installation. Everything will be installed in one go, hence there is no need to deploy any other service packs on the installation.
Q24. What is a silent installation and how can we use this feature?
The procedure to install SQL Server instance through command line using ConfigurationFile.ini file in Quite mode is known as Silent installation.
Q25. What is the default port of a SQL Server instance?
SQL Server default instance by default listen on 1433 port.
Q26. Can we change the default port of SQL Server, How?
Yes, it is possible to change the Default port on which SQL Server is listening.
Step 1. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools >SQL Server Configuration Manager
Step 2. Go to SQL Server Configuration Manager > SQL Server Network Configuration >Protocols for <Instance Name>

Step 3. Right Click on TCP/IP and select Properties

Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAllgroup. Now change the value to static value which you want to set for SQL Server port.

Q27. How to get the port number where the SQL Server instance is listening?
Below are the methods using which we can get the port information.
Method 1: SQL Server Configuration Manager
Method 2: Windows Event Viewer
Method 3: SQL Server Error Logs
Method 4: sys.dm_exec_connections DMV
Method 5: Reading registry using xp_instance_regread


Q28. What is a Filestream?
FILESTREAM was introduced in SQL Server 2008 for the storage and management of unstructured data. The FILESTREAM feature allows storing BLOB data (example: word documents, image files, music and videos etc) in the NT file system and ensures transactional consistency between the unstructured data stored in the NT file system and the structured data stored in the table.
Q29. What’s the location of SQL Server log files?
SQL Server error logs are stored in the below location.
INSTANCE ROOT DIRECTORY\MSSQL\LOG
Q30. How many SQL Server log files can be retained in the SQL Server error logs by default?
By default, there are seven SQL Server error logs; Errorlog and Errorlog.1 through Errorlog.6. The name of the current, most recent log is Errorlog with no extension. The log is re-created every time that you restart SQL Server. When the Error log file is re-created, the previous log is renamed to Errorlog.1, and the next previous log (Errorlog.1) is renamed to Errorlog.2, and so on. Errorlog.6 is deleted.
Q31. Is it possible to increase the retention of Error log files and How?
Yes it is possible to change the no. of Error logs retention. We can follow the below steps to change the Error log file retention.

• Open SQL Server Management Studio and then connect to SQL Server Instance
• InObject Explorer, ExpandManagement Node and then right click SQL Server Logs and click Configure as shown in the snippet below.
• In Configure SQL Server Error Logs window you can enter the value between 6 and 99 for the number of error logs and click OK to save the changes

SQL Server DBA Interview Questions and Answers - SQL Server Architecture

1. Tell me something about the SQL Server Architecture? 

SQL Server is divided into two main engines: the Relational Engine and the Storage Engine.

The Relational Engine contains below components:

• Cmd Parser 
• Optimizer 
• Query Executor 

The Storage Engine contains below components:

•  Access Methods code
•  Buffer Manager
• Transaction Manager

2. What is Relational Engine and its Role? 

The Relational Engine is also sometimes called the query processor because its primary function is query optimization and execution.

The main responsibilities of the relational engine are: 

Parsing the SQL statements. 
The parser scans an SQL statement and breaks it down into the logical units, such as keywords, parameters, operators, and identifiers. The parser also breaks down the overall SQL statement into a series of smaller logical operations.

Optimizing the execution plans. 
Typically, there are many ways that the server could use data from the source tables to build the result set. The query optimizer determines what these various series of steps are, estimates the cost of each series (primarily in terms of file I/O), and chooses the series of steps that has the lowest cost. It then combines the specific steps with the query tree to produce an optimized execution plan. 

Executing the series of logical operations defined in the execution plan.
After the query optimizer has defined the logical operations required to complete a statement, the relational engine steps through these operations in the sequence specified in the optimized execution plan.

Processing Data Definition Language (DDL) and other statements.
These statements are not the typical SELECT, INSERT, UPDATE, or DELETE statements; these statements have special processing needs. Examples are the SET statements to set connection options, and the CREATE statements to create objects in a database.

Formatting results. 
The relational engine formats the results returned to the client. The results are formatted as either a traditional, tabular result set or as an XML document. The results are then encapsulated in one or more TDS packets and returned to the application.

3. What is Storage Engine and its Role? 
The Storage Engine is responsible for managing all I/O to the data.The main responsibilities of the storage engine include: 

• Managing the files on which the database is stored and managing the use of space in the files.
• Building and reading the physical pages used to store data.
• Managing the data buffers and all I/O to the physical files.
• Controlling concurrency. Managing transactions and using locking to control concurrent user access to rows in the database.
• Logging and recovery.
• Implementing utility functions such as the BACKUP, RESTORE, and DBCC statements and bulk copy.

4. What is SNI Protocol Layer? 

SQL Server Network Interface (SNI) is a protocol layer that establishes the network connection between the client and the server. It consists of a set of APIs that are used by both the database engine and the SQL Server Native Client (SNAC). SQL Server has support for the following protocols:

• Shared memory 
• TCP/IP
• Named Pipes
• VIA — Virtual Interface Adapter

5. What are Tabular Data Stream (TDS) Endpoints? 
TDS is a Microsoft-proprietary protocol originally designed by Sybase that is used to interact with a database server. Once a connection has been made using a network protocol such as TCP/IP, a link is established to the relevant TDS endpoint that then acts as the communication point between the client and the server. 

6. What is a Command Parser? 
The Command Parser’s role is to handle T-SQL language events. It first checks the syntax and returns any errors back to the protocol layer to send to the client. If the syntax is valid, then the next step is to generate a query plan or find an existing plan. A query plan contains the details about how SQL Server is going to execute a piece of code. It is commonly referred to as an execution plan. To check for a query plan, the Command Parser generates a hash of the T-SQL and checks it against the plan cache to determine whether a suitable plan already exists. The plan cache is an area in the buffer pool used to cache query plans. If it finds a match, then the plan is read from cache and passed on to the Query Executor for execution. Otherwise an Execution plan is created by the optimizer. 


7.What is an Execution Plan? 
An execution plan is composed of primitive operations. Examples of primitive operations are: reading a table completely, using an index, performing a nested loop or a hash join. All primitive operations have an output: their result set. Some, like the nested loop, have one input. Other, like the hash join, has two inputs. Each input should be connected to the output of another primitive operation. That’s why an execution plan can be sketched as a tree: information flows from leaves to the root.

8. What is a Plan Cache? 
Plan cache is the part of SQL Server’s buffer pool, is used to store execution plans in case they are needed later when the same type of scripts are submitted by the users. 

9. What is the role of an Optimizer? 
The Optimizer is one of the important assets of a database engine. This is the component on which a particular RDBMS stands off. The primary function of the optimizer is to generate execution plan. 

10. What is Query Executor? 
The Query Executor’s job is self-explanatory; it executes the query. To be more specific, it executes the query plan by working through each step it contains and interacting with the Storage Engine to retrieve or modify data. 

11. What are Access methods and its roles?
 Access Methods is a collection of code that provides the storage structures for your data and indexes, as well as the interface through which data is retrieved and modified. It contains all the code to retrieve data but it doesn’t actually perform the operation itself; it passes the request to the Buffer Manager. 

Suppose our SELECT statement needs to read just a few rows that are all on a single page. The Access Methods code will ask the Buffer Manager to retrieve the page so that it can prepare an OLE DB rowset to pass back to the Relational Engine.


12. What is a Buffer Manager? 
The buffer management component consists of two mechanisms: the buffer manager to access and update database pages, and the buffer cache (also called the buffer pool), to reduce database file I/O.

The Buffer Manager, as its name suggests, manages the buffer pool, which represents the majority of SQL Server’s memory usage. If you need to read some rows from a page, the Buffer Manager checks the data cache in the buffer pool to see if it already has the page cached in memory. If the page is already cached, then the results are passed back to the Access Methods.

 If the page isn’t already in cache, then the Buffer Manager gets the page from the database on disk, puts it in the data cache, and passes the results to the Access Methods.

13. What is a Buffer pool?
What is the importance of Data cache? Buffer Pool consist of various type of cache like data cache, plan cache, log cache etc. Here data cache is the very important part of buffer pool which is used to store the various types of pages to serve particular query. Suppose if we run a particular select query on a table to show all data rows of that table. Then all the data pages of that table will be required to fulfill the requirement of this query. Here first all data pages will move from disk to buffer pool. This operation of reading data pages from disk to memory is known as physical IO. But if we running the same query again then there is no need to read data pages from disk to buffer pool because all the data pages are already in buffer pool. This operation is known as Logical IO.

14. What is the Data cache?
The data cache is usually the largest part of the buffer pool; therefore, it’s the largest memory consumer within SQL Server. It is here that every data page that is read from disk is written to before being used. The sys.dm_os_buffer_descriptors DMV contains one row for every data page currently held in cache. You can use this script to see how much space each database is using in the data cache:

SELECT count(*)*8/1024 AS 'Cached Size (MB)' ,CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END AS 'Database' FROM sys.dm_os_buffer_descriptors GROUP BY db_name(database_id),database_id ORDER BY 'Cached Size (MB)' DESC

15. What is a Transaction manager and its role? 
Transaction Manager interacts with the Access Methods and has two components through which it works on the transactions. 

Lock Manager: It is responsible for providing concurrency to the data, and it delivers the configured level of isolation by using locks. 

Log Manager: It writes the changes to the transaction log. Writing to the transaction log is the only part of a data modification transaction that always needs a physical write to disk because SQL Server depends on being able to reread that change in the event of system failure

16. What is Write Ahead Logging? 
At the time a modification is made to a page in the buffer, a log record is built in the log cache recording the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record. Because log records are always written ahead of the associated data pages, the process is called a write-ahead logging.


17. What are dirty pages?
When a page is read from disk into memory it is regarded as a clean page because it’s exactly the same as its counterpart on the disk. However, once the page has been modified in memory it is marked as a dirty page. 

A dirty page is simply a page that has changed in memory since it was loaded from disk and is now different from the on-disk page.


18. Which DMV can be used to check how many dirty pages exists in the memory for each database? 
We can use the following query, which is based on the sys.dm_os_buffer_descriptors DMV, to see how many dirty pages exist in each database:

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

 19. How is the dirty page written to disk?

Dirty pages are written to disk on the following events.

• Lazy writing is a process to move pages containing changes from the buffer onto disk. This clears the buffers for us by other pages.
• Checkpoint writes all dirty pages to disk. SQL Server periodically commits a CHECKPOINT to ensure all dirty pages are flushed to disk.
• Explicitly issuing a CHECKPOINT will force a checkpoint

Examples of events causing a CHECKPOINT

• net stop mssqlserver
• SHUTDOWN
• ALTER DATABASE adding a file
• Eager writing – Nonlogged bcp, SELECT INTO, WRITETEXT,UPDATETEXT,BULK INSERT are examples of non-logged operations. To speed up the tasks , eager writing manages  page creation and page writing in parallel. The requestor does not need to wait for all the page creation to occur prior to  writing pages

20. What is a check point?
A checkpoint is a point in time created by the checkpoint process at which SQL Server can be sure that any committed transactions have had all their changes written to disk. This checkpoint then becomes the marker from which database recovery can start. The checkpoint process ensures that any dirty pages associated with a committed transaction are flushed to disk.

21. What is the frequency of checkpoint in an ideal scenario?
The Database Engine supports several types of checkpoints: automatic, indirect, manual, and internal. The following table summarizes the types of checkpoints.

a. Automatic
Transact-SQL Interface
EXEC sp_configure'recovery interval','seconds'
Description: Issued automatically in the background to meet the upper time limit suggested by the recovery interval server configuration option. Automatic checkpoints run to completion. Automatic checkpoints are throttled based on the number of outstanding writes and whether the Database Engine detects an increase in write latency above 20 milliseconds.

b. Indirect
Transact-SQL Interface
ALTER DATABASE … SET TARGET_RECOVERY_TIME =target_recovery_time{ SECONDS | MINUTES }
Description Issued in the background to meet a user-specified target recovery time for a given database. The default target recovery time is 0, which causes automatic checkpoint heuristics to be used on the database. If you have used ALTER DATABASE to set TARGET_RECOVERY_TIME to >0, this value is used, rather than the recovery interval specified for the server instance.

c. Manual
Transact-SQL Interface
CHECKPOINT [ checkpoint_duration ]
Description Issued when you execute a Transact-SQL CHECKPOINT command. The manual checkpoint occurs in the current database for your connection. By default, manual checkpoints run to completion. Throttling works the same way as for automatic checkpoints. Optionally, the checkpoint_duration parameter specifies a requested amount of time, in seconds, for the checkpoint to complete.

d. Internal
Transact-SQL Interface
None.
Description Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.

22. What is LazyWriter?
Lazywriter also flushes dirty pages to disk. SQL Server constantly monitors memory usage to assess resource contention (or availability); It’s job is to make sure that there is a certain amount of free space available at all times. As part of this process, when it notices any such resource contention, it triggers LazyWriter to free up some pages in memory by writing out dirty pages to disk. It employs Least Recently Used (LRU) algorithm to decide which pages are to be flushed to the disk.

23. What is log flush?
Log Flush also writes pages to disk. The difference here is that it writes pages from Log Cache into the Transactional log file (LDF). Once a transaction completes, LogFlush writes those pages (from Log Cache) to LDF file on disk.

Each and every transaction that results in data page changes, also incurs some Log Cache changes. At the end of each transaction (commit), these changes from Log Cache are flushed down to the physical file (LDF).

24. What is the difference between check point lazy writer?


25. What are ghost records in SQL server?
When a record is deleted from a clustered index data page or non-clustered index leaf page or a versioned heap page or a forwarded record is recalled, the record is logically removed by marking them as deleted but not physically removed from the page immediately. Pages which are marked as deleted but actually not deleted physically are called Ghost Records.

26. Which process removes the records which are marked as Ghost Records?

Ghostcleanuptask: SQL Server Ghostcleanuptask thread physically removes the records which are marked as deleted.

27. How Ghost cleanup task works?

• Ghostcleanuptask thread wakes up every 10 seconds.
• Sweepdatabases one by one starting from master.
• Skip the database if it is not able to take a shared lock for database (LCK_M_S) or database is not in Open read/write state.
• Scans the PFS pages of the current database to get the pages which has ghost records.
• PFS Page:A PFS page occurs once in 8088 pages. SSQL Server will attempt to place a PFS page on the first page of every PFS interval(8088Pages). The only time a PFS page is not the first page in its interval is in the first interval for a file.
• In this case, the file header page is first, and the PFS page is second. (Page ID starts from 0 so the first PFS page is at Page ID 1)
• Remove the records which are marked as deleted (ghosted) physically

28. What is the different protocol supported by SQL server, explain each of these?

• Shared memory — Simple and fast, shared memory is the default protocol used to connect from a client running on the same computer as SQL Server. It can only be used locally, has no configurable properties, and is always tried first when connecting from the local machine.
• TCP/IP — This is the most commonly used access protocol for SQL Server. It enables you to connect to SQL Server by specifying an IP address and a port number. Typically, this happens automatically when you specify an instance to connect to. Your internal name resolution system resolves the hostname part of the instance name to an IP address, and either you connect to the default TCP port number 1433 for default instances or the SQL Browser service will find the right port for a named instance using UDP port 1434.
• Named Pipes — TCP/IP and Named Pipes are comparable protocols in the architectures in which they can be used. Named Pipes was developed for local area networks (LANs) but it can be inefficient across slower networks such as wide area networks (WANs).
• VIA — Virtual Interface Adapter is a protocol that enables high-performance communications between two systems. It requires specialized hardware at both ends and a dedicated connection.

29. What is HOT ADD CPU term in SQL server?
SQL Server 2008 increases these capabilities by adding hot-add CPU as well. ‘Hot ADD’ means being able to plug in a CPU while the machine is running and then reconfigure SQL Server to make use of the CPU ONLINE! (i.e. no application downtime required at all)

There are a few restrictions:

⦁ Need a 64-bit system that support hot-add CPU (obviously :-))
⦁ Need Enterprise Edition of SQL Server 2008
⦁ Need Windows Server Datacenter or Enterprise Edition

30. What is MaxDOP term in SQL server?
When SQL Server runs on a computer with more than one processor or CPU, it detects the best degree of parallelism that is the number of processors employed to run a single statement, for each query that has a parallel execution plan. You can use the max degree of parallelism option to limit the number of processors to use for parallel plan execution and to prevent run-away queries from impacting SQL Server performance by using all available CPUs.

31. What is a Batch, Task, Windows Thread, Fiber, Worker Thread  in SQL Server OS architecture?

Batch
An SQL batch is a set of one or more Transact-SQL statements sent from a client to an instance of SQL Server for execution. It represents a unit of work submitted to the Database Engine by users.

Task
A task represents a unit of work that is scheduled by SQL server. A batch can map to one or more tasks. For example, a parallel query will be executed by multiple tasks.

Windows Thread
A windows thread represents an independent execution mechanism.

Fiber
A fiber is lightweight thread that queries fewer resources than a windows thread and can switch context when in user mode. One Windows thread can be mapped to many fibers.

Worker Thread
The worker thread represents a logical thread in SQL Server that is internally mapped (1:1) to either a windows thread or, if lightweight pooling is turned ON, to a fiber. The mapping is maintained until worker thread is deallocated either because of memory pressure, or if it has been idle for long time. The association task to a worker thread is maintained for the life of the task.