Q. When you upgrade a SQL Server, the upgrade wizard seems to stop responding and fails. Why?

If applications or services have opened ODBC connections to SQL Server 2005 during the conversion process, they may not allow SQL Server to shut down completely. The conversion process will not proceed to the next step if it does not receive verification that SQL Server has been completely stopped.

Q. What are the terminology used for Replication?

Publisher server

Distributor (Optional)

Subscriber server

Q. How to rollback the upgrade?

If the legacy SQL Server instance is replaced by a new SQL Server 2008 instance, rolling back an in-place upgrade can be complex and time-consuming; whereas, in a side-by-side upgrade, the legacy instance remains available if a rollback is needed.

Q. What is log shipping?

The source data will be copied to destination via Transaction Log backup jobs. Database level tech.

Q. What is SSIS?

SQL Server Integration services

Q. What is the usage of SSIS?

It is used to carry out (Extraction, Transform, Load data) ETL

Q. How to speed up the DBCC CHECKDB execution process?

The below command enforces an exclusive lock on the database, which makes the process faster:

DBCC CHECKDB (‘TestDB’) WITH NO_INFOMGS, TABLOCK

Q. What is the usage of Analysis service?

To analyse huge amounts of data and apply to business decisions.

Q. What are the terminology used in log shipping?

  • Primary server.
  • Secondary server.
  • Monitor server (Optional)

Q. What is mirroring?

The primary data will be copied to secondary via network. Database level tech.

Q. What is PHYSICAL_ONLY in DBCC CHECKDB?

The PHYSICAL_ONLY command limits checking the integrity of the physical structure of a page and record headers and can also detect torn pages, checksum failures, and common hardware failures. Using this option may cause shorter run-time for DBCC CHECKDB on large databases and is recommended for frequent use on production systems. Specifying the PHYSICAL_ONLY option causes DBCC CHECKDB to skip all checks of the FILESTREAM data.

DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, PHYSICAL_ONLY

Q. What are the terminology used for mirroring?

  • Principal server.
  • Mirror server.
  • Witness server(Optional)

Q. How to check data purity using DBCC CHECKDB?

The below command causes DBCC CHECKDB to check the database for column values that are not valid or out of range:

DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, DATA_PURITY

With this command, DBCC CHECKDB detects columns with date and time values, which are either larger or less than the acceptable range for the DATETIME data type. It also limits checking the integrity of the physical structure of the page and record.

Q. How to clear all records including data types in a table?

Truncate table Tablename

Q. What is clustering?

The data will be stored in shared location which is used by both primary and secondary servers based on availability of the server. Instance level tech.

Q. How long are locks held/retained within the REPEATABLE_READ and SERIALIZABLE isolation levels during a read operation, assuming a row-level locking?

Within the REPEATABLE_READ and SERIALIZABLE isolation levels, locks are held/retained for the duration of the transaction, unlike within the READ_COMMITTED isolation level.

Q. What are the terminology used for clustering?

  • Active node.
  • Passive node

Q. Which two isolation levels support optimistic/row-version-based concurrency control?

  • One is the READ_COMMITTED isolation level. This is the only level that supports both a pessimistic (locking-based) and an optimistic (version-based) concurrency control model.
  • The other is the SNAPSHOT isolation level that supports only an optimistic concurrency control model.

Q. What is Always ON Availability Groups?

The primary data will be copied to secondary via network. Group of database level tech.

Q. What database options must be set to allow the use of an optimistic concurrency control model?

The READ_COMMITTED_SNAPSHOT option for the READ_COMMITTED optimistic model and the ALLOW_SNAPSHOT_ISOLATION option for the SNAPSHOT isolation level.

Q. What are the terminology used for Always ON Availability Groups?

  • Primary is source server.
  • Secondary is destination server

Q. Explain the purpose of Intent locks.

The database engine uses intent locks to protect placing a shared (S) lock or an exclusive (X) lock on a resource (lower in the lock hierarchy). Intent locks are named thus because they are acquired before a lock at the lower level and therefore signal the intent to place the locks at a lower level. Intent locks serve two purposes:

  • They prevent other transactions from modifying a higher-level resource in a way that would invalidate the lock at the lower level.
  • They improve the efficiency of the database engine in detecting lock conflicts at a higher level of granularity.

Q.  What is the usage of reporting services?

To create and publish various kinds of reports.

Q. What is the command to see the execution plan by user?

SHOWPLAN

Q. what is the syntax to get top 60 records in a table?

“Select top 60 * from table”

Q. How to read the graphical execution plan?

The graphical execution plan should be read from Right to Left:

  • Check the graphical execution plan of a stored procedure/query
  • Table Scan: Index is missing
  • Index Scan: Proper indexes are not used
  • BookMark Lookup: Limits the number of columns in the select list
  • Filter: Removes any functions from the WHERE clause; may require additional indexes
  • Sort: Checks if the data really needs to be sorted, if an index can be used to avoid sorting, and if sorting can be done at the client-side more efficiently?
  •  DataFlow Arrow (high density): Sometimes, we find few rows as the outcome, but the arrow line density indicates the query/proc processing huge number of rows
  •  Cost: Easily finds out which table/operation taking much time

From the execution plan, we can find out the bottlenecks and give possible solutions to avoid latency

Q. What is syntax to insert data into a table?

Insert into table(Column1, column2,etc)

Values(‘’,’’,etc)

Q. What are the environments used for reports?

The BIDS and SSDT

Q. What is execution plan?

Helps to statistics and processor tree. It is the result of query optimizer.

Q. How to delete current day records in a table which has timestamp column?

Delete from table where datediff(day,timestamp,getdate())=0

Q. What are types of query estimation plans there?

There are two types of query estimation plans…

  • Estimated
  • Actual

Q. How many types of execution plans formats are available?

There are three formats of execution plans…..

  • Graphical
  • Text
  • XML

Q. What are the permissions required to view the execution plan?

Either a user must be mapped to sysadmin/db_owner/db_creator or the user will be granted the below permission:

GRANT SHOWPLAN TO [username]

<< BACK TO PART ONE |||||||||||||||||||||||||||||||||NEXT PAGE>>>