SQL Server DBA Interview Questions and Answers for Experienced
Qus: Different type of pages in SQL Server?
Ans: The following table shows the page types used in the data files of a SQL Server database.
| Page type | Decription |
| Data | It holds the data of the table for databases.The format of the page is header , data rows and row id. The row id starts from right to left. Data row contains the actual data of rows.Header size that is 96 bytes. |
| Index | They hold the information about indexes.SIZE of 8KB each. Indexes are actually B tree like structures.Size of header is not fixed. |
| Shared Global Allocation Map | SGAM bitmap is exactly the same as the GAM bitmap in structure and the interval it covers, but the semantics of the bits are different:
|
| Global Allocation Map, | Information about whether extents are allocated.The bits in the GAM bitmap have the following semantics:
|
| Index Allocation Map | It is a bit map page. This page tells where the data pages of that particular object lies; Means it gives us path for data pages of a particular object.Eachobject will have a minimum IAM page. 1 IAM Page manages 64000 extents.One object can have more than 1 IAM page.The address of the IAM page can be collected from sysindex table. |
Qus: DBCC INPUTBUFFER command usages?
Ans:
DBCC INPUTBUFFER ( session_id [ , request_id ]) [WITH NO_INFOMSGS ]
session_id: Is the session ID associated with each active primary connection.
request_id: Is the exact request (batch) to search for within the current session.
The following query returns request_id:
SELECT request_id FROM sys.dm_exec_requests WHERE session_id = @@spid;
WITH: Enables options to be specified.
NO_INFOMSGS: Suppresses all informational messages that have severity levels from 0 through 10.
DBCC INPUTUFFER returns a rowset with the following columns:
| Column name | Data type | Description |
| EventType | nvarchar(30) | Event type. This could be RPC Event or Language Event. The output will be No Event when no last event was detected. |
| Parameters | smallint | 0 = Text1- n = Parameters |
| EventInfo | nvarchar(4000) | For an EventType of RPC, EventInfo contains only the procedure name. For an EventType of Language, only the first 4000 characters of the event are displayed. |
Qus: Types of locks in SQL Server?
Ans:
SQL Server have following types of Locks:
| Lock mode | Description |
| Shared (S) | Used for operations that do not change or update data (read-only operations), such as a SELECT statement. |
| Update (U) | Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later. |
| Exclusive (X) | Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time. |
| Intent | Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX). |
| Schema | Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S). |
| Bulk Update (BU) | Used when bulk-copying data into a table and the TABLOCK hint is specified. |
Qus: Quorum in Database Mirroring?
Ans:
Quorum is a relationship that exists when two or more server instances in a database mirroring session are connected to each other. Typically, quorum involves three interconnected server instances. When a witness is set, quorum is required to make the database available. Designed for high-safety mode with automatic failover, quorum makes sure that a database is owned by only one partner at a time.
If a particular server instance becomes disconnected from a mirroring session, that instance loses quorum. If no server instances are connected, the session loses quorum and the database becomes unavailable. Three types of quorum are possible:
- A full quorum includes both partners and the witness.
- A witness-to-partner quorum consists of the witness and either partner.
- A partner-to-partner quorum consists of the two partners.
Qus: How to check Index fragmentation by SQL Command?
Ans:
SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
Qus: How to take tail log backup in SQL Server?
Ans:
BACKUP LOG AdventureWorks2012 TO MyAdvWorks_FullRM_log1 WITH NO_TRUNCATE;
Qus: How to do manual failover through command in Database Mirroring?
Ans:
Issue the following statement on the principal server:
ALTER DATABASE database_name SET PARTNER FAILOVER
where database_name is the mirrored database. This initiates an immediate transition of the mirror server to the principal role.
Qus: Difference between SEEk and SCAN in SQL Server?
Ans:
| Scan | Seek |
| When search definition can not point close on single row/ Range of rows to satisfy search predicates. It called as scan | When index definition can point close on single row/ Range of rows to satisfy search predicates. It called as Seek |
| SQL Server has to scan multiple pages to find range of rows which satisfy search predicates. | SQL Server knows which page / Range of rows to read |
| Scan is good if you have to retrieves all the rows from the table. | Seek is used if we need selected rows from the table |
| It reads every row in the table whether or not it qualifies | It reads rows that qualify and pages that contain these qualifying rows |
| Scan cost is proportional to the total number of rows in the table | The cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table. |
| If Scan is table scan means table doesn’t have index | Seek is always on index only |
| If table is having very small no. of rows and its not very frequently used, scan and seek will be almost same | If table is having huge no. of records then seek will give huge performance impact. |
| When SQL Server does a scan it loads the complete object which it wants to read from disk into memory, then reads through that object from top to bottom looking for the records that it needs. | It knows where in the index data is going to be, so goes directly to the part of the index that it needs and load to memory |
| You never want to convert seek to scan but if you just want it, then drop index | By addin column in where clause and select option in index, we can convert a scan into seek operation. |
| A scan means whole data irrespective of data you want | A seek means that you are looking for specific value(s) and the index provides you with the best way to do it |
| Scan can be possible with index or table | Seek is always with index , Db engine search data in B tree |
| Scan is of 3 types clustered index scan , non clustered index scan , table scan | Seek is of two types clustered index seek and nonclustered index seek |
| An Non Clustered index scan is a complete scan of all the leaf pages in B tree to to find index key of cluster index | An Non clustered index seek is a seek through the B-tree structure of a non-clustered index to find index key of clusterindex leaf page, from the root down to the leaf |
| A clustered index scan is a complete scan of all data pages at leaf in a clustered index | A clustered index seek is a seek through the b-tree structure of a clustered index, from the root down to the leaf |
| Nonclustered index scan means lot fewer pages then in clustered index scan | Nonclustered index seek means only pages required for data address, where as in clustered index seek it only reads data pages. |
| Order of data doesnt impact much | Search is fast beacause data is stored in order based on the clustered index key |
| Fragmented data affects scan most as whole data need to be read from disk | fragmented data affects but not as compare to scan, as sql engine reed minimal required data. |
| Scan is genreally not good , some queries it will use a table scan even when a useful index is present – this is usually because the data in the table is so small that it is more hassle to traverse the indexes (if this is the case you would expect the plan to change as the data grows, assuming the selectivity measure of the index is good). | seek is generally much better, but a great many seeks (caused by bad query design with nasty correlated sub-queries for instance, or because you are making many queries in a cursor operation or other loop) can be worse than a scan, especially if your query may end up returning data from most of the rows in the affected table. |
| Table scan only appears for a heap ie table without a clustered index. The first page in the heap is located based on info in the system tables, and then the pages are read one by one, using the next and, if necessary, previous pointers in the page headers. This is generally an expensive operation and should be avoided where ever possible | Seek is not possible without index |
| Clustered index scan is similar to table scan, just on a table that has clustered index. This operation reads the leaf pages of the clustered index, using the next and previous page pointers. Like with the table scan, this can be an expensive operation and should, wherever possible be avoided | Clustered index seek uses the clustered index’s b-tree structure. The seek starts at the root of the tree and navigates down the levels of the index until it reached the leaf page(s) with the desired data. This operation also appears when a partial scan of the table is done, when the index’s tree is used to locate a page, and the index is scanned from that point until another point in the table (possibly the end). |
| Index scan means reading all the leaf pages of a non-clustered index using the next and previous page pointers to navigate. Because non-clustered indexes generally have fewer pages in the leaf than a clustered index, this operation is usually cheaper than a clustered index scan | Non-clustered index seek is the same as the clustered index seek, just using a non-clustered index |
| This some times comes with lookups (Row \ Bookmark) , apart of scanning , Another idex is used to over result of scan | In a seak not all of the index is considered. Only one used index is enough |
| We can see scan is horizontal arrow | Seek is vertical arrow explain its nature of work |