Miscellaneous Interview Questions

1: What is ACID Property?

ACID Properties

When a transaction processing system creates a transaction, it will ensure that the transaction will have certain characteristics

ATOMICITY

The atomicity property identifies that the transaction is atomic. An atomic transaction is either fully completed, or is not begun at all. Any updates that a transaction might affect on a system are completed in their entirety. If for any reason an error occurs and the transaction is unable to complete all of its steps, the then system is returned to the state it was in before the transaction was started. An example of an atomic transaction is an account transfer transaction. The money is removed from account A then placed into account B. If the system fails after removing the money from account A, then the transaction processing system will put the money back into account A, thus returning the system to its original state. This is known as a rollback

CONSISTENCY

Data is either committed or roll back, not “in-between” case where something has been updated and something hasn’t and it will never leave your database till transaction finished. If the transaction completes successfully, then all changes to the system will have been properly made, and the system will be in a valid state. If any error occurs in a transaction, then any changes already made will be automatically rolled back. This will return the system to its state before the transaction was started. Since the system was in a consistent state when the transaction was started, it will once again be in a consistent state.

ISOLATION

No transaction sees the intermediate results of the current transaction. We have two transactions both are performing the same function and running at the same time, the isolation will ensure that each transaction separate from other until both are finished.

DURABILITY

Once transaction completed whatever the changes made to the system will be permanent even if the system crashes right after

2: What is Certificate?

A certificate is a digitally signed security object that contains a public (and optionally a private) key for SQL Server.

You can use externally generated certificates or SQL Server can generate certificates.

Certificates can be used to help secure connections, in database mirroring, to sign packages and other objects, or to encrypt data or connections.

3: What is checkpoint?

Checkpoint is an internal process that writes all dirty pages (modified pages) from Buffer Cache to Physical disk, apart from this it also writes the log records from log buffer to physical file. Writing of Dirty pages from buffer cache to data file is also known as Hardening of dirty pages.

It is a dedicated process and runs automatically by SQL Server at specific intervals. SQL Server runs checkpoint process for each Database individually.

Checkpoint helps to reduce the recovery time for SQL Server in the event of unexpected shutdown or system crash\Failure.

In SQL Server 2012 there are four types of Checkpoints:

Automatic: This is the most common checkpoint which runs as a process in the background to make sure SQL Server Database can be recovered in the time limit defined by the Recovery Interval – Server Configuration Option.

Indirect: This is new in SQL Server 2012. This also runs in the background but to meet a user-specified target recovery time for the specific Database where the option has been configured. Once the Target_Recovery_Time for a given database has been selected this will override the Recovery Interval specified for the server and avoid Automatic Checkpoint on such DB.

Manual: This one runs just like any other T-SQL statement, once you issue checkpoint command it will run to its completion. Manual Checkpoint runs for your current Database Only. You can also specify the Checkpoint_Duration which is optional, this duration specifies the time in which you want your checkpoint to complete.

Internal: As a user you can’t control Internal Checkpoint. Issued on specific operations such as:

1. Shutdown initiates a Checkpoint operation on all databases except when Shutdown is not clean (Shutdown with nowait)

2. If the recovery model gets changed from Full\Bulk-logged to Simple.

3. While taking Backup of the Database.

4. If your DB is in Simple Recovery model, checkpoint process executes automatically either when the log becomes 70% full, or based on Server option-Recovery Interval.

5. Alter Database command to add or remove a data\log file also initiates a checkpoint.

6. Checkpoint also takes place when the recovery model of the DB is Bulk-Logged and a minimally logged operation is performed.

7. DB Snapshot creation.

4: What is DAC in SQL Server?

Dedicated Administrator Connection (DAC)

SQL Server’s DAC is a special diagnostic connection that’s intended for administrators to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests. By default SQL Server listens for DAC on TCP port 1434. However, you can configure this. If you have changed the default connection port, you can find the port number the DAC is listening on in the Error log.

You can use the DAC in two different ways. You can use it via the sqlcmd prompt or you can use it from SQL Server Management Studio (SSMS).

To use DAC from the sqlcmd prompt, you need to start sqlcmd using the administrator switch (-A) as you can see in the following example.

sqlcmd -S MySQL Server -U sa -P <xxx> –A

sqlcmd –A –d master

The first example shows how to open a DAC to the server named MySQLServer. By default, the DAC will open to the default database. The second example shows how you can open a DAC connection to the master database.

To use DAC from SSMS, select Database Engine Query on the toolbar. Then, in the Connect to Database Engine dialog box, type the word ADMIN: followed by the name of the server instance in the Server name prompt. For example, to connect to a server enter ADMIN:MySQLServer. Then, complete the Authentication section by entering the login information for a member of the sysadmin group and then select Connect.

5: What is Fill Factor and what is the best value for it?

 A page is the basic unit of data storage in SQL server. Its size is 8KB(8192 bytes). Data is stored in the leaf-level pages of Index.  The percentage of space to be filled with data in a leaf level page is decided by fill factor. The remaining space left is used for future growth of data in the page. Fill factor is a number from 1 to 100. Its default value is 0, which is same as 100. So when we say fill factor is 70 means, 70% of space is filled with data and remaining 30% is vacant for future use. So higher the fill factor, more data is stored in the page. Fill factor setting is applied when we create/rebuild index.

6: What is ISOLATION Level in SQL Server?

There are different types of isolations available in SQL Server.

• READ COMMITTED
• READ UNCOMMITTED
• REPEATABLE READ
• SERIALIZABLE
• SNAPSHOT

Read Committed

In select query it will take only committed values of table. If any transaction is opened and incomplete on table in others sessions then select query will wait till no transactions are pending on same table.

Read Committed is the default transaction isolation level.

