1.

What are the types of indexes available with SQL Server?

 

There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered. 
 

2.

When do we use the UPDATE_STATISTICS command?

This command is basically used when we do a large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly. 
 

3.

Which TCP/IP port does SQL Server run on?

SQL Server runs on port 1433 but we can also change it for better security. 
 

4.

From where can you change the default port?

From the Network Utility TCP/IP properties Ė> Port number.both on client and the server. 
 

5.

What is the use of DBCC commands?

DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks. 
 

6.

Can you give me some DBCC command options?

(Database consistency check) - DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.and DBCC CHECKALLOC - To check that all pages in a db are correctly allocated. DBCC SQLPERF - It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables file group for any damage. 
 

7.

What command do we use to rename a db?

sp_renamedb Ďoldnameí , Ďnewnameí 
 

8.

Well sometimes sp_reanmedb may not work you know because if some one is using the db it will not accept this command so what do you think you can do in such cases?

In such cases we can first bring to db to single user using sp_dboptions and then we can rename that db and then we can rerun the sp_dboptions command to remove the single user mode. 
 

9.

What is a Join in SQL Server?

Join actually puts data from two or more tables into a single result set. 
 

10.

Can you explain the types of Joins that we can have with Sql Server?

There are three types of joins: Inner Join, Outer Join, Cross Join 
 

11.

When do you use SQL Profiler?

SQL Profiler utility allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc.. 
 

12.

What is a Linked Server?

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. 
 

13.

Can you link only other SQL Servers or any database servers such as Oracle?

We can link any server provided we have the OLE-DB provider from Microsoft to allow a link. For Oracle we have a OLE-DB provider for oracle that microsoft provides to add it as a linked server to the sql server group. 
 

14.

Which stored procedure will you be running to add a linked server?

sp_addlinkedserver, sp_addlinkedsrvlogin 
 

15.

What are the OS services that the SQL Server installation adds?

MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator
 

16.

Can you explain the role of each service?

SQL SERVER - is for running the databases 
SQL AGENT - is for automation such as Jobs, DB Maintanance, Backups 
DTC - Is for linking and connecting to other SQL Servers
 

17.

How do you troubleshoot SQL Server if its running very slow?

First check the processor and memory usage to see that processor is not above 80% utilization and memory not above 40-45% utilization then check the disk utilization using Performance Monitor, Secondly, use SQL Profiler to check for the users and current SQL activities and jobs running which might be a problem. Third would be to run UPDATE_STATISTICS command to update the indexes 
 

18.

Letís say due to N/W or Security issues client is not able to connect to server or vice versa. How do you troubleshoot?

First I will look to ensure that port settings are proper on server and client Network utility for connections. ODBC is properly configured at client end for connection óóMakepipe & readpipe are utilities to check for connection. Makepipe is run on Server and readpipe on client to check for any connection issues. 
 

19.

What are the authentication modes in SQL Server?

Windows mode and mixed mode (SQL & Windows). 
 

20.

Where do you think the users names and passwords will be stored in sql server?

They get stored in master db in the sysxlogins table. 
 

21.

What is log shipping? Can we do logshipping with SQL Server 7.0

Logshipping is a new feature of SQL Server 2000. We should have two SQL Server - Enterprise Editions. From Enterprise Manager we can configure the logshipping. In logshipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and we can use this as the DR (disaster recovery) plan. 
 

22.

Let us say the SQL Server crashed and you are rebuilding the databases including the master database what procedure to you follow?

For restoring the master db we have to stop the SQL Server first and then from command line we can type SQLSERVER Ėm which will basically bring it into the maintenance mode after which we can restore the master db. 
 

23.

What is BCP? When do we use it?

BulkCopy is a tool used to copy huge amount of data from tables and views. But it wonít copy the structures of the same. 
 

24.

What should we do to copy the tables, schema and views from one SQL Server to another?

We have to write some DTS packages for it.
 

Create a Full Database Backup (SQL Server)

 

SQL Server Management Studio

Transact-SQL

PowerShell

 

Execute the BACKUP DATABASE statement to create the full database backup, specifying:

         The name of the database to back up.

         The backup device where the full database backup is written.

 

The basic Transact-SQL syntax for a full database backup is:

BACKUP DATABASE database

TO backup_device [ ,...n ]

[ WITH with_options [ ,...o ] ] ;

 

A. Backing up to a disk device

The following example backs up the complete AdventureWorks2012 database to disk, by using FORMAT to create a new media set.

 

Transact-SQL

SE AdventureWorks2012;

GO

BACKUP DATABASE AdventureWorks2012

TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'

†† WITH FORMAT,

      MEDIANAME = 'Z_SQLServerBackups',

      NAME = 'Full Backup of AdventureWorks2012';

GO

 

Using PowerShell

Use the Backup-SqlDatabase cmdlet. To explicitly indicate that this is a full database backup, specify the -BackupAction parameter with its default value, Database. This parameter is optional for full database backups.

The following example creates a full database backup of the MyDB database to the default backup location of the server instanceComputer\Instance. Optionally, this example specifies -BackupAction Database.

 

--Enter this command at the PowerShell command prompt, C:\PS>
Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Database

 

Create a Differential Database Backup (SQL Server)

 

Limitations and Restrictions

         The BACKUP statement is not allowed in an explicit or implicit transaction.

Prerequisites

         Creating a differential database backup requires that a previous full database backup exist. If the selected database has never been backed up, run a full database backup before creating any differential backups. For more information, see Create a Full Database Backup (SQL Server).

Recommendations

         As the differential backups increase in size, restoring a differential backup can significantly increase the time that is required to restore a database. Therefore, we recommend that you take a new full backup at set intervals to establish a new differential base for the data. For example, you might take a weekly full backup of the whole database (that is, a full database backup) followed by a regular series of differential database backups during the week.

Security

Permissions

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions. However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.

 

Using Transact-SQL

 

To create a differential database backup

         Execute the BACKUP DATABASE statement to create the differential database backup, specifying:

o    The name of the database to back up.

o    The backup device where the full database backup is written.

o    The DIFFERENTIAL clause, to specify that only the parts of the database that have changed after the last full database backup was created are backed up.

The required syntax is:

BACKUP DATABASE database_name TO <backup_device> WITH DIFFERENTIAL

 

Example (Transact-SQL)

