Monday, April 15, 2013

SQL Server - Interview Questions and Answers . (Updated in 2018)

SQL Server DBA interview questions and answers..
Lets help other dba to find their dream job.

  •  Tell me about your self. - Most of the time, this will be the first questions in your interview.
  •  What is a page in sql server? -  The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages. In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page. Data rows are put on the page serially, starting immediately after the header. A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.
    SQL Server data page with row offsets


  • What is an extent in sql server? - Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are stored in extents. Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.
    To make its space allocation efficient, SQL Server does not allocate whole extents to tables with small amounts of data. SQL Server has two types of extents:

    Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
    Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object. A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.




    What is the relation between page and extent? - read above answer.
    How many services are installed when you install sql server first time? - SQL SERVICES, SQL AGENT SERVICE, SQL BROWSER SERVICE, SQL SERVER ACTIVE DIRECTORY HELP, SQL SERVER VSS WRITER.



    What doesn SQL Browser Service do? - The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:

    Browsing a list of available servers
    Connecting to the correct server instance
    Connecting to dedicated administrator connection (DAC) endpoints
    What types of indexes are available in SQL Server? Cluster Index and Non Cluster Index.
    What is the difference between Cluster and Non-Clustered index?
    Why do you need Non-Clustered Index?
    What is covering index? Why do you need Covering Index?
    What is Composite Index? Why do we need it?
    Difference between Composite Index and Covering Index.
    What was your backup strategies?
    What is the difference between Full backup vs Differential backup?
    What is tail-log backup? When do we use tail-log backup?
    What is the difference between rebuild index and reorg index?
    When do we rebuild index and reorg index?
    What is sql statistics and what kind of information it contains?
    When do you need to update statistics?
    How do you find out statistics is not updated?
    Which DMV finds the missing indexes?
    Is it good idea to add all recommended missing indexes?
    How do you find out Indexes are fragmented? How do you fix this issue?
    How do you find out the length of the column?
    How do you find out BLOB object column length?
    What is your DR strategies?
    What is the difference between Mirroring and LogShipping?
    What types of mirroring are available?
    What is the difference between Asynchronous and synchronous mirroring?
    How do you setup LogShipping in your environment?
    Have you ever worked with replication? What types of replications are available?
    How do you setup replication if database is really big?
    How does replication works? exp: Transactional Replication.
    How do you solve latency issue in sql server?
    Tell us about some DBCC commands you use in daily work.
    Tell us about some DMV commands you use in daily work.
    Have you ever worked on clustered environment? What is the difference between active and passive clustered?
    How big was your clustered environment?
    What is the difference between 2005 and 2008 clustered?
    Can you install sql server from remote machine in cluster?
    Have you ever add SAN in clustered? if Yes, what are the steps to add SAN in clustered?
    Have you ever add or move Quorum drive? What are the steps to do it?
    What are the steps to upgrade SQL on clustered environment?
    What is minimum requirements before installing SQL on Cluster environment?
    What is Index Padding and Fill Factor?
    What would you do if you want to update statistics and db is really huge?
    What is Sample rows or percentage in Update Statistics?
    Have you ever worked with reporting service? Explain your role in that.
    How to migrate reports from one server to another server?
    How to find out error in reporting server? - Reporting service has their own log file.
    Have you ever worked with SSIS? explain your role in that.
    How many ways you can deploy SSIS packages?
    What is the difference between File System and SQL Server deployment?
    How to migrate packages from File System to File system and MSDB to MSDB?
    If you are doing replication of one database and if you do rebuild index on primary server does secondary server will follow same?
    How to move one table from one disk to another disk when there is a space issue on database and you don't have any other data files?
    How to add data or log file when database is in Mirroring, Log-Shipping or AlwaysOn?
    How to start SQL when WSFC is offline? https://www.mssqltips.com/sqlservertip/4917/recover-wsfc-using-forced-quorum-for-sql-server-alwayson-availability-group/
    How to upgrade SQL Server when database is in AlwaysOn?
    How to troubleshoot AlwaysOn performance or not synchronizing issue?
    Which SQL 2016 features you have been using in your current environment?
    What is the difference in SQL 2012 and 2014/2016 AlwaysOn Availibility Group?
    What is the difference between SQL 2012 and SQL 2016 installation?
    What is SQL Server R Service?