Important Questions:- Database Architecture and Database Properties

4.Database Architecture and Database Properties

1) How many types of files are there in a SQL Server database? 3 Types

·         Primary data files

·         Secondary data files

·         Transaction Log files

2) Explain each type of database files?

Primary data files

The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension is .mdf.

Secondary data files

Secondary data files make up all the data files, other than the primary data file. Some databases may not have any secondary data files, while others have several secondary data files. The recommended file name extension is .ndf.

Transaction Log file

This file holds all the log information that is used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf.

3) What is the major difference between Primary data files and secondary data files?

Primary data file contains system objects where as secondary data files contains all user defined Database objects if these are not part of the Primary File group.

One of the important difference between Primary and Secondary data files is BOOT PAGE. Page Number 9 is the boot page Page type 13). Boot page is available as 9th page only in the primary data file.

4) How many maximum files can be added to a database?       32,767

5) What are file groups and Type of File Groups?

Database File groups: Database objects and files can be grouped together in file groups for allocation and administration purposes. There are two types of file groups:

Primary: The primary file group contains the primary data file and any other files not specifically assigned to another file group. All pages for the system tables are allocated in the primary file group.

User-defined: User-defined file groups are any file groups that are specified by using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.

6) What is the default File Group in a database? Primary File group

7) Is it possible to change the Default file group from Primary to some other user defined file group? What is the benefit of this?

Yes it is possible to change the Default File group to user defined File group. All the newly created objects will be created in User defined File group be default.

8) How many Maximum File groups can be added in a database?      32,767

9) Is it possible to add Transaction Log file in a file group?

Log files are never part of a filegroup. Log space is managed separately from data space.

10) What is the use of having multiple File Groups?

Below are the major benefits which can be achieved using multiple data files and placing these files in separate file groups on separate disk drives.

1.       Disk I\O Performance

2.       Easy Management and Archiving of the data

3.       Benefit of doing File Group level Backups and restores

4.       Usage of File Groups in Portioning of the tables

11) Is there any benefit to add multiple log files? Yes/No, Why?

No, there is no benefit of adding multiple log files in a database as the write operations in a Transaction log files are always serial.

12) Will the below script work if yes, how?

Create database Test;

Yes, this script will work because rest of the parameters will be taken from model database and Files will be located to the respective folders which are set at the SQL Server instance level.

13) What is Database Growth and what settings are available in a database?

Each database file that is associated with your database has an auto-growth setting. There are three different settings you can use to identify how your database files will grow. They can grow by a specific size, a percentage of the current size, or not grow at all. Additionally you can set your files to unrestricted growth, which means they will keep growing as they need more space or you run out of disk space. Or you can restrict the growth of a database file to grow no larger than a specified size. Each one of these different auto-grow setting have defaults, or you can set them for each database file.

 14) What are the recommended settings for transaction Log File for file growth?

If you are required to set the setting for Auto growth of Transaction log file, it should always be in a specific size instead of percentage.

15) What is a compatibility level of a database?

Compatibility level sets certain database behaviours to be compatible with the specified version of SQL Server. The default compatibility level is 110. Databases created in SQL Server 2012 are set to this level unless the model database has a lower compatibility level.

16) How to change the Compatibility level of a database?

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 90 | 100 | 110 | 120|130 }

90 = SQL Server 2005

100 = SQL Server 2008 and SQL Server 2008 R2

110 = SQL Server 2012

17) What’s the difference between database version and database compatibility level?

Database version

The database version is a number stamped in the boot page of a database that indicates the SQL Server version of the most recent SQL Server instance the database was attached to.

SELECT DatabaseProperty ('dbccpagetest', 'version');

Database compatibility level

The database compatibility level determines how certain database behaviors work. For instance, in 90 compatibility, you need to use the OUTER JOIN syntax to do an outer join, whereas in earlier compatibility levels, you can use ‘*=’ and ‘=*’

SELECT name AS 'DB Name', compatibility_level AS 'Compatibility Level' FROM master.sys.databases;

