Interview Questions: SQL Server DBA

by Posted on

 Questions

  1. Explain SQL Server Failover Clustering?
  2. How do we check index Fragmentation?
  3. TSQL syntax for Index rebuild and reorg. Difference between the two. How users are effected?
  4. How do we rebuild large indexes?
  5. Detect Locks in SQL Server.
  6. Simulate a deadlock situation. Explain how SQL Server handles and comes out of the situation.
  7. List out some commonly used DMVs.
  8. Function to retrieve file level space usage.
  9. List out and Explain some useful DBCC commands.
  10. How do we store datafiles in a network drive.
  11. Faster way of counting rows in a table.
  12. What do we do when tempdb is full.
  13. Whats the role of MSDTC.
  14. Expalin two phase commit.
  15. Steps before and after SQL Server upgrade. And steps to perform to enhance performance or prevent performance problems as a result of upgrade.
  16. Isolation levels in SQL Server.
  17. Restore a database from suspect mode without using Backup.
  18. Frequestly used stored procedures and functions.
  19. TLOG for tempdb is full and is not shrinking with shrink command. How do we take care of this issue?
  20. What all activities happen during a checkpoint?
  21. Events that can trigger a checkpoint.
  22. How to change checkpoint interval? and whats the default interval?
  23. Activities happening while a SQL Server instance starts.
  24. What is 3GB switch?
  25. Find memory usage of three instances respectively in a Server from Task manager.
  26. How can a database go into suspect mode?
  27. Different modes for a database in SQL Server.
  28. Find tables that has no indexes.
  29. Move indexes to a separate file group.
  30. Will log-shipping work for bulk logged recovery model?
  31. Different modes of Database mirroring?
  32. Role of Cluster Quorum disk.
  33. Two communication methods between the nodes of cluster.

Part 2

  1. Maximum Number of instances possible in a SQL Server?
    1. 50 instances on a stand-alone server for all SQL Server editions. SQL Server supports 25 instances on a failover cluster when using a shared cluster disk as the stored option for you cluster installation SQL Server supports 50 instances on a failover cluster if you choose SMB file shares as the storage option for your cluster installation.
  2. Frequency at which the merge agent checks for changes at publisher and subscriber in a merge replication.
    1. By default 60. Right click merge agent and look for pol interval inside agent profile from management studio.
  3. Main differences in installation steps for 2005 and 2008 failover cluster.
  4. Best recommended replication possible for sales person who do not have access to Office internal LAN. Keep in mind that they can connect only through internet without vpn.
    1. Enable web synchronization. Refer http://msdn.microsoft.com/en-us/library/ms151810.aspx and http://msdn.microsoft.com/en-us/library/ms345214.aspx
  5. What happens to a table if it doesn’t have a clustered index? Ans: It becomes a heap.

Answers and Clues

1.

a. Group of independent servers that work together to increase the availability of applications and services, protecting against software and hardware failiures by failing over resources from one server to another as required.

b. Failover cluster requires one or more clustered servers (called nodes),  configuration of shared cluster disks, two networks for communication  (atleast one public and one private.)

 

2. use dmv

sys.dm_db_index_physical_stats(
 'database_id',
 'object_id',
 'index_id',
 'partition_number',
 'mode')

. Refer MSDN for more details.

 

3.

Reorganize: This defragments the indexes by moving contents across pages making the data contiguous. Highly fragmented tabled should not be reorganized. Instead we should go with index rebuild. Index reorg is an online process and it can be stopped or cancelled anytime. The work done so far will not be rolled back as compared to index rebuild.

Rebuild: Existing Indexes are dropped and rebuilt from scratch. Enterprise Edition supports online index rebuild. If the index rebuild process is killed in the middle, entire work done on that index will be rolled back.

Refer http://technet.microsoft.com/en-us/library/ms188388.aspx for Syntax and Samples.

To automatically rebuild or reorg indexes, refer my post https://www.sherbaz.com/2011/12/automatically-rebuild-or-reorg-index-based-on-fragmentation/

 Helpfull Links:

http://www.sqldbadiaries.com/2010/09/05/mr-dba-what-is-the-status-of-rebuild-index/

http://www.sql-server-performance.com/2011/index-maintenance-performance/

 

16. Isolation Levels in SQL Server

Command: “SET TRANSACTION <Isolation Level>”

Read Uncommitted

– Lowest
– Higher concurrency
– All concurrency problems: Dirty reads, lost updates,
Nonrepeatable reads(Inconsistent analysis) and phantom reads.

Read Committed

– Eliminates dirty-reads
– Other concurrency problems exists.
– Default Isolation level of SQL Server

Repeatable Read

– Eliminates all concurrency problems except Phantom reads.
– Does not release the shared lock once the record is read and keeps till the transaction
is over.

Serializable

– Highest Isolation level.
– Avoids all concurrency related problems.
– Its just like Repeatable read with one additional feature. Obtains key range locks based on the filters that have been used. It locks not only current records that stratify the filter, but new records that fall into same filter.

Snapshot Isolation Level

– Works on Row Versioning Technology.
– When a transaction is gonna modify something, SQL server will first store the consistence version of the record in tempdb so that when another transaction running on same isolation level requires same record, it can be taken from the version store(in Tempdb).
– Prevents all concurrency problems. And also it allows in multiple updates for same resource by different transactions cuncurrently.

Read commited snapshot

– New implementation of Read commited.
– Has to be applied at database level and not session or transaction level.
– Read committed Vs Read Commited snapshot : Pessimistic Vs Optimistic.
– Snapshot Vs Read commited Snapshot : Unlike snapshot, It always returns latest consistence version and no conflicts are detected.
– All concurrency problems will happen except dirty reads.

Above points were summarized from http://www.sql-server-performance.com/2007/isolation-levels-2005/3/

 17. Recover a DB from suspect mode without backup.

Refer https://www.sherbaz.com/2012/03/error-transaction-log-file-got-damaged-or-corrupted/. The procedure is similar.

 

Published by Sherbaz

Technology enthusiast Sherbaz is a Microsoft certified SQL Server Subject Matter Expert. He had fun automating day to day task to achieve management goals. He has extensive hands-on experience in high availability deployments and support with several years of industry experience performing SQL Server installations, configuration, maintenance, backups, performance tuning, Transactional & P2P replication, migration, high availability solutions (Always on, Failover Clustering, Logshipping and Mirroring) and Disaster recovery. Sherbaz also has basic hands-on experience in other DBMSs like MongoDB, Oracle 9i, 10g, Sybase and MySQL. 1 year on Mongodb, Oracle 9i & 10g. Completed M102 & M202 courses in MongoDB. Sherbaz spends his spare time with automation in electronics, embedded system circuits and IOT in his private lab at home. Also, he supports his customers and users at www.SplitExpense.in for issues with app, website or telegram chat bot @SplitExpenseBot https://t.me/SplitExpenseBot. @TheSherBot https://t.me/TheSherBot is another telegram chat bot, partially artificial intelligent, programmed and maintained by him on python and backend Mongodb database. Sherbaz always showed interest learning new technologies and to think out of the box bringing innovation in work. Some of his work is documented @ www.sherbaz.com. He is now busy learning 3D modeling software on his roadmap towards building his next hobby build, A 3D PRINTER. Subscribe to his youtube channel https://www.youtube.com/c/SherbazMohamed/

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.