62 + Interview Questions in Monitoring and Auditing in SQL Server Page 1 InterviewSolution

1.	Which of the following is syntax for sp_add_collector_type procedure?(a) core.sp_add_collector [ @collector_type_uid = ] ‘collector_type_uid’(b) core.sp_add_collector_type [ @collector_type_uid = ].(c) core.sp_add_collector_type [ @collector_type_uid = ] ‘collector_type_uid’(d) none of the mentionedThis question was posed to me in a national level competition.I'm obligated to ask this question of Management Data Warehouse in division Monitoring and Auditing of SQL Server
Answer» CORRECT option is (C) core.sp_add_collector_type [ @collector_type_uid = ] ‘collector_type_uid’ Explanation: core.sp_add_collector_type adds a new entry to the core.supported_collector_types VIEW in the MANAGEMENT data warehouse database.

Discussion

2.	Which of the following table is used in the management data warehouse schema that is required for the Server Activity?(a) snapshots.query_stat(b) snapshots.os_latch_stats(c) snapshots.active_sessions(d) all of the mentionedThis question was posed to me in unit test.My question is taken from Management Data Warehouse in chapter Monitoring and Auditing of SQL Server
Answer» CORRECT option is (b) snapshots.os_latch_stats Explanation: snapshots.os_latch_stats is a SYSTEM level RESOURCE table.

Discussion

3.	____________ stores information about how the management data warehouse reports should group and aggregate performance counters.(a) core.snapshots_internal(b) core.supported_collector_types_internal(c) core.wait_categories(d) core.performance_counter_report_group_itemsI had been asked this question in an online interview.My doubt stems from Management Data Warehouse in chapter Monitoring and Auditing of SQL Server
Answer» RIGHT option is (d) core.performance_counter_report_group_items The explanation is: core.wait_categories contains the categories USED to group WAIT types according to wait_type CHARACTERISTIC.

Discussion

4.	Which of the following is the best Practice and Caveat for Management Data Warehouse?(a) Use a centralized server for the MDW database(b) The XML parameters for a single T-SQL collection item can have multiple elements(c) Use a distributed server for the MDW database(d) All of the mentionedThis question was posed to me in quiz.I'd like to ask this question from Management Data Warehouse topic in section Monitoring and Auditing of SQL Server
Answer» Right option is (a) USE a centralized SERVER for the MDW database Explanation: Centralized server ALLOWS you to use a SINGLE point for viewing reports for multiple instances.

Discussion

5.	Point out the wrong statement.(a) The Data Collection is performed primarily through SSIS packages that control the collection frequency on the target(b) You shouldchange the database name after creation(c) Do not change any of the job specifications for the data collection and upload jobs(d) None of the mentionedI have been asked this question by my college professor while I was bunking the class.I want to ask this question from Management Data Warehouse in chapter Monitoring and Auditing of SQL Server
Answer» CORRECT choice is (b) You shouldchange the DATABASE name after creation To ELABORATE: You should not change the database name after creation, because all of the JOBS created to manage the database COLLECTION refer to the database by the original name and will generate errors if the name is changed.

Discussion

6.	Which of the following mode allows for the collection and uploading of data to occur on demand?(a) Non-cached mode(b) Cached mode(c) Mixed mode(d) All of the mentionedThis question was posed to me in an interview for job.Question is taken from Management Data Warehouse in section Monitoring and Auditing of SQL Server
Answer» Correct OPTION is (a) Non-cached MODE Explanation: In non-cached mode, collection and UPLOAD are on the same schedule.

Discussion

7.	Which of the following scenario favours cached mode?(a) Continuous collection of data(b) Less frequent uploads(c) Data collection and uploading of jobs on different schedules(d) All of the mentionedI got this question in my homework.This intriguing question comes from Management Data Warehouse in portion Monitoring and Auditing of SQL Server
Answer» RIGHT answer is (d) All of the mentioned To EXPLAIN: Cached MODE USES separate schedules for COLLECTION and upload.

Discussion

8.	Point out the correct statement.(a) MDW consist of three components(b) SQL Server Express instances can be targets(c) Setting up the MDW is a one-step process(d) All of the mentionedThe question was posed to me in an interview for job.My doubt stems from Management Data Warehouse in portion Monitoring and Auditing of SQL Server
Answer» Right ANSWER is (a) MDW consist of THREE COMPONENTS The BEST I can explain: MDW consists of three components: Data Collector, MDW database and MDW REPORTS.