Read Uncommitted

If any table is updated (insert or update or delete) under a transaction and same transaction is not completed that is not committed or roll backed then uncommitted values will display (Dirty Read) in select query of "Read Uncommitted" isolation transaction sessions. There won't be any delay in select query execution because this transaction level does not wait for committed values on table.

Repeatable Read

In select query data of table that is used under transaction of isolation level "Repeatable Read" cannot be modified from any other sessions till transaction is completed.

Serializable

Serializable Isolation is similar to Repeatable Read Isolation but the difference is it prevents Phantom Read. This works based on range lock. If table has index then it locks records based on index range used in WHERE clause (like where ID between 1 and 3). If table doesn't have index then it locks complete table.

Snapshot

Snapshot isolation is similar to Serializable isolation. The difference is Snapshot does not hold lock on table during the transaction so table can be modified in other sessions. Snapshot isolation maintains versioning in Tempdb for old data in case of any data modification occurs in other sessions then existing transaction displays the old data from Tempdb.

7: What is ORPHAN Users and how to fix orphan users issue?

Orphaned Users

When a database user for a corresponding SQL Server Login is undefined or incorrectly defined, SQL Server does not allow the user to log on to that instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server user name is dropped. Most likely, a database user becomes orphaned after a database is restored or attached to a different instance of SQL Server. Orphans occur if the database user is mapped to a SID that is not present in the new server instance.

Use SQL Server Management Studio or any other tool that executes SQL statements to address all orphaned users before you start or upgrade the StarTeam Server configuration.

Addressing Orphaned Users in SQL Server 2005 and above

Use SQL Server Management Studio or any other tool that executes SQL statements to address all orphaned users before you start or upgrade the StarTeam Server configuration. Use either the system administrator user name (sa) or windows authentication (under an administrative user account) to connect to the database and change the database context for the SQL console to the StarTeam database.

To address all orphaned users:

1. Open a SQL console such as SQL Server Management Studio or Enterprise Manager.

2. Open a new query.

3. Enter the following command to display the user names of all orphaned users:

sp_change_users_login 'REPORT'

4. Enter the following to address the orphaned database owner (dbo). The database user is always orphaned when changing servers.

sp_addlogin <User Name> , <Password> 

go

sp_changedbowner <User Name>

go

sp_defaultdb <UserName> , <Database Name>  (database Name is the StarTeam database)

<UserName> is the appropriate user name, <Password> is the appropriate password for the StarTeam Server and <Database Name>  is the StarTeam database

5. For all other users who are returned as orphans, repeat the following commands for each of the orphaned users.

a) sp_addlogin <UserName>, <Password>

b) go

c) EXEC sp_change_users_login 'Update_One', '<Orphaned UserName>', '<New UserName> where the first< Orphaned UserName> is the orphaned user from the previous server and the second <New UserName> is the new login created in the previous step.

6. Enter the following command and this command should not return any rows

sp_change_users_login 'REPORT'

8: What is Resource Database in SQL Server? How can we take backup for resource database?

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.

Physical Properties of Resource

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.

Backing Up and Restoring the Resource Database

SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.

9: What is Slipstreaming?

It is a term used to describe merging original source media with updates in memory and then installing the updated files.

Slipstreaming has been supported by Windows Operating systems for awhile but has just been added to SQL Server 2008 service pack 1.

Slipstream allows you to go to the latest and greatest, currently service pack 1 and a CU for service pack 1.

Prior to service pack, we supported just update the setup file also referred to as “Patchable Setup”.

Since the release of SQL Server 2008 Service Pack 1, it is recommend to use the slipstream procedures instead as patchable setup since the entire product can be updated and you will be using the latest and greatest.

Any scenario (install, upgrade, addnode) that is supported by the original media is supported when slipstream.

10: What is Patchable Setup?

Patchable Setup is a procedure used for updating just the SQL Server 2008 setup files prior to service pack 1.

Instruction can be found here. Patchable Setup still requires you to apply the CU after installing the product.

For fixes in CU for RTM, you can use patchable setup, but if an issue is only addressed in service pack 1, you need to use the slipstream procedure.

11: What is suspect state of database and how will you overcome with this?

Suspect state of SQL Server database is a state when you are unable to connect to the database.

In this state you cannot do anything with your database: no opening, no backup and no restore.

Possible cause for this problem can be one of the following:

·         database is corrupted

·         Database files are being "opened" or held by some process (operating system, other program(s)...)

·         not enough disk space for SQL Server

·         insufficient memory (RAM) for SQL Server

·         unexpected SQL Server shutdown caused by power failure

For Overcome with this follow below commands.

EXEC sp_resetstatus [YourDatabase];

ALTER DATABASE [YourDatabase] SET EMERGENCY

DBCC checkdb([YourDatabase])

ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE [YourDatabase] SET MULTI_USER

12: How do you troubleshoot slowness with a specific Stored Procedure or a Query?

• First, get more details like, how much time on an average this query was taking previously (baseline).
• Were there any changes to the stored procedure or query recently.
• How often this query does runs.
• Does the problem only happen on this server or other servers too, meaning can this be reproduced on Test or Dev servers.
• Check if this query is being blocked by other sessions.
• Check if this query is waiting some any resource using wait stats DMV’s.
• Check if statistics are up to date for the tables and indexes used in the stored procedure or the query.
• Check fragmentation of the objects in the stored procedure or the query.
• Collect execution plan of the Stored Procedure and the statements inside the SP or the query. Also collect Read/Write and Time execution statistics of the query.
• Check for any missing indexes based on the execution plan, based on table or clustered index scans.
• Try and suggest to developers if the query needs to be rewritten in a better way like replacing temp variables with tempdb tables or usage of functions in where clause.