This example creates a full and a differential database backup for the MyAdvWorks database.

 

-- Create a full database backup first.
BACKUP DATABASE MyAdvWorks 
†††TO MyAdvWorks_1 
†††WITH INIT;
GO
-- Time elapses.
-- Create a differential database backup, appending the backup
-- to the backup device containing the full database backup.
BACKUP DATABASE MyAdvWorks
†† TO MyAdvWorks_1
†† WITH DIFFERENTIAL;
GO

 

Restore a Database Backup under the Simple Recovery Model (Transact-SQL)

 

USE master
GO
RESTORE DATABASE AdventureWorks2012
†† FROM TAPE = '\\.\Tape0'
GO

 

Copy-Only Backups (SQL Server)

 

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.

 

The types of copy-only backups are as follows:

         Copy-only full backups (all recovery models)

A copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base.

Restoring a copy-only full backup is the same as restoring any other full backup.

         Copy-only log backups (full recovery model and bulk-logged recovery model only)

A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create a new routine log backup (using WITH NORECOVERY) and use that backup together with any previous log backups that are required for the restore sequence. However, a copy-only log backup can sometimes be useful for performing an online restore. For an example of this, see Example: Online Restore of a Read/Write File (Full Recovery Model).

The transaction log is never truncated after a copy-only backup.

†††††††††††† Copy-only backups are recorded in the is_copy_only column of the backupset table.

 

You can create a copy-only backup by using SQL Server Management Studio, Transact-SQL, or Power Shell.

 

To Create a Copy-Only Backup

Using SQL Server Management Studio

 

1.      On the General page of the Back Up Database dialog box, select the Copy Only Backup option.

 

Using Transact-SQL

 

The essential Transact-SQL syntax is as follows:

         For a copy-only full backup:

BACKUP DATABASE database_name TO <backup_device> Ö WITH COPY_ONLY Ö

 

For a copy-only log backup:

 

BACKUP LOG database_name TO <backup_device> Ö WITH COPY_ONLY Ö

 

Using PowerShell

 

1.      Use the Backup-SqlDatabase cmdlet with the -CopyOnly parameter.

 

Copy-Only Backup:

A Copy-only backup will not break the restore chain, which means the differential database backups would still have a reference to the last full database backup taken, it will not consider the intermediate full database backup taken with copy-only option.
 
Copy-only backup can be taken, when you want to get a copy of the database for testing purposes

In SSMS 2005, Copy-only backups are not supported via SQL Server Management Studio (SSMS) and must be performed via the Transact-SQL (T-SQL) BACKUP command.

∑          

In SSMS 2008, it supports GUI or T-SQL statement to create Copy Only backups.

 

Recovery Models (SQL Server)

 

SQL Server backup and restore operations occur within the context of the recovery model of uses the full recovery model or simple recovery model.

A database can be switched to another recovery model at any time.

Recovery Model Overview

The following table summarizes the three recovery models.

Using SQL Server Management Studio

To view or change the recovery model

1.      After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

2.      Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

3.      Right-click the database, and then click Properties, which opens the Database Properties dialog box.

4.      In the Select a page pane, click Options.

5.      The current recovery model is displayed in the Recovery model list box.

6.      Optionally, to change the recovery model select a different model list. The choices are Full, Bulk-logged, or Simple.

7.      Click OK.

 

Using Transact-SQL

To view the recovery model

1.      Connect to the Database Engine.

2.      From the Standard bar, click New Query.

3.      Copy and paste the following example into the query window and click Execute. This example shows how to query the sys.databases catalog view to learn the recovery model of the model database.

 

Transact-SQL

SELECT name, recovery_model_desc
   FROM sys.databases
      WHERE name = 'model' ;
GO
 

To change the recovery model

1.      Connect to the Database Engine.

2.      From the Standard bar, click New Query.

3.      Copy and paste the following example into the query window and click Execute. This example shows how to change the recovery model in themodel database to FULL by using the SET RECOVERY option of the ALTER DATABASE statement.

 
Transact-SQL
USE master ;
ALTER DATABASE model SET RECOVERY FULL ;

 

SQL server has three types of recovery models. 

1. Full

2. Bulk Logged

3. Simple

Full

         In full recovery model all the database operations are fully logged like all redo information. This means all the modifications have written fully to the log file.

         The full recovery model is always the best one for production servers.

         In the full recovery model we can take all kinds of backups like full, differential, transaction Log and point in time recovery (PTR) too.

Bulk Logged

         The name itself you can understand the answer. The bulk operations (bulk operations) are minimally logged in this mode. This means it does not have sufficient information in the log to replay the transaction. The BCM page contains all the required information. See an example you can understand.

         Bulk logged recovery model is the best model for server performance. Because all the bulk changes have been written minimally (Not fully) to the transaction log file.

         In bulk logged recovery model we can take all kinds of backups like full, differential and transaction Log but, the drawback is the point in time recovery (PTR) is not possible, when there is a bulk operations  have done with the transaction log file.

Keep in mind, in full and bulk logged recovery model the log files grow bigger until the BACKUP LOG has done.

For more clarity. See an example: (This is not a concept just an example)

Just assume 5,000 bulk operations have written in 8000 pages.

In full recovery model each 5,000 bulk operations have written in 8000 pages, because all the changes have written fully in the full recovery model.

But when we used bulk logged recovery model, it has written 1000 pages only ,Because SQL server has written the bulk changes to an extent wise not the page wise i.e. minimally logged operation (8 pages = 1 extent). It will help to improve the overall server performance.

Now you know, Writing 8000 pages are good or 1000 pages.

Note: The bulk logged model is good only for the bulk operations.

 

 

Simple

         The name itself you can understand the answer all operations are fully logged except bulk operation, Since bulk are minimally logged. Simple recovery model is just simple this means SQL server will run the checkpoint every time and truncate the transaction log file and marked the space for reuse . Mostly the log file will not grow larger.

         Most of the time the simple recovery model is a good choice for non production servers. Because the log file will not grow larger. Also we would not take log backups. (If youíre planning to take the log backup then donít put in simple)

         In the simple recovery model the transaction log backup is not possible. We can take full and differential backups only.

Now you could know the answer for the following questions.

What is the use of Full, Bulk logged and Simple recovery model.

Which one is best for your case?

 

Backup Types

SQL server has number of backup types. Here I am going to explain the main three types.