Discussion

9.	_________ introduces the Management Data Warehouse (MDW) to SQL Server Management Studio for streamlined performance troubleshooting.(a) SQL Server 2005(b) SQL Server 2008(c) SQL Server 2012(d) SQL Server 2014This question was posed to me in an international level competition.This intriguing question originated from Management Data Warehouse in chapter Monitoring and Auditing of SQL Server
Answer» Right answer is (B) SQL SERVER 2008 The best I can explain: MDW is a set of components that ENABLE a database developer or ADMINISTRATOR to quickly track down problems that could be causing performance degradation.

Discussion

10.	Which of the following query returns the average throughput for the most recent sessions?(a) SELECT command_count*duration AS [Throughput] FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0(b) SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0(c) SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sesss WHERE session_id = 0(d) None of the mentionedI had been asked this question during an interview.This intriguing question comes from SQL Audit in portion Monitoring and Auditing of SQL Server
Answer» The correct option is (B) SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0 Best EXPLANATION: To determine the throughput of a session, divide the value in the command_count COLUMN by the value in the duration column.

Discussion

11.	Which of the following field in cdc.lsn_time_mapping has varbinary data type?(a) tran_begin_time(b) tran_id(c) start_lsn(d) all of the mentionedI have been asked this question in a job interview.My enquiry is from SQL Audit in division Monitoring and Auditing of SQL Server
Answer» CORRECT answer is (b) tran_id To ELABORATE: tran_id denotes ID of the TRANSACTION.

Discussion

12.	__________gives DBAs an option to shut down the server in case of an audit log failure.(a) Shut down server on partial failure(b) Shut down server on audit log failure(c) Shut down server on complete failure(d) None of the mentionedThe question was asked in my homework.This question is from SQL Audit in chapter Monitoring and Auditing of SQL Server
Answer» Right OPTION is (b) Shut down server on AUDIT log failure Best EXPLANATION: QUEUE delay (in milliseconds) defines the time interval after which the EVENTS will be written in the logs.

Discussion

13.	Which of the following query returns the average latency for the most recent sessions?(a) SELECT latency FROM sys.dm_cdc_log_audit_sessions WHERE session_id = 0(b) SELECT latency FROM sys.dm_cdc_log_scan_ WHERE session_id = 0(c) SELECT latency FROM sys.dm_cdc_audit_scan_sessions WHERE session_id = 0(d) SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0This question was posed to me during an online exam.Question is taken from SQL Audit in portion Monitoring and Auditing of SQL Server
Answer» Right answer is (d) SELECT LATENCY FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0 For EXPLANATION I would SAY: You can use latency data to determine how fast or SLOW the CAPTURE process is processing transactions.

Discussion

14.	Which of the following error is returned for a query for all changes when a parameter that is used to define the query interval is not valid?(a) An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_partial_changes_(b) An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_(c) An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_(d) None of the mentionedThis question was posed to me in an interview for job.Enquiry is from SQL Audit topic in division Monitoring and Auditing of SQL Server
Answer» Right option is (b) An INSUFFICIENT number of arguments were supplied for the PROCEDURE or FUNCTION cdc.fn_cdc_get_all_changes_ The BEST I can explain: Sometimes even the error MESSAGE arises when the row filter option is invalid.

Discussion

15.	Point out the wrong statement.(a) cdc.lsn_time_mapping applies to SQL Server 2014(b) Datetime wrappers are owned by the user, and not are created in the default schema of the caller(c) Change data capture supports up to two capture instances for a single tracked source table(d) The name of the function to wrap the all changes query is fn_all_changes_ followed by the capture instance name.I have been asked this question in an online quiz.My doubt is from SQL Audit topic in portion Monitoring and Auditing of SQL Server
Answer» The correct ANSWER is (a) cdc.lsn_time_mapping APPLIES to SQL Server 2014 For EXPLANATION: cdc.lsn_time_mapping applies to SQL Server (SQL Server 2008 through current VERSION).

Discussion

