Advanced SQL Server Interview Questions

I came across several questions during an Interview for SQL admin; I thought other folks might also benifit from it.

And I will try to add more to the list as long as I am being Interviewed! ;)

• Question: When you failover an SQL Server from node1 to node 2, do all processes/users automatically shifted to node 2?
• Jobs running on node 1 will fail and do not automatically Failover to node2. You might need to run those jobs manually after the Failover.
• 
  
• Question: How can you move a physical table from one disk to another (using filegroup),
• Recreate the clustered index of the table on the destination disk (filegroup), the physical table automatically moves along with the clustered index to the new disk.
• 
  
• Question: When you upgrade a DB what thing doesn't automatically get updated?
• run UPDATE STATS manually because statistics are not automatically updated upon upgrade.
• 
  
• Question: Is it fine to have a fill factor of value 100 by default when creating an index?
• Do not automatically accept default fill factor value of 100 unless your data seldom changes. You might need a lower fill factor value for OLTP db to reduce page splitting and save I/O.
• 
  
• Question: Is it fine to have SQL startup account as a member of Builtin/Administrators group, if not then what explicit rights might be required for a startup account with limited rights?
• 
  
• Though it’s recommended that SQL start up server account should not be domain admin, and should have limited rights to avoid risking complete machine/network in case of injection/intrusion; You might need LOCK PAGES IN MEMORY setting to be explicitly allowed for the SQL start up account in order to start the SQL service.
• 
  
• Question: How do we replicate dbs over WAN with limited internet bandwidth and is it possible to setup a single large publication in that case?
• While replicating Large DBs over WAN, you need to setup ftp options on publication so the snapshot could manually be uploaded on ftp for subscriber to download. You can not create a snapshot file (.cab) over 2 GB, so if you have large tables; use multiple publications with less than 2 GB snapshot cab file.
• 
  
• Question: How can we brwose SQL configuration options without wanting to enable advanced options
• SELECT * FROM SYS.CONFIGURATIONS
• 
  
• Question: What is the recommended size to set when setting up a disk or RAID for an SQL database
• Optimum Block size to set when formatting a disk or setting up RAID is 64 K for SQL database, as SQL Server basic data storage is in Extents which are of 64 K.
• 
  
• Question: Is it a problem if your NTFS disk is 90% used?
• NTFS needs room to work,and when you exceed 80% capacity, NTFS become less efficient and I/O cansuffer for it. Keeping adequate free space onf the disk will prevent the MTF from becoming fragmented, which is important because the Disk Defragmenter tool can't defragment the MFT.
• 
  
• Question: In SQL 2000, we know that page size in SQL storage structue is 8064 bytes out which 4 is header information. what's the actual row size in a data page?
• The actual max row size in SQL 2000 is 8039 bytes, not 8060. A table to hold a row of 8040 bytes is not possible and the following command will fail:
• CREATE TABLE ROWSIZE ( address CHAR(8000), name CHAR (40) )
• 
  
• Question: In SMS executing the below command; what will be the output.
  SELECT @@version
• GO 5
• It will execute the select command five times.
• 
  
• Question: Does enabling AWE on 32 bit OS means better Procedure Cache?
• SQL on 32 bit OS with AWE eabled still can't address more than 4 GB of virtual address space. Only buffer cache can benifit from more RAM on 32 bit with AWE enabled.
• 
  
• Question: Does enabling AWE on 64bit OS means better Procedure Cache?
• True, however one of the Draw back of SQL on 64 bit OS is that we can not manage procedure cache size and SQL server may prefer to keep large expensive plans in cache and it drains out memory. And a larger procedure cache may also impact the time for SQL to search the procedure cache to determine if a plan exists.
• 
  
• Question: What's the purpose of AWE enable option in SQL 2005 64 bit
• On 64 bit OS AWE isn't ignored (as per several msdn posts) " locked pages, awe mechanism, enable better application's scalability and performance both during ramp up time and over long period of time." http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
• 
  
• Question: How can we handle an SQL statement to rollback if it gets blocked, instead of waiting for the blocking process to commit or rollback?
• By using SET LOCK_TIMEOUT
• 
  
• Question: What's wrong with the following queries:

SELECT name FROM DepartmentsTable ORDER BY id
SELECT DISTINCT name FROM DepartmentsTable ORDER BY id

• Second Query will return error because when using DISTINCT all columns should be in SELECT statement referred in ORDER BY cluase.

Career as a DBA

Database Administration comes into play in medium large and large enpterprises and organizations running database server boxes where the business can not tolerate downtime and data availability is very critical.
Such setups involve expensive hardware and an enterprise class DBMS solution , eg., MSSQL Enteprise Edition, ORACLE Enterprise Edition etc.
Usually the server boxes include SAN(Storage Area Network) or NAS(Network Area Storage) setup as clusters and connected to multiple active/active or active/passive server boxes. The SAN usually connects to the server through a Host Bus Adapter and the medium could be Fiber Channel or some other fast communication channel.
NAS connects to the server via a Switch and has much more flexibility of placing it physically farther than the servers.
The disk drives in NAS, SAN, or the Server itself are fast SATA or SCSIs disks bundled up in different level of hardware RAIDS for faster read/writes and fault tolerance.
Many people focus more on multiple high speed processors with not quite edequate L2 Cache. As a general rule of thumb, a PIII processor running on a board with greator L2 Cache would outperform a P4 Processor running with inadequate L2 Cache.
A DBAs task also involves selecting and configuring RAIDs to serve different kind of I/O activity; example is that you require different RAID for sequential Reads/Writes and different for non sequential Reads/Writes. One also needs to accommodate the fault tolerance.
Technically speaking, DBAs are most prone to waking up nights and staying long at office for backups , performance and downtime issues. The business stucks if the db server goes down; that adds your value to your organization more than a Developer(no offence). DBA is a hot and fun job, but you also take stress along with you at home with a fear of being called late night for assistance:)

A DBA tasks include:
Disaster Recovery Plans (including backups, restore testing)
Performance Tuning (Includes hardware and Server performance monitoring, SQL optimization)
Development (TSQL or PLSQL, whatever dbms you have)
Security (Audits, user roles and permissions, patterns of transactions etc)
Reporting (Reports server, including automation of reports for management and staff)

As of my past experience Databases are most prone to DBAs than any thing; to err is human :). I don't believe if any of the db gurus never caught up in a disasterous situation of a messed up database, the quality that seperates them from ordinary db guys is, how confidently they react by keeping the nerves in control and resolving the problem in best possible way with minimum down time.
Many smart and compitent Tecchy folks turn to the DARK END; the so called MANAGEMENT. As it's said, when you get technically experienced and older in your field; your technical skills become secondary to your management skills. Your spoken and writting skills, dressing and attitude decides how far you'll go.
Or in other words; Degree of Management Skills is INVERSELY proportional to that of Technical Skills.:)


Cheers!