1. Full

2. Differential

3. Transaction log

 Full backup

         A full backup backs up the full/whole database. That backs up all the data.

         The full database backup has been done in all the recovery models.

Differential backup

         A differential backup only backs up the changed data/extents that were modified after the full database backup has done. The DCM has tracked all the changed extents.

         The differential backup has been done in all the recovery models.

Transaction log backup

         A transaction log backup backs up the transaction log file. That is all the modifications/changes.

         The transaction log backup only possible in full and bulk logged recovery model.

         The transaction log backup is important to minimize data loss and log file size too.

Itís  very important to keep the transaction log backup as much as safe, because the restoration needs the sequence of the transaction log backup if, you deleted/missing any one of the backups then you canít recover the whole data.

Itís good to take a log backup often. It will reduce the log file size and helps to minimize your data loss.

 

What are the difference between clustered and a non-clustered index?

  1. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
  2. A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What is difference between DELETE and TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

1.   TRUNCATE:

1.   TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

2.   TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

3.   TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.

4.   You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

5.   TRUNCATE cannot be rolled back.

6.   TRUNCATE is DDL Command.

7.   TRUNCATE Resets identity of the table

2.    DELETE:

1.    DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.

2.    If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

3.    DELETE Can be used with or without a WHERE clause

4.    DELETE Activates Triggers.

5.    DELETE can be rolled back.

6.    DELETE is DML Command.

7.    DELETE does not reset identity of the table.

Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.

 

When is the use of UPDATE_STATISTICS command?

This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

9. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

10. What are the properties and different Types of Sub-Queries?

  1. Properties of Sub-Query
    1. A sub-query must be enclosed in the parenthesis.
    2. A sub-query must be put in the right hand of the comparison operator, and
    3. A sub-query cannot contain an ORDER-BY clause.
    4. A query can contain more than one sub-query.
  2. Types of Sub-Query
    1. Single-row sub-query, where the sub-query returns only one row.
    2. Multiple-row sub-query, where the sub-query returns multiple rows,. and
    3. Multiple column sub-query, where the sub-query returns multiple columns

11. What is SQL Profiler?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

12. What are the authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode - SQL and Windows. To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

13. Which command using Query Analyzer will give you the version of SQL server and operating system?

SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').

14. What is SQL Server Agent?

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full- function scheduling engine, which allows you to schedule your own jobs and scripts.

 

16. What is Log Shipping?

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

17. Name 3 ways to get an accurate count of the number of records in a table?

SELECT * FROM table1 
SELECT COUNT(*) FROM table1 
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

18. What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

 

19. What is the difference between a Local and a Global temporary table?

  1. A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
  2. A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

 

21. What is PRIMARY KEY?

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

22. What is UNIQUE KEY constraint?

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

23. What is FOREIGN KEY?

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

24. What is CHECK Constraint?

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

 

27. What is a Scheduled Jobs or What is a Scheduled Tasks?

Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.

28. What are the advantages of using Stored Procedures?

  1. Stored procedure can reduced network traffic and latency, boosting application performance.
  2. Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
  3. Stored procedures help promote code reuse.
  4. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
  5. Stored procedures provide better security to your data.

 

29. What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?

Unindexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and then do bulk of inserts and to restore those indexes after that.

30. Can SQL Servers linked to other servers like Oracle?

SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

 

31. What is BCP? When does it used?

BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

 

32. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

33. What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad- hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

When I face the interview for the post of Jr. SQL server DBA, I was asked the following questions.

Q.1. What are system databases into SQL server (2005/2008)

Ans. TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource,

Q.2. What stored by the TEMPDB ?

Ans. Row versions, cursor, temp objects.

Q.3. What Stored by the MODEL?

Ans. Templates of new database objects, like tables and column.

Q.4. What Stored by the MASTER?

Ans. Serverís configurations and logins.

Q.5. What Stored by the MSDB?

Ans. Scheduled jobs Backup/Restore and DTA information.

Q.6. Can we Perform Backup Restore operation on TEMPDB?

Ans. NO

Q.7. What is stored in the mssqlsystemresource database?

Ans. Definition of sys objects, which logically shows into all database and DMVs.

Q.8. Where the SQL Logs gets stored?

Ans. Itís stored into root folder SQL server, LOG folder.

Q.9. What is a Linked Server?

Ans. Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server databases using T-SQL Statements.

Q.10. How to find the Service pack installed?

Ans. Ans. Select @@version Or select serverpropertyproductlevelí)

Q.11. What are the Deference between Primary Key and Unique Key?

Ans. An unique key cant not be referenced as foreign key. And it may allow on null.

Q.12. What is mean by Clustered Index and Non clustered index, give syntax of creation?

Ans. create clustered index index_name on empmst(card)

Q.13. What is Scan Table/View and Seek Table/View When its Occurs?
Ans. A Table/view SCAN occurs when no useful indexes exist. A TABLE SCAN reads all data, row by row, to find the match.


Q.14. What is SQL Profiler. What are the default templates with it?

Ans. SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services.

You can capture and save data about each event to a file or table to analyze later.

Q.15. What are the DMVs?

Ans. Dynamic Management Views (DMV) return server state information that can be used to monitor the health of a server instance,

diagnose problems, and tune performance.

Q.16. What is the syntax to execute the sys.dm_db_missing_index_details?

Ans. Select * from sys.dm_db_missing_index_details

What are the difference between DDL, DML and DCL commands?

 

DDL


Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

DML


Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

DCL


Data Control Language (DCL) statements. Some examples:

TCL


Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

 

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

View can be described as virtual table which derived its data from one or more than one table columns. It is stored in the database. It is used to implements the security mechanism in the Sql Server

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.

 

SQL CREATE INDEX Statement

 

The CREATE INDEX statement is used to create indexes in tables.

Indexes allow the database application to find data fast; without reading the whole table.


Indexes

An index can be created in a table to find data more quickly and efficiently.

The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column_name)

SQL CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

Note: The syntax for creating indexes varies amongst different databases. Therefore: Check the syntax for creating indexes in your database.


CREATE INDEX Example

The SQL statement below creates an index named "PIndex" on the "LastName" column in the "Persons" table:

CREATE INDEX PIndex
ON Persons (LastName)

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

To create a clustered index

 

USE AdventureWorks2012;

GO