16.	Upper bound on expected throughput of the capture job is calculated using which of the following computation?(a) (maxtrans * maxscans) / number of seconds between scans(b) (maxtrans * maxscans) * number of seconds between scans(c) (maxtrans * maxscans) – number of seconds between scans(d) None of the mentionedThis question was addressed to me during an online exam.This intriguing question originated from Change Data Capture in division Monitoring and Auditing of SQL Server
Answer» The correct CHOICE is (a) (maxtrans * maxscans) / number of seconds between scans Easy explanation: When RUNNING in one shot mode, you can COMPUTE an UPPER bound on expected THROUGHPUT of the capture job

Discussion

17.	Which of the following is a catalog view used for change tracking?(a) sys.change_tracking_tables(b) sys.change_tracking_views(c) sys.change_tracking_columns(d) none of the mentionedI got this question during a job interview.This interesting question is from Change Tracking topic in chapter Monitoring and Auditing of SQL Server
Answer» RIGHT ANSWER is (a) sys.change_tracking_tables Best EXPLANATION: sys.change_tracking_tables RETURNS one row for each database in the instance of SQL Server that has change TRACKING enabled.

Discussion

18.	_____________ obtains tracking information for all changes to a table that have occurred since a specific version.(a) CHANGETABLE_CONTEXT(b) CHANGETABLE(c) CHANGE_TRACKING_IS_COLUMN_IN_MASK(d) All of the mentionedThe question was asked during a job interview.Asked question is from Change Tracking in portion Monitoring and Auditing of SQL Server
Answer» RIGHT choice is (b) CHANGETABLE Easy explanation: CHANGETABLE OBTAINS LATEST CHANGE tracking information for a specified row.

Discussion

19.	Which of the following is a change tracking function?(a) WITH CHANGE_TRACK_CONTEXT(b) CHANGE_TRACKING_MAX_VALID_VERSION()(c) CHANGE_TRACKING_CURRENT_VERSION(d) All of the mentionedI have been asked this question in examination.Asked question is from Change Tracking in section Monitoring and Auditing of SQL Server
Answer» The CORRECT OPTION is (C) CHANGE_TRACKING_CURRENT_VERSION Explanation: You can use this version the next time you enumerate changes by using CHANGETABLE.

Discussion

20.	Which of the following pair of views has one to many relationship?(a) sys.server_event_session_actions.event_session_id, sys.sys.server_event_sessions.event(b) sys.server_event_session_actions, sys.sys.server_event_sessions.event_session_id(c) sys.server_event_session_actions.event_session_id,sys.sys.server_event_sessions.event _session_id(d) none of the mentionedThis question was addressed to me during an internship interview.This is a very interesting question from Extended Events in section Monitoring and Auditing of SQL Server
Answer» The CORRECT choice is (c) sys.server_event_session_actions.event_session_id,sys.sys.server_event_sessions.EVENT _session_id Best explanation: Almost all the extended event VIEWS has one to MANY cardinality.

Discussion

21.	_________ returns a row for each customizable column that was explicitly set on events and targets.(a) sys.server_event_session_targets(b) sys.server_event_session_fields(c) Returns a row for each event in an event session(d) All of the mentionedI have been asked this question in examination.I would like to ask this question from Extended Events in portion Monitoring and Auditing of SQL Server
Answer» Right option is (b) sys.server_event_session_fields Best EXPLANATION: sys.server_event_session_fields requires VIEW SERVER STATE PERMISSION on the server.

Discussion

22.	Which of the following catalog view is used for SQL Server Extended Events?(a) sys.server_sessions(b) sys.server_event_sess(c) sys.server_event_session_actions(d) all of the mentionedI have been asked this question by my school principal while I was bunking the class.This is a very interesting question from Extended Events in division Monitoring and Auditing of SQL Server
Answer» The correct option is (c) sys.server_event_session_actions To EXPLAIN I would say: sys.server_event_session_actions returns a ROW for each ACTION on each EVENT of an event session.

Discussion

23.	_________ can be applied to the metadata or to an active session and the metadata.(a) DROP EVENT SESSION(b) ALTER EVENT SESSION,STATE=STOP(c) ALTER EVENT SESSION,STATE=START(d) All of the mentionedThis question was addressed to me in an interview for job.My question is based upon Extended Events in section Monitoring and Auditing of SQL Server
Answer» The CORRECT answer is (d) All of the mentioned The explanation: Both ALTER EVENT SESSION and DROP EVENT SESSION can be applied to the METADATA or to an active session and the metadata.

Discussion

