SQL Server DBA Interview question and answers Part 2

1. What is Trace Flag ? Where do we use it ?

Trace Flags are used to switch on specific server characteristics temporarily .
DBCC TRACEON is the command to set the trace flags .
Once activated , trace flags will be in effect until the server is restarted .
Trace Flags are frequently used for diagnosing performance issues .

Ex: the trace flag 3205 is used for disabling hard compression for tape drives when an instance of SQL Server starts .

2. If the SQL Server crashes and if all the differential backups are bad , when is the latest point in time you can successfully restore the database ? Can you recover the database to the current point in time without using any of the differential backups ?

You can recover to the current point in time as long as you have all the transaction log backups available and they are all valid .
Differential backups do not effect the transaction log backup chain.

3. What methods are available for removing fragmentation of any kind on an index in SQL Server ?

ALTER INDEX ... REORGANIZE
ALTER INDEX ... REBUILD
CREATE INDEX ...DROP EXISTING (cluste)
DROP INDEX
CREATE INDEX

4. What is the fundamental unit of storage in SQL Server log files and what is it's size ?

A Log Record , size is variable depending on the work being performed .

5. How many different types of pages exist in SQL Server ?

There are 9 pages available .

Data Page
Index Page
Text / Image (LOB , ROW_OVERFLOW,XML)
GAM (Global Allocation Map)
SGAM ( Share Global Allocation Map)
PFS ( Page Free Space )
IAM (Index Allocation Map )
BCM (Bulk Change Map )
DCM (Differential Change Map )

6. If you need REBUILD a non-clustered index that is 10 GB in size and have 5 GB of free data-file space available with no room to grow the data file(s) , How can you accomplish the task ?

You can use SORT_IN_TEMPDB=ON during index rebuild operation .

SQL Server uses tempdb to store the intermediate sort results when SORT_IN_TEMPDB=ON  which are used to build the INDEX while reindexing / rebuilding .

Ex:

Use AdventureWorks2012
GO
Alter Index All on Production.product REBUILD
with
(
Fillfactore=80,Sort_In_tempDB=on,Statistics_Norecompute=ON
);


7.  What special type of page with in SQL Server that is responsible for tracking (via simple bit map ) extents that have been modified since the last "BACKUP DATABASE" statement ?

BCM Pages . Bulk changed map stores all extents which have been modified since last full backup .


8. How is it possible to capture the IO and time statistics for your queries ?

Use SET STATISTICS IO and SET STATISTICS TIME setting in your queries or enable the setting in your management studio .

9. What is fill factor ? What is default fill factor value ?

Fill factor is a setting that is applicable to indexes in SQL Server . The fill factor value determines how much data is written to an index page when it is created / rebuit .

By default the fill factor value is set to 0.

10. What are the different types of database compression introduced in SQL Server 2008 ?

Row compression & Page compression .

11. What are the different types of upgrades that can be performed in SQL Server ?

in-place upgrade and side-by-side upgrade .

12. What operations comes first during recover - Rollback of Roll forward ? 

Roll Forward

13 .What does sp_validatename ?

It checks for a valid SQL Server identifier .

14. What options / arguments can be specified in a BACKUP LOG statement to keep inactive log records from being truncated ?

For SQL Server 2005 and previous versions BACKUP LOG with NOTRUNCATE option .
After 2005 database recovery model should be changed from FULL to SIMPLE .

15. What are the primary differences between an index reorganize and index rebuild . ?

A reorganize in an "online " operation by default where as rebuild is an "offline" operation .
A reorganization only affects the leaf level of an index.
A reorganization swaps data pages in-place by using only the pages already allocated to the index
A rebuild uses a new pages / allocations.
A reorganization is always a fully-logged operation ; a rebuild can be a minimally-logged operation.
A reorganization can be stopped mid-process and all completed work is retained; a rebuild is transnational and must be completed in entirety to keep changes.




I hope the article was useful! And I need to let you know that this won’t stop here .keep in touch and feel free to ask any doubts related to this topic .