-- Create a new table with three columns.

CREATE TABLE dbo.TestTable

††† (TestCol1 int NOT NULL,

†††† TestCol2 nchar(10) NULL,

†††† TestCol3 nvarchar(50) NULL);

GO

-- Create a clustered index called IX_TestTable_TestCol1

-- on the dbo.TestTable table using the TestCol1 column.

CREATE CLUSTERED INDEX IX_TestTable_TestCol1

††† ON dbo.TestTable (TestCol1);

GO

To create nonclustered index

USE AdventureWorks2012;

GO

-- Find an existing index named IX_ProductVendor_VendorID and delete it if found.

IF EXISTS (SELECT name FROM sys.indexes

††††††††††† WHERE name = N'IX_ProductVendor_VendorID')

††† DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;

GO

-- Create a nonclustered index called IX_ProductVendor_VendorID

-- on the Purchasing.ProductVendor table using the BusinessEntityID column.

CREATE NONCLUSTERED INDEX IX_ProductVendor_VendorID

††† ON Purchasing.ProductVendor (BusinessEntityID);

GO

 

/* Create Nonclustered Index over Table */


CREATE NONCLUSTERED INDEX [IX_MyTable_NonClustered]
ON [dbo].[MyTable]
(
[First] ASC,
[Second] ASC
) ON [PRIMARY]
GO

 

 

 

 

 

 

 

SQL Questions-2

 

Q1:†††††††† What are different types of system database and what is their use?

Answer:

Master: This database is first database to come online and keep configuration level setting,
information about all databases and logins etc. If Master is down, whole SQL server instance is down.

 

Model: Model database is used as a template by SQL serverwhenever a new DB is created. If any object is required in every new database then it can be achieved by adding that object in Model Database.

 

MSDB: This database keep information about SQL server agent tasks like job, maintenance plans, they execution history , backup-restore history etc.

 

TempDB: This database is use for all temporary works like sorting and all temporary objects gets created in this database. TempDB get created every time SQL server is recycled.

 

Q2:†††††††† What are different types of recovery model and how they impact recovery of a database?

Full: In this recovery model, every transaction gets logged maximally hence transaction log file
grows fast and transaction log backup policy is expected to avoid log full situation. This recovery model allows point in time recovery.

 

Bulk-logged: Only bulk logged transactions get logged minimally. Rest all transaction are logged like Full. Point in time is possible only if there is no bulk operation executed. This provides high performance to bulk operation.

 

Simple: This transaction logs transaction minimally hence log growth is not expected very much. SQL server records only that state of transaction where recovery is possible. Transaction log backup is not available.

 

Q3:†††††††† Is point-in-time recovery possible in bulk recovery model? In which scenario itís not possible? How to perform point-in-time recovery.

††††††††††††††† Answer: Yes itís possible only if there is no bulk operation has taken place. It can be performed by using STOP AT parameter in restore log operation.

 

Q4:†††††††† What is difference between full logging and minimal logging?

Minimal Logging: Minimal logging involves logging only the information that is required to recover the transaction.

 

Maximum Logging: Maximum logging involves all that information (every command in transaction) which is required to perform point in time recovery.

 

Q5:†††††††† What are different types of SQL server backup and difference?

††††††††††††††† Full: Full backup capture complete state of the database. Full data is captured in full backup.

Differential: It captures all changes since last full backup. These changes are tracked on the basis
of extent bit which get changed if data is changed in data pages in that extent.

Transaction Log: This back captures all transaction performed since last transaction log\database backup.

Q6:†††††††† How SQL server identifies that what is required to be captured in differential backup?

SQL server identify this on the basis of extent bit value. Whenever extent bit is changed SQL server mark this extent to include in differential backup.

 

Q7:†††††††† What is copy only backup?

Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database.

 

Q8:†††††††† Is copy only can be used with differential back?

††††††††††††††† Yes, it available for full, differential and transaction log backup.

 

Q9:†††††††† Can we restore differential backup on a full backup which was taken with copy only option?

††††††††††††††† I think , itís not possible. Need to check on this.

 

Q10:†††††† What are difference phases of a restore operation?

††††††††††††††† Copy: Database files get copied from backup to drive location.

††††††††††††††† Redo: All completed transactions in backup set get rolled forward.

††††††††††††††† Undo: All completed transactions in backup set get rolled back.

 

Q11:†††††† What is the difference between RECOVERY and NORECOVERY restore parameters?

Recovery option bring database in fully usable mode while norecovery option leave database in restoring mode to apply sub sequent backups.

 

Q12:†††††† How to identify how much space would be required to restore a backup set?

††††††††††††††† Restore FILELISTONLY from disk =íBackup_locationí

 

Q13:†††††† Suppose your server has caught fire or crashed, no this server is not available. How will you identify that which version and code level was installed? This is required to be identified to install same code level in new server because your application may not support latest SP or specific code level.

 

††††††††††††††† We need to run Restore HEADERONLY from disk =íMaster backup locationí. This backup should be available on the tape as per DBA best practices other this information canít be recovered.

 

Q14:†††††† Suppose your master database has been corrupted, how will you recover your instance?

††††††††††††††† First you need to rebuild database engine. This will create new copy on system databases. Apply service packs\hotfixes while were applied to corrupted instance. Restart SQL server in single user mode and restore master database backup. Restoring master database is possible only with same code level. And restore MSDB & Model databases.

 

Q15:†††††† Suppose your user database is in suspect mode, how to resolve this?

1.       Files not accessible

2.       Corruption

†††††††††††† Sp_resetstatus - sp_resetstatus <db name>

†††††††††††††† update sysdatabases set status = 32768

††††††††††††††† Then set the db to single user

††††††††††††† exec sp_dboption 'yourdb', 'single user', 'true'

††††††††††††† Then run dbcc checkdb ('yourdb', REPAIR_REBUILD)

†††††††††††† Set single user to false.

 

††††††

Q15:†††††† How to identify required estimated space in TEMPDB to execute DBCC CHECKDB for a particular database?

††††††††††††††† DBCC CHECKDB with ESTIMATEONLY

 

Q16:†††††† How to skip logical consistency check in DBCC CHECKDB.

††††††††††††††† DBCC CHECKDB with Physical_only

 

Q17:†††††† What are 3 DBCC commands which are covered by DBCC CHECKDB?

 