24.	Which of the DDL statement creates a session object that contains the metadata?(a) CREATE EVENT SESSION(b) CREATE SESSION(c) CREATE EVENT SESSION METADATA(d) None of the mentionedI had been asked this question at a job interview.My question is from Extended Events topic in section Monitoring and Auditing of SQL Server
Answer» CORRECT OPTION is (a) CREATE EVENT SESSION To EXPLAIN I would say: CREATE EVENT SESSION CREATES an EXTENDED Events session that identifies the source of the events, the event session targets, and the event session options.

Discussion

25.	Point out the wrong statement.(a) Actions and predicates are bound to events on a per-session basis(b) Extended Event sessions have implied boundaries in that the configuration of one session does change the configuration of another session(c) The mapping between package objects and sessions is many to many(d) None of the mentionedThis question was posed to me during an interview.My enquiry is from Extended Events topic in division Monitoring and Auditing of SQL Server
Answer» Right option is (b) Extended EVENT sessions have IMPLIED boundaries in that the CONFIGURATION of one session does change the configuration of another session Easiest explanation: Boundaries do not PREVENT an event or target from being used in more than one session.

Discussion

26.	SQL Server Extended Events Packages consist of _____________(a) Events(b) Predicates(c) Types(d) All of the mentionedThe question was posed to me in an interview.This key question is from Extended Events topic in chapter Monitoring and Auditing of SQL Server
Answer» The correct option is (d) All of the mentioned The explanation is: Packages are identified by a NAME, a GUID, and the BINARY module that contains the package.

Discussion

27.	Point out the correct statement.(a) The SQL Server Extended Events engine is a collection of services and objects(b) Buffers are attached to sessions to handle buffering and dispatch, and causality tracking(c) Policy tracking provides the ability to track work across multiple tasks(d) None of the mentionedThe question was asked in exam.The origin of the question is Extended Events topic in chapter Monitoring and Auditing of SQL Server
Answer» Right CHOICE is (a) The SQL Server Extended Events engine is a collection of SERVICES and objects For EXPLANATION: The Extended Events engine itself does not provide any events or actions to take when an EVENT fires.

Discussion

28.	Which of the following wait type is for internal use only?(a) ASYNC_NETWORK_IO(b) AUDIT_LOGINCACHE_LOCK(c) HADR_DBR_SUBSCRIBER_FILTER_LIST(d) AUDIT_XE_SESSION_MGRThis question was posed to me in examination.The question is from Wait States in section Monitoring and Auditing of SQL Server
Answer» The correct answer is (c) HADR_DBR_SUBSCRIBER_FILTER_LIST The best EXPLANATION: The PUBLISHER for an availability replica event (such as a state change or CONFIGURATION change) is waiting for EXCLUSIVE read/write access to the list of event subscribers that correspond to availability databases.

Discussion

29.	Which of the following is an extended event task?(a) create an Extended Events session(b) alter an Extended Events session(c) find out what events are available in the registered packages(d) all of the mentionedI got this question during an interview.Asked question is from Extended Events topic in portion Monitoring and Auditing of SQL Server
Answer» RIGHT option is (d) all of the mentioned For EXPLANATION: USING Management Studio or Transact-SQL to execute Transact-SQL Data Definition Language statements, dynamic management views and functions, or catalog views, you can create SIMPLE or complex SQL SERVER Extended Events troubleshooting solutions for your SQL Server environment.

Discussion

30.	When does BROKER_TO_FLUSH wait type event occur?(a) When the Service Broker queue task handler tries to shut down the task(b) When the Service Broker transmitter is waiting for work(c) When the Service Broker lazy flusher flushes the in-memory transmission objects to a work table(d) All of the mentionedThe question was posed to me during an interview.I would like to ask this question from Wait States in portion Monitoring and Auditing of SQL Server
Answer» Correct answer is (c) When the Service Broker LAZY flusher FLUSHES the in-memory transmission OBJECTS to a work table The best I can EXPLAIN: BROKER_SHUTDOWN tries to SHUT down the task.

Discussion

31.	Which of the following wait occurs when a task is waiting for I/Os to finish?(a) ASYNC_NETWORK_IO(b) AUDIT_LOGINCACHE_LOCK(c) AUDIT_ON_DEMAND_TARGET_LOCK(d) AUDIT_XE_SESSION_MGRThe question was posed to me in an interview.This is a very interesting question from Wait States topic in portion Monitoring and Auditing of SQL Server
Answer» Right CHOICE is (a) ASYNC_NETWORK_IO The best I can EXPLAIN: AUDIT_XE_SESSION_MGR occurs when there is a wait on a lock that is USED to SYNCHRONIZE the starting and stopping of audit related EXTENDED Events sessions.