18) What is a Page Verify option in a database?

When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This helps provide a high level of data-file integrity.

19) What are the different Database states in SQL server instance?

No	State	Definition
1	ONLINE	Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed.
2	OFFLINE	Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.
3	RESTORING	One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable.
4	RECOVERING	Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.
5	RECOVERY PENDING	SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.
6	SUSPECT	At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem.
7	EMERGENCY	User has changed the database and set the status to EMERGENCY. The database is in single-user mode and may be repaired or restored. The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can be set to the EMERGENCY state. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

 20) How many databases can be created in SQL server instance?      32,767

21) What is auto close option?

AUTO CLOSE option:

When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

 When set to OFF, the database remains open after the last user exits.

22) What is auto shrink option?

AUTO SHRINK option:

When set to ON, the database files are candidates for periodic shrinking. Both data file and log files can be shrunk automatically by SQL Server. AUTO_SHRINK reduces the size of the transaction log only if the database is set to SIMPLE recovery model or if the log is backed up.

When set to OFF, database files are not automatically shrunk during periodic checks for unused space.

23) What is page?

Page is the smallest unit of storage in SQL Server database, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page

24) What is an extent?  a collection of eight physically contiguous pages.

25) Types of extent?

Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.

Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

26) What is the difference between single user and restricted user and restricted user option?

This option controls who and how many users can connect to a database.

When SINGLE_USER is specified, one user at a time is allowed to connect to the database. All other user connections are broken.

When RESTRICTED_USER is specified, only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles are allowed to connect to the database, but it does not limit their number.

When MULTI_USER is specified, all users that have the appropriate permissions to connect to the database are allowed.

27) What is a logical File and physical File name?

Each data and transaction log file in a SQL Server database has two names:

logical_file_name

The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements. The logical file name must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.

os_file_name

The os_file_name is the name of the physical file including the directory path. It must follow the rules for the operating system file names.

28) What is ROW_OVERFLOW_DATA? How does it work?

ROW_OVERFLOW_DATA : Assume that a table is created with record size 12000 bytes having 4 varchar data types of size 4000 bytes. Whenever user inserts a record with size greater than 8000 (page size is 8K), then the excess data is moved to ROW_OVERFLOW_DATA pages. In simple terms, ROW_OVERFLOW_DATA pages will come in to picture only when the row size exceed page maximum limit.

29) How can we check the allocation unit of objects?

DMV sys.system_internals_allocation_units

30) What is trustworthy property of a database?

Trustworthy property :When ON, database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database.

When OFF is specified, in an impersonation context cannot access resources outside the database.

TRUSTWORTHY is set to OFF whenever the database is attached.

31) What is Instant file initialization?

Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. Data and log files are first initialized by filling the files with zeros when you perform one of the following operations:

·         Create a database.

·         Add files, log or data, to an existing database.

·         Increase the size of an existing file (including autogrow operations).

·         Restore a database or file group.

·         File initialization causes these operations to take longer. However, when data is written to the files for the first time, the operating system does not have to fill the files with zeros.

Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.

Important Question:- SQL-SERVER ARCHITECTURE

3.SQL-SERVER ARCHITECTURE

Q1. Tell me something about the SQL Server Architecture?

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

Relational Engine components: Cmd Parser, Optimizer, Query Executor

Storage Engine components: Access Methods code, Buffer Manager, Transaction Manager

Q2. 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.

Q3. 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.

Q4.  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

Q5. 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.

Q6. 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.

Q7.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.

Q8. 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.

Q9. 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.

Q10. 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.

Q11. 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.

Q12. 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.

Q13. 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.

Q14. 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

Q15. What is a Transaction manager and its role?

Transaction Manager interacts with the Access Methods and has two components thorugh 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

Q16. 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.

Q17. 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.

Q18. Which DMV can be used to check how many dirty pages exists in the memory for 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

Q19. 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

Q20. 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.

Q21. 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?

