Home
MS SQL
Interview FAQs

FAQs of level

This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.

Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.

The global variable @@Version stores the build number of the sqlserver.exe, which is used to determine the service pack installed. eg: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
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.

INTERSECT - gives you the final result set where values in both of the tables match
EXCEPT - gives you the final result set where data exists in the first dataset and not in the second dataset
The advantage of these commands is that it allows you to get a distinct listing across all of the columns such as the UNION and UNION ALL operators do without having to do a group by or do a comparison of every single column.  


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.

These are some common post restore processes:

  • Sync the logins and users
  • Validate the data is accurate
  • Notify the team/user community
  • Cleanse the data to remove sensitive data i.e. SSNs, credit card information, customer names, personal information, etc.
  • Change database properties i.e. recovery model, read-only, etc.

These are the recommendations to design a backup and recovery solution?

  • Determine What is Needed
  • Recovery Model
  • Select Backup Types
  • Backup Schedule
  • Backup Process
  • Document
  • Backup to Disk
  • Archive to Tape
  • Backup to Different Drives
  • Secure Backup Files
  • Encrypt or Password Protect Backup Files
  • Compress Backup Files
  • How Much to Keep on Disk
  • Online Backups
  • Run Restore Verifyonly
  • Offsite Storage

  1. The MSDB database is the database with the backup and restore system tables.
  2. Here are the backup and restore system tables and their purpose:

  • backupfile - contains one row for each data file or log file backed up
  • backupmediafamily - contains one row for each media family
  • backupmediaset - contains one row for each backup media set
  • backupset - contains one row for each backup set
  • restorefile - contains one row for each restored file
  • restorefilegroup - contains one row for each restored filegroup
  • restorehistory - contains one row for each restore operation

Backups can be automated by using a cursor to loop through each of the databases and backup each one

Restores can also be automated by either looping over the files, reading from the system tables (backup or log shipping) or reading from a table as a portion of a custom solution

These are some common reasons why database restores fail:

  • Unable to gain exclusive use of the database.
  • LSNs are out of sequence so the backups cannot be restored.
  • Syntax error such as with the WITH MOVE command.

Need Help? Contact Us.

Log in

*
*

Forgot password?

*

New User

*
*
*
*