Discussion

32.	______ waits occur when a worker requests access to a resource that is not available because the resource is being used by some other worker or is not yet available.(a) Resource(b) Queue(c) Lock(d) ExternalI got this question during an interview.This interesting question is from Wait States in division Monitoring and Auditing of SQL Server
Answer» Correct OPTION is (a) RESOURCE Easy EXPLANATION: Examples of resource WAITS are locks, latches, network and DISK I/O waits. Lock and latch waits are waits on synchronization objects.

Discussion

33.	The contents of sys.dm_os_wait_stats can be reset by running which of the following command?(a) DBCC PERF (‘sys.dm_os_wait_stats’, CLEAR);(b) DBCC SQLP (‘sys.dm_os_wait_stats’, CLEAR);(c) DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR);(d) None of the mentionedThe question was posed to me in an interview for internship.Query is from Wait States in section Monitoring and Auditing of SQL Server
Answer» Right option is (C) DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR); EXPLANATION: DBCC SQLPERFcommand resets all COUNTERS to 0.

Discussion

34.	Point out the wrong statement.(a) LCK_M_XX wait is often a symptom of blocking(b) PAGELATCH_XX wait occurs when SQL Server is attempting to latch a page in memory(c) Few variations of PAGEIOLATCH waits usually indicate a problem with the disk subsystem(d) All of the mentionedI had been asked this question in final exam.My question comes from Wait States in division Monitoring and Auditing of SQL Server
Answer» The CORRECT answer is (c) Few variations of PAGEIOLATCH waits usually indicate a PROBLEM with the DISK SUBSYSTEM The explanation: All variations of PAGEIOLATCH waits usually indicate a problem with the disk subsystem.

Discussion

35.	How many types of wait occur in SQL Server?(a) 2(b) 3(c) 4(d) 5This question was addressed to me during an interview.I'm obligated to ask this question of Wait States in chapter Monitoring and Auditing of SQL Server
Answer» The CORRECT choice is (b) 3 Easiest explanation: SQL Server mainly SUPPORTS THREE types of waits: Resource waits, Queue waits and External waits

Discussion

36.	Point out the correct statement.(a) 510: CXPACKET indicates parallelism(b) SQL Server does not always waits for something, a disk io, a place in the processor queue, a lock(c) Few variations of PAGEIOLATCH waits usually indicate a problem with the disk subsystem(d) All of the mentionedThe question was asked during an online exam.My question is taken from Wait States topic in portion Monitoring and Auditing of SQL Server
Answer» CORRECT CHOICE is (a) 510: CXPACKET indicates parallelism For EXPLANATION: The coordinator thread in a parallel query always ACCUMULATES these waits.

Discussion

37.	Which of the following requires VIEW SERVER STATE permission on the server?(a) sys.dm_os_states(b) sys.dm_os_wait_stats(c) sys.dm_os_wait_states(d) none of the mentionedThis question was addressed to me at a job interview.I would like to ask this question from Wait States in section Monitoring and Auditing of SQL Server
Answer» The CORRECT option is (b) sys.dm_os_wait_stats For explanation I would say: sys.dm_os_wait_stats RETURNS INFORMATION about all the waits encountered by threads that EXECUTED.

Discussion

38.	Which of the following event has event number 15 in the trace?(a) DTCTransaction(b) ErrorLog(c) SQL:BatchStarting(d) Lock:CancelThe question was posed to me during an interview for a job.My question is based upon Profiling topic in division Monitoring and Auditing of SQL Server
Answer» Right CHOICE is (c) SQL:BatchStarting To elaborate: SQL:BatchStarting OCCURS when a Transact-SQL BATCH has started.

Discussion

39.	TRACE_PRODUCE_BLACKBOX file can be saved in the following location?(a) N’%SQLDIR%\MSSQL\DATA\blackbox.trc(b) N’%SQLDIR%\MSSQL\DATA\black.trc(c) N’%SQLDIR%\MSSQL\DATA\box.trc(d) None of the mentionedThe question was asked in an international level competition.My doubt stems from Profiling topic in section Monitoring and Auditing of SQL Server
Answer» Right option is (a) N’%SQLDIR%\MSSQL\DATA\blackbox.trc To explain: The DEFAULT file_count is 2 but can be OVERRIDDEN by the USER using filecount option.