Checkpoint	Lazy writer
Checkpoint is used by sql engine to keep database recovery time in check	Lazy writer is used by SQL engine only to make sure there is enough memory left in sql buffer pool to accommodate new pages
Check point always mark entry in T-log before it executes either sql engine or manually	Lazy writer doesn’t mark any entry in T-log
To check occurrence of checkpoint , we can use below queryselect * from ::fn_dblog(null,null) WHERE [Operation] like ‘%CKPT’	To check occurrence of lazy writer we can use performance monitor
	SQL Server Buffer Manager Lazy writes/sec
Checkpoint only check if page is dirty or not	Lazy writer clears any page from memory when it satisfies all of 3 conditions. 1.Memory is required by any object and available memory is full 2.Cost factor of page is zero 3.Page is not currently reference by any connection
Checkpoint is affected by two parameters 1.Checkpoint duration: is how long the checkpoint can run for. 2.Recovery interval: affects how often it runs.	Lazy writer is affected by 1.Memory pressure 2.Reference counter of page in memory
Check point should not be very low , it can cause increasing recovery time of database	No. of times lazy writer is executing per second should always be low else it will show memory pressure
Checkpoint will run as per defined frequency	No memory pressure, no lazy writer
Checkpoint tries to write as many pages as fast as possible	Lazy writer tries to write as few as necessary
checkpoint process does not put the buffer page back on the free list	Lazy writer scans the buffer cache and reclaim unused pages and put it n free list
We can find last run entry of checkpoint in Boot page	Lazy writer doesn’t update boot page
Checkpoint can be executed by user manually or by SQL engine	Lazy writer cant be controlled by user
It keeps no. of dirty pages in memory to minimum	It helps to reduce paging
Auto frequency can be controlled using recovery interval in sp_configure	Works only @ memory pressure , It uses clock algorithm for cleaning buffer cache
It will be automatically executed before every sql statement which requires consistent view of database to perform task like (Alter, backup, checkdb, snapshot …..)	It kicks pages out of memory when reference counter of page reaches to zero
Command : Checkpoint	No command available
It comes in picture to find min lsn whenever t-log truncates	No entry in T-log
Checkpoint is affected by Database recovery model	Lazy writer doesn’t get impacted with recovery model of database
To get checkpoint entry in error log DBCC TRACEON(3502, -1)	Not Applied
Members of the SYSADMIN, DB_OWNER and DB_BACKUPOPERATOR can execute checkpoint manually	Not Applied

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 ashared 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?

‘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.

Q. 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.

Important Questions-INSTALLATION

INSTALLATION

Q1. 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.

C:\programfiles\Microsoft SQL Server\110\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\

Q2. 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.

Q3. What is the location of ConfigurationFile.ini file?

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log folder.

Q4. 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.

Q5 . 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.

Q6. 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.

Q7. 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.

A connection request must specify both the computer name and instance name in order to connect to the instance.

Q8. 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

Q9. What is an RTM setup of SQL Server?

RTM stands for release to manufacturing.

Q10. 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

Q11. 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.

Q12. 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.

Q13. 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.

Q14. What is the default port of a SQL Server instance? 1433

Q15. 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. Go to SQL Server Configuration Manager > SQL Server Network Configuration >Protocols for <Instance Name>

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

Step 3. 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.

Q15. 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 (TCP/IP Properties)

Method 2: Windows Event Viewer-Event ID 26022

Method 3: SQL Server Error Logs 

(EXEC xp_readerrorlog 0, 1, N'Server is listening on', 'any', NULL, NULL, N'asc')

 Method 4: sys.dm_exec_connections DMV

(select distinct local_net_address, local_tcp_port from sys.dm_exec_connections)

Method 5: Reading registry using xp_instance_regread

Q16. 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.

Q17. What’s the location of SQL Server log files?

Instance Root Directory\MSSQL\Log

Q18. How many SQL Server log files can be retained in the SQL Server error logs be 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 Errorlog 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.

Q19. Is it possible to increase the retention of Error log files and How?

Yes , Open SQL Server Management Studio and then connect to SQL Server Instance. In Object Explorer, Expand Management 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.