SQL Server DBA Q and A Page – 2

The following Question and Answers were added recently (on Dec 14th 2011)

41. How many Mirrored Servers can be setup in a Database Mirroring setup?

  • Only 1 Mirrored Server can be configured in a Database Mirroring setup.

42. Can there be a 3rd Server take part in Database Mirroring? 

  • Yes.
43. What is that Server called and what is the role of that Server?
  • The 3rd Server (which is optional) is called a Witness Server, its main role is for Automatic failover of the Prinicpal and Mirrored Servers.
The following Question and Answers were added recently (on Dec 24th 2011)
44. What are Dynamic Management Objects and how are they useful?
  • DMOs provide Server wide and Database level information on the SQL Server. DMOs include Dynamic Management Views and Dynamic Management Functions. Together, these DMOs provides internal information about the SQL Server with respect to Performance, I/O, Security, Indexes, Database Mirroring, Replication and many more. This information can be used to monitor the SQL Server and troubleshooting.
45. In what editions is Backup Compression available in SQL Server 2008 and 2008 R2?
  • in SQL Server 2008, Backup Compression is supported in Enterprise Edition only (apart from Developer and Eval Editions). In SQL Server 2008 R2, the feature is available in Datacenter Edition, Enterprise Edition and Standard Edition.
The following Question and Answers were added recently (on Jan 2nd 2012). The questions (46-48) have been submitted by Venkat M.
46. How will you know that data is copied to mirror Server in Database Mirroring?
  • We can monitor the status of the Principal Server and the Mirror Server using Database Mirroring Monitor from SSMS.
47. What is the difference between Table Scan and Index Scan?
  • A Table Scan is performed when the table has no Indexes OR the existing Index(es) are not beneficial to fulfill the request in the query. Generally, for a large table, Table Scans are slower. 
  • Index Scans are usually faster than Table Scans as the Index access reduces the I/O operations. 
48. List some of the Important DMV’s used in day to day work.
  • sys.dm_db_index_usage_stats
  • sys.dm_db_missing_index_details
  • sys.dm_exec_query_stats
  • sys.dm_os_wait_stats
  • sys.dm_tran_locks
49. What is  a Trace flag?
  • A Trace flags are used to change certain characteristics of the SQL Server. Trace flags can be used to switch off or enable certain feature, for troubleshooting purpose, or to diagnose performance issues such as deadlock monitoring. 
50. How do you enable Trace flag globally?
  • Trace flags can be set permanently using -T trace flag # as a start-up parameter or temporarily by running a command DBCC TRACEON (xxxx,-1).

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

- Bru Medishetty