Discussion

40.	Which of the following argument has option value 2 in sp_trace_create procedure?(a) TRACE_PRODUCE_BLACKBOX(b) SHUTDOWN_ON_ERROR(c) TRACE_FILE_ROLLOVER(d) All of the mentionedThe question was asked in quiz.My enquiry is from Profiling topic in section Monitoring and Auditing of SQL Server
Answer» The correct option is (c) TRACE_FILE_ROLLOVER The best EXPLANATION: As more rollover TRACE files are created, the integer VALUE appended to the file name INCREASES sequentially.

Discussion

41.	Which of the following events and their columns are contained in the trace?(a) Attention(b) Batch starting(c) Exception(d) All of the mentionedThe question was posed to me in homework.I would like to ask this question from Profiling topic in section Monitoring and Auditing of SQL Server
Answer» Correct ANSWER is (d) All of the mentioned Easiest EXPLANATION: EVENTS or columns cannot be added or REMOVED from this TRACE.

Discussion

42.	Which of the following is an event class?(a) SQL:BatchCompleted(b) Audit Password(c) Lock:Blocked(d) All of the mentionedThe question was posed to me in an internship interview.Enquiry is from Profiling in section Monitoring and Auditing of SQL Server
Answer» Correct option is (a) SQL:BatchCompleted To explain: The EVENT CLASS contains all of the DATA that can be REPORTED by an event.

Discussion

43.	Point out the correct statement.(a) SQL Server Profiler also supports auditing the actions performed on instances of SQL Server(b) A profiler class is a type of event that can be traced(c) A profile category defines the way events are grouped within SQL Server Profiler(d) None of the mentionedI had been asked this question in an interview for job.I need to ask this question from Profiling topic in chapter Monitoring and Auditing of SQL Server
Answer» Right option is (a) SQL Server PROFILER ALSO SUPPORTS auditing the actions performed on instances of SQL Server Explanation: SQL Server Profiler is a rich interface to create and manage traces and analyze and REPLAY trace results.

Discussion

44.	Which of the following determines type or resource to which the lock applies?(a) sp_lock(b) sp_who(c) sp_monitor(d) sp_spaceusedI got this question in an interview for internship.I want to ask this question from Performance Monitoring topic in division Monitoring and Auditing of SQL Server
Answer» The CORRECT answer is (a) sp_lock Explanation: sp_lock reports information about locks.use the sys.dm_tran_locks DYNAMIC MANAGEMENT VIEW in future versions.

Discussion

45.	Which of the following is the benefit of SQL Server Profiler?(a) Capturing the series of Transact-SQL statements that lead to a problem(b) Finding and diagnosing slow-running queries(c) Correlating performance counters to diagnose problems(d) All of the mentionedThis question was addressed to me in an internship interview.This intriguing question originated from Profiling in section Monitoring and Auditing of SQL Server
Answer» Correct choice is (d) All of the mentioned Best explanation: MICROSOFT SQL SERVER Profiler is a graphical user INTERFACE to SQL Trace for monitoring an INSTANCE of the Database Engine or Analysis SERVICES.

Discussion

46.	_____________ displays an estimate of the current amount of disk space used by a table.(a) sp_lock(b) sp_who(c) sp_monitor(d) sp_spaceusedThe question was asked in a national level competition.This intriguing question originated from Performance Monitoring in chapter Monitoring and Auditing of SQL Server
Answer» The correct answer is (d) sp_spaceused For explanation: sp_spaceused DISPLAYS the NUMBER of rows, disk space RESERVED, and disk space used by a table, indexed view, or Service BROKER QUEUE in the current database, or displays the disk space reserved and used by the whole database.

Discussion

47.	Which of the following stored procedure reports snapshot information about current SQL Server user?(a) sp_lock(b) sp_who(c) sp_monitor(d) sp_userThis question was addressed to me in an interview for internship.This intriguing question comes from Performance Monitoring in chapter Monitoring and Auditing of SQL Server
Answer» Correct option is (B) sp_who The best I can explain: sp_who reports SNAPSHOT information about current SQL Server users and processes, INCLUDING the currently executing statement and whether the statement is BLOCKED.

Discussion