††††††††††††††† DBCC CHECKALLOC - Checks the consistency of disk space allocation structures for a specified†† database.

 

DBCC CHECKTABLE - Checks the integrity of all the pages and structures that make up the table or indexed view.

 

††††††††††††††† DBCC CHECKCATALOG - Checks for catalog consistency within the specified database. The database must be online. - DBCC CHECKCATALOG checks that every data type in syscolumns has a matching entry in systypes and that every table and view in sysobjects has at least one column in syscolumns.

 

 

 

Q18:†††††† How to rebuild MSDB database?

††††††††††††††† There is a script which can be found on <Insall_media_Location>\MSSQL\Install\ Instmsdb.sql

 

Q19:†††††† What is the startup order of system databases?

††††††††††††††† 1-Master

††††††††††††††† 2- MSSQLSYSTEMRESOURCE

††††††††††††††† 3- MODEL

††††††††††††††† 4- MSDB

††††††††††††††† 5- TEMPDB (This can be found by looking at create date of TempDB)

††††††††††††††† 6- User databases

 

Q20:†††††† If resource database is missing, will SQL server start? If yes, what would be the over all impact?

††††††††††††††† SQL Server can not start is MSSQLSYSTEMRESOURCE database is missing.

†††††††††††† - The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.

 

Q21:†††††† Can you backup TEMPDB database?

††††††††††††††† Backup and restore operations are not allowed on database tempdb.

 

Q22:†††††† How to backup resource database?

††††††††††††††† You can simply copy mdf and ldf file of MSSQLSYSTEMREQOURCE database.

 

Q23:†††††† What is tail log backup and why itís required?

††††††††††††††† Tail log backup is log backup which is performed with No_Truncate or NORECOVERY.

††††††††††††††† NO_TRUNCATE is used when database is down but log file is healty. This allow use to recover all completed transaction.

††††††††††††††† NORECOVERY is used to bring a database in restoring mode.

 

Q24:†††††† Suppose, you have a 1 TB database and one user has truncated a table? How to restore data of this table with minimal impact on the application?

††††††††††††††† You can use SQL internal tables INSERTED and DELETED to recover this data. Whenever a record is deleted, same record is entered in DELETED table.

††††††††††††††† Whenever a record is updated then old record is entered in DELETD table and new record in INSERTED table.

††††††††††††††† Whenever new record is added to table, itís added in INSERTED table as well. INSERTED and DELETED tables are temporary, SQL server managed, memory resident tables.

 

Q25:†††††† What is role of SQL server browser service? Is it good to disable this service?

††††††††††††††† SQL server browser service divert incoming request to correct SQL Server instance. Suppose, you SQL server is running on non default port (other than 1433) and if SQL browser is disabled then you will not be able to connect with SQL server if port number is not mentioned with SQL server instance name. But port number is not required if SQL browser is running.

 

Q26:†††††† Suppose SQL server is running in mixed mode. None other than SA has sys admin permissions. Somehow, we lost password of SA? How to get this on track?

††††††††††††††† Windows Administrators have access to SQL server by default if SQL server is in single user mode. To get sysadmin access back, you need to start SQL server is single user mode and execute below command:

†††††††††††††††

EXEC sp_addsrvrolemember 'Login_Name', 'sysadmin';

GO

 

Once this account is added to SQL server with SA permissins, you need to start SQL server in normal mode and connect to SQL server using this account. Then you can reset password of SA account and unlock SA account.

 

Q27:†††††† If you have been assigned db_owner role in a particular database and select permissions are denied on a specific table in this database. Can you fire select command on this table?

 

††††††††††††††† You canít access that table.

 

Q28:†††††† You donít have select permission on a table which is being used by a stored procedure where you have execute permission? Will you be able to execute this procedure?

††††††††††††††† Stored procedure will throw the error if the owner of stored procedure is different from table

 

Q29:†††††† What is difference between user and login?

††††††††††††††† User is associated with database

††††††††††††††† Login is associated with instance

Q30:†††††† How to transfer logins to different server?

††††††††††††††† Sp_helprevlogin

Sp_hexadecimal

 

 

Q31:†††††† What is difference between server roles : security admin, sysadmin, dbcreator, setupadmin and ,diskadmin process admin

database roles dbowner,datareader, writer, deny datareader datawriter?

 

 

Q32:†††††† What are different types of indexes available in SQL server? Explain?

Indexes are created on columns in tables or views. The index provides a fast way to look up data based on the values within those columns. You can create indexes on most columns in a table or a view. The exceptions are primarily those columns configured with large object (LOB) data types, such as image, text, and varchar(max).

 

Clustered Indexes

A clustered index stores the actual data rows at the leaf level of the index

Nonclustered Indexes

Unlike a clustered indexed, the leaf nodes of a nonclustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data.

        Composite index: An index that contains more than one column. In both SQL Server 2005 and 2008, you can include up to 16 columns in an index, as long as the index doesnít exceed the 900-byte limit. Both clustered and nonclustered indexes can be composite indexes.

        Unique Index: An index that ensures the uniqueness of each value in the indexed column. If the index is a composite, the uniqueness is enforced across the columns as a whole, not on the individual columns. For example, if you were to create an index on the FirstName and LastName columns in a table, the names together must be unique, but the individual names can be duplicated.

 

Q32:†††††† What is fill factor? What should be the value of fill factor in a read only database and on database where high rate of data insert is expacted.

 

††††††††††††† 1 page Ė 8kb data stored, 1 extent Ė 8 pages , 1 page , how much free space should be allowed .If fill factor is 80 , 20% free space on a page is allowed while creating indexesBy setting the fill-factor value, you specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth.

 

High rate Ė fill factor Ė 30-70

 

Q 33 to Q 48 later

 

Q33:†††††† How to identify fragmentation? What are the options to remove fragmentation?

 

†††††††††††††††† Using DMV - sys.dm_db_index_physical_stats

 

††††† SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC

 

 

Reducing fragmentation:

 

Q34:†††††† Which one is online operation by default Rebuild index or Reorganize index? How to rebuild index in TEMPDB database?

 

Reorg Index is an online operation.

 

Q35:†††††† What are bookmark lookup, RID lookup, Index seek? Which one is best for performance point of view?

Book Mark Lookup:

When a small number of rows are requested by a query, the SQL Server optimizer will try to use a non-clustered index on the column or columns contained in the WHERE clause to retrieve the data requested by the query. If the query requests data from columns not present in the non-clustered index, SQL Server must go back to the data pages to get the data in those columns. Even if the table has a clustered index or not, the query will still have to return to the table or clustered index to retrieve the data.

 

In the above scenario, if table has clustered index, it is called bookmark lookup (or key lookup); if the table does not have clustered index, but a non-clustered index, it is called RID lookup. This operation is very expensive. To optimize any query containing bookmark lookup or RID lookup, it should be removed from the execution plan to improve performance.

 

Bookmark Lookup is not used in SQL Server 2008. Instead, Clustered Index Seek and RID Lookup provide bookmark lookup functionality. The Key Lookup operator also provides this functionality.

 

Index Scan:

Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

 

Index Seek:

Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

 

Index Seek is best among these for better performance.

 

Q36:†††††† Suppose, you are a DBA and gets a call from support center to check latency on database side? What checks you perform at that time? Explain in detail.

 

††††††††††††††† Check resource utilization, blocking in SQL server, errors in SQL log and windows log file. Check what maintenance job is running (Rebuild\full backup etc should not run in peak hours). Check waitypes for waiting processes.

 

Q37:†††††† How to check memory pressure in SQL server?

††††††††††††† sys.dm_os_performance_counters

1.       Look for Free Pages below 640

2.       Look for Lazy Writes/sec above 20

  1. Look for Page Life Expectancy below 30

SELECT * FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy' AND [object_name] LIKE '%BUFFER MANAGER%'

 

Q38:†††††† How to check CPU pressure in SQL server?

sys.dm_os_wait_stats - If the signal waits are above 20%, that is a definite indicator of CPU pressure.

 

Q39:†††††† How to check CPU usages in last 30 minutes?

 

 

 

Q40:†††††† How to check Disk slowness in SQL server?

 

Physical Disk / Logical Disk

Avg. Disk sec/Read

*

( < .005 excellent; .005 - .010 Good; .010 - .015 Fair; > .015 investigate)

Measures read latency on the disks

Physical Disk / Logical Disk

Avg. Disk sec/Write

*

( < .005 excellent; .005 - .010 Good; .010 - .015 Fair; > .015 investigate)

Measures write latency on the disks

Physical Disk / Logical Disk

Avg. Disk sec/Transfer

*

( < .005 excellent; .005 - .010 Good; .010 - .015 Fair; > .015 investigate)

Measures average latency for read or write operations

Physical Disk / Logical Disk

% Idle Time

*

> 50%

The disk should not be working constantly

Physical Disk / Logical Disk

Avg. Disk Queue Length

*

None

Be careful when using old thresholds of 2 with SQL Server and SANs. This value can be much higher than 2. Do not rely solely on this counter to diagnose an IO problem.

 

 

Q41:†††††† What are Dormant, Running, Backuground, Rollback, Pending, Runnable,, and suspended status of SPID?

††††††††††††††† Dormant. SQL Server is resetting the session.

Running. The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see Using Multiple Active Result Sets (MARS).

Background. The session is running a background task, such as deadlock detection.

Rollback. The session has a transaction rollback in process.

Pending. The session is waiting for a worker thread to become available.

Runnable. The session's task is in the runnable queue of a scheduler while waiting to get a time quantum.

Suspended. The session is waiting for an event, such as I/O, to complete.

 

Q42:†††††† What is Recource_semaphore, MSSQL_XP waittypes.

††††††††††††††† Resoure Semaphore is a wait ype which occurs when there is not enough memory to be allocated to SQL server processes to proceed.

††††††††††††††† MSSQL_XP: This wait type comes when SPID is waiting for an extended stored procedure to be complete:

 

Q43:†††††† What is difference between Process and Thread?

††††††††††††††† Process is nothing but a client or system request running in SQL server. Thread is smallest portion of process which can be executed in at a time.

 

Q44:†††††† What is Optimizer?

 

††††††††††††††† The Optimizer is the most prized possession of the SQL Server team and one of the most complex and secretive parts of the product. Fortunately, it's only the low-level algorithms and source code that are so well protected (even within Microsoft), and research and observation can reveal how the Optimizer works.

It is what's known as a "cost-based" optimizer, which means that it evaluates multiple ways to execute a query and then picks the method that it deems will have the lowest cost to execute. This "method" of executing is implemented as a query plan and is the output from the optimizer.

Based on that description, you would be forgiven for thinking that the optimizer's job is to find the best query plan because that would seem like an obvious assumption. Its actual job, however, is to find a good plan in a reasonable amount of time, rather than the best plan. The optimizer's goal is most commonly described as finding the most efficient plan.

If the optimizer tried to find the "best" plan every time, it might take longer to find the plan than it would to just execute a slower plan (some built-in heuristics actually ensure that it never takes longer to find a good plan than it does to just find a plan and execute it).