48.	Point out the correct statement.(a) Slow SELECT queries with high physical reads and low queue lengths demonstrates under performance of Disk read and write(b) If many simultaneous requests are waiting, investigate the process which is taking less time(c) SQL Server 2008 R2 Data Collection provides information about SQL Server processes and how these processes affect the current instance of SQL Server(d) All of the mentionedI had been asked this question in an interview.Origin of the question is Performance Monitoring in portion Monitoring and Auditing of SQL Server
Answer» Right option is (a) Slow SELECT queries with high physical reads and low QUEUE LENGTHS demonstrates under PERFORMANCE of Disk read and write Explanation: Index optimization can REDUCE this problem.

Discussion

49.	_________ reports the total processor time with respect to the available capacity of the server.(a) Disk Read Bytes/sec(b) Avg.Disk Queue Length(c) Processor time(d) Response timeI have been asked this question in examination.My query is from Performance Monitoring topic in division Monitoring and Auditing of SQL Server
Answer» Correct answer is (c) Processor TIME Easy explanation: If counter is between 50 to 70 % CONSISTENTLY, investigate the process which is taking long time.

Discussion

50.	Which of the following is one of the most crucial performance counters for monitoring?(a) Disk Write Bytes/sec(b) Memory Queue Length(c) Virtual Memory(d) All of the mentionedThe question was asked in exam.Enquiry is from Performance Monitoring topic in section Monitoring and Auditing of SQL Server
Answer» The correct ANSWER is (a) Disk WRITE Bytes/sec To ELABORATE: Disk Write Bytes/sec report the number of bytes written to the disk.

Discussion

Explore topic-wise InterviewSolutions in .

Which of the following field in cdc.lsn_time_mapping has varbinary data type?(a) tran_begin_time(b) tran_id(c) start_lsn(d) all of the mentionedI have been asked this question in a job interview.My enquiry is from SQL Audit in division Monitoring and Auditing of SQL Server

SQL Server Extended Events Packages consist of _____________(a) Events(b) Predicates(c) Types(d) All of the mentionedThe question was posed to me in an interview.This key question is from Extended Events topic in chapter Monitoring and Auditing of SQL Server

Which of the following wait type is for internal use only?(a) ASYNC_NETWORK_IO(b) AUDIT_LOGINCACHE_LOCK(c) HADR_DBR_SUBSCRIBER_FILTER_LIST(d) AUDIT_XE_SESSION_MGRThis question was posed to me in examination.The question is from Wait States in section Monitoring and Auditing of SQL Server

How many types of wait occur in SQL Server?(a) 2(b) 3(c) 4(d) 5This question was addressed to me during an interview.I'm obligated to ask this question of Wait States in chapter Monitoring and Auditing of SQL Server

Which of the following event has event number 15 in the trace?(a) DTCTransaction(b) ErrorLog(c) SQL:BatchStarting(d) Lock:CancelThe question was posed to me during an interview for a job.My question is based upon Profiling topic in division Monitoring and Auditing of SQL Server

Which of the following argument has option value 2 in sp_trace_create procedure?(a) TRACE_PRODUCE_BLACKBOX(b) SHUTDOWN_ON_ERROR(c) TRACE_FILE_ROLLOVER(d) All of the mentionedThe question was asked in quiz.My enquiry is from Profiling topic in section Monitoring and Auditing of SQL Server

Which of the following events and their columns are contained in the trace?(a) Attention(b) Batch starting(c) Exception(d) All of the mentionedThe question was posed to me in homework.I would like to ask this question from Profiling topic in section Monitoring and Auditing of SQL Server

Which of the following is an event class?(a) SQL:BatchCompleted(b) Audit Password(c) Lock:Blocked(d) All of the mentionedThe question was posed to me in an internship interview.Enquiry is from Profiling in section Monitoring and Auditing of SQL Server

Which of the following determines type or resource to which the lock applies?(a) sp_lock(b) sp_who(c) sp_monitor(d) sp_spaceusedI got this question in an interview for internship.I want to ask this question from Performance Monitoring topic in division Monitoring and Auditing of SQL Server

Which of the following is one of the most crucial performance counters for monitoring?(a) Disk Write Bytes/sec(b) Memory Queue Length(c) Virtual Memory(d) All of the mentionedThe question was asked in exam.Enquiry is from Performance Monitoring topic in section Monitoring and Auditing of SQL Server