As well as being cost based, the optimizer also performs multi-stage optimization, increasing the number of decisions available to find a good plan at each stage. When a good plan is found, optimization stops at that stage. The first stage is known as pre-optimization, and queries drop out of the process at this stage when the statement is simple enough that the most efficient plan is obvious, obviating the need for additional costing. Basic queries with no joins are regarded as "simple," and plans produced as such have zero cost (because they haven't been costed) and are referred to as trivial plans.

The next stage is where optimization actually begins, and it consists of three search phases:

n Phase 0: During this phase the optimizer looks at nested loop joins and won't consider parallel operators (parallel means executing across multiple processors and is covered in Chapter 5.

The optimizer will stop here if the cost of the plan it has found is < 0.2. A plan generated at this phase is known as a transaction processing, or TP, plan.

n Phase 1: Phase 1 uses a subset of the possible optimization rules and looks for common patterns for which it already has a plan.

The optimizer will stop here if the cost of the plan it has found is < 1.0. Plans generated in this phase are called quick plans.

n Phase 2: This final phase is where the optimizer pulls out all the stops and is able to use all of its optimization rules. It will also look at parallelism and indexed views (if you're running Enterprise Edition).

Completion of Phase 2 is a balance between the cost of the plan found versus the time spent optimizing. Plans created

in this phase have an optimization level of "Full."

 

 

Q45:†††††† What is the difference between UPDATE STATISTICS and SP_UPDATESTATS?

††††††††††††††† UPDATE STATISTICS: Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.

†††††††††††††††

SP_Updatestats: sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.

 

Q46:†††††† How to troubleshoot if SQL server agent is not starting?

††††††††††††††† You need to make sure that correct log on account is configured and having sys admin permissions. Also, you need to read SQL server agent log to find out the route cause of the problem.

Q47:†††††† If you have changed default database location, will it impact service pack installation? If yes why?

††††††††††††††† Yes, because once patch is applied successfully, we need to restart SQL server. Upon starting it goes to scripting mode and a temporary database is created. This database will not create and SQL server service would not start. This is very critical and this situation states to rebuild master database in SQL server error log. But a clear message is logged that database could not started , this message is for temporary database.

Q48:†††††† What are different DR solutions?†††††††

††††††††††††††† Log shipping and Database mirroring are DR solution in SQL server.

Q49:†††††† What is different between log-shipping in Mirroring?

 

Log Shipping:

 

Data Transfer:††† T-Logs are backed up and transferred to secondary server

Transactional Consistency:All committed and un-committed are transferred

Server Limitation:†† Can be applied to multiple stand-by servers

Failover:†† Manual

Failover Duration:Can take more than 30 mins

Role Change:†† Role change is manual

Client Re-direction:Manual changes required

With Database Mirroring:

Data Transfer:††† Individual T-Log records are transferred using TCP endpoints

Transactional Consistency:Only committed transactions are transferred

Server Limitation:†† Can be applied to only one mirror server

Failover:†† Automatic

Failover Duration:Failover is fast, sometimes < 3 seconds but not more than 10 seconds

Role Change:†† Role change is fully automatic

Client Re-direction:Fully automatic as it uses .NET 2.0

Q50:†††††† What is Undo or TUF file in log shipping?

††††††††††††††† This is transaction undo file which is used to keep information about incomplete transaction at the time of transaction log backup in log shipping. If this file is missing, then log shipping will fail and need to reconfigure again.

Q51:†††††† What would be the impact of additional manual transaction log backup on log shipping?

††††††††††††††† Transaction log will fail if additional transaction log backup was not taken with copy_only. You need to restore this transaction log backup manually on secondary server. If this transaction log file is missing then need to reconfigure log shipping.

Q52:†††††† What error you get if a transaction log backup file is missing on secondary server?

††††††††††††††† Some thing like backup set is too early to be applied. Need to restore transaction log backup file having a specific LSN number.

Q53:†††††† What would be the impact on log shipping if you add a new data file on primary server?

Solution: When you add a new file to the database at the Primary Server, new Transaction Log backup files which are backed up after you add the new file are not going to be restored to the database at the Secondary Server. You will need to restore your Primary Database (with the new file) at the Secondary Server.

I have to make myself clearer that the above scenario is correct if Primary and Secondary servers are the same logically, also correct when file paths \ drive letters are not the same as the Primary Server. Because at the Secondary Server, SQL Server will try to restore the added file to the same file path as it's at the Primary Server.

So, if Primary and Secondary Servers are separate at least logically and file paths and drive letters are identical, then data and log files added at the primary server should be created at the secondary server when new Transaction Log files are restored at the secondary server.

A more sensible approach: Specifically, do the following steps :

1. Before do the data file split/addition, DISABLE ALL the Logshipping Jobs ( LS- Backup, LS-COPY, and LS-Restore);
2. Manually Run LS-Backup job,
3. when 2 is done, manually run LS-COPY job;
4. When 3 is done, Manually RUN LS-Restore Job;
5. When 4 is done, Do data file Split/Addition;
6. When 5 is done, Manually Start LS-Backup
7. Manually start LS-COPY
8. LOCATE the last copied Transactional Log backup on Share or Secondary server local directory;
9. Under Master DB in Secondary server, RUN a Restore LOG Statement for that LOG file with MOVE all the DB files(including the newly added ones) in the Primary DB to the New locations onyour secondary server, AND make sure adding NORECOVERY;
10. Manually run the LS-restore job (it should give no error)
11. Enable all the LS jobs.

 

Q54:†††††† What would be the impact on mirroring if you add a new data file on primary server?

Since the directory structure is not the same on either server, the database mirror doesn't know what to do with the extra data file which then results in a suspended mode. You cannot just delete the data file/FileGroup to re-establish the mirroring so don't bother trying it. The only thing you can do is redo the entire mirror. Be sure to change the RESTORE DATABASE command to include the new data file(s) and use MOVE to their new directory.

 

Q55:†††††† What is default mirroring port? Is it changeable?

††††††††††††††† 5022 is mirroring default port and yes it is changeable.

Q56:†††††† What are different modes in mirroring and what is the difference?††††††††††

††††††††††††††† High safety (Synchronous without witness. No automatic failover)

††††††††††††††† High availability (Synchronous with witness server. Automatic failover is available)

††††††††††††††† High performance (Asynchronous)

 

Q57:†††††† Synchronous is used for high-performance or high-availability?

††††††††††††††† High availability.

 

Q58:†††††† Suppose you have configured mirroring in synchronous mode on a large database, if your mirror goes down for around 5 hours, what would be the impact on principal database?

†††††††††††††††

Transaction log file will grow. Transaction log backup will not help because committed transactions will flush out from transaction log only once mirror database is in sync.

 

Q59:†††††† What are end points in mirroring?

††††††††††††††† End points are communication channel between SQL server instances involved in mirroing.

 

Q60:†††††† How to failover database mirroring manually?

††††††††††††††† Alter database database_name set partner failover.

††††††††††††††† Below command is to change mirroring mode:

††††††††††††††† Alter database database_name set safety = FULL (Synchronous)

††††††††††††††† Alter database database_name set safety = OFF (asynchronous)

 

Q61:†††††† What are the limitations of Mirroring?

††††††††††††††† Itís database level configuration. Only one mirror copy can be configured. Limited number of databases can be mirrored on a particular instance. MS recommend not to configure more than 10 mirroring in 32 bit server and 22 in 64 bit server. Though, this number depends on transaction rate, server and network configuration.

 

Q62:†††††† How to troubleshoot failed service pack or installation?

††††††††††††††† You need to go through log generated in bootstrap loader directory. Read them very closely to find out exact problem.

 

Q63:†††††† What is the difference between patching on SQL server 2005 clustering and SQL server 2008 clustering?

††††††††††††††† In SQL server 2005, patch was applied on active node which leads to long downtime window. But , in SQL 2008, patch is applied on passive node which reduce downtime and increase SQL server availability.

Q64:†††††† What steps a DBA needs to perform if a new drive is added in Cluster?

††††††††††††††† Need to create SQL server dependency on this drive.

 

Q65:†††††† Who monitor Cluster?

††††††††††††††† Cluster service.

 

Q66:†††††† What is the role of Quorum in cluster?

††††††††††††††† Quorum is brain of clustering , it capture all configuration changes and initiate automatic failover whenever required. If Quorum is down , means cluster is down.

Q68:†††††† Is clustering possible if nodes are in different subnet?

††††††††††††††† Yes this is possible in SQL server 2012 onwards. Cross subnet clustering is available. But not in older versions.

 

Q69:†††††† What is heartbeat?

††††††††††††††† Heartbest is direct connectivity between cluster nodes using which they execute continuous ping commnd against each other.

Q70:†††††† What is virtual name in clustering?

††††††††††††††† This is network name of the cluster. Just like host name in stand alone server.

 

Q71:†††††† How many types of replication are in SQL server?

††††††††††††††† Transaction

††††††††††††††† Snapshot

††††††††††††††† Merge

Q72:†††††† What are different agents in replication?

††††††††††††††† Snapshot Agent

††††††††††††††† Log Reader Agent

††††††††††††††† Distribution Agent

††††††††††††††† Merge Agent

 

Q73:†††††† How to check latency in replication?

††††††††††††††† Trace token can be used to check latency between Publisher-Distributor and Distributor-subscriber.

 

Q74:†††††† What specific property is required for a table to be included in transactional replication?

††††††††††††††† Primary key.

 

Q75:†††††† What is role of distribution database in replication?

††††††††††††††† Distribution database is used in replication is used to distribute the data in subscriber

 

†††††††††††††††

Q76:†††††† How to troubleshot replication?

††††††††††††††† Using replication monitor,

 

Q77:†††††† What is lock escalation? Why itís required.

††††††††††††††† Lock escalation is the process to escalate level of lock on bigger level. Like if there are many rows are locked by process then SQL server escalate this to page level lock for better manageability.

 

Q78:†††††† How to do capacity planning? Suppose you need to find required space for a table which will have 1000000 records? How can you identify required space?

††††††††††††††† This can be down by calculating size of record on the basis of data types and their length defined. Once record size is available we need to identify how many such records can be placed on a single page. Keep in mind that 96 byte space is used as page header and data canít be placed on this space. Hence only (1024*8-96) is available.

 

Q79:†††††† What is default port used by database mail?

††††††††††††††† 25

 

Q80:†††††† What is change data capture?

††††††††††††††† This is SQL server 2008 feature to capture changes made to a particular database. We need to enable it. This is available only with Enterprise and Dev editions.

USE master
GO
SELECT [name]database_idis_cdc_enabled 
FROM sys.databases      
GO
    

USE AdventureWorks
GO
EXEC sys.sp_cdc_enable_db
GO  

 

Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.

 

 

Q81:†††††† What is policy based management? Suppose you want to implement a policy where all user defined SPís should start with USP, is it possible to implement such policy with earlier created SPs not started with USP?

††††††††††††††† Policy based management is SQL server 2008 to define specific policy in SQL server. Example all user defined stored procedure start with USP_ then this can be done by policy based management. If this is enabled and a user try to create a procedure which is not starting with USP_ then he would not be able to create it.

 

††††††††††††††† No. To create such policy we need to make sure that all old user defined stored proc should start USP_ other such policy canít be implemented.

Q82:†††††† What is role of recourse governor? What are the resources are covered by RG?

 

a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU and memory that incoming application requests can use.

 

Q83:†††††† What is Transparent Data Encryption?

 

Q84:†††††† How SQL server performs data compression?

Hereís a simplified example. Letís say that we want to update a row in a table, and that the row we want to update is currently stored on disk in a table that is using row-level data compression. When we execute the UPDATE statement, the Relational Engine (Query Processor) parses, compiles, and optimizes the UPDATE statement, ready to execute it. Before the statement can be executed, the Relational Engine needs the row of data that is currently stored on disk in the compressed format, so the Relational Engine requests the data by asking the Storage Engine to go get it. The Storage Engine (with the help of the SQLOS) goes and gets the compressed data from disk and brings it into the Data Cache, where the data continues to remain in its compressed format.

Once the data is in the Data Cache, the row is handed off to the Relational Engine from the Storage Engine. During this pass off, the compressed row is uncompressed and given to the Relational Engine to UPDATE. Once the row has been updated, it is then passed back to the Storage Engine, where is it again compressed and stored in the Data Cache. At some point, the row will be flushed to disk, where it is stored on disk in its compressed format.



Q85:†††††† What is index selectivity?

Q86:†††††† What are different methods to perform SQL serve upgrade?

††††††††††††††† In Place Upgrade

††††††††††††††† Side By Side by upgrade

Q87:†††††† What are proc and cons of side-by-side upgrade?

††††††††††††††† Advantages:-
    Migration provides more granular control over the upgrade process
    Having new and old instances side-by-side helps with testing & verification
    Legacy instance remains online during migration
    Flexibility to implement migration with failover
    Dis-advantages
    May require new or additional hardware resources
    Applications need to be directed to new instance

Q88:†††††† What are proc and cons of in-place upgrade?

-       Advantage

-       Easier, faster, less headache for small systems
    Requires no additional hardware
    Applications remain pointing to old instance

Dis-advantages
    Less granular control over upgrade process
    Instance remains offline during part of upgrade
    Not best practice for all components
    Analysis Services cubes are recommended to be migrated

 

 

Q89:†††††† What are disadvantages of database detach?

††††††††††††††† Database owner gets changed. Default database is changed to Master for logins have this particular database as default.

 

Q90:†††††† What is dedicated administrator connection? How to use it?

 

SQL Server provides a special diagnostic connection for administrators when standard connections to the server are not possible. This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.

This dedicated administrator connection (DAC) supports encryption and other security features of SQL Server. The DAC only allows changing the user context to another admin user.

SQL Server makes every attempt to make DAC connect successfully, but under extreme situations it may not be successful.

                     sqlcmd ĖA Ėd master