SQL Server Practice Test 8

1. Which of the following syntax is valid?

SET SHOW_XML { ON | OFF }

SET SHOWPLAN_XML { ON | OFF }

SET SHOWPLAN { ON | OFF }

None of the mentioned

2. Setting the SORT_IN_TEMPDB option to ON can direct the SQL Server Database Engine to use ________ to store the intermediate sort results.

msdb

tempdb

master

model

3. _____________ wait type applies to SQL Server 2014 only.

HADR_WORK_POOL

HADR_XRF_STACK_ACCESS

HADR_WORK_QUEUE

LCK_M_U_ABORT_BLOCKERS

4. Which of the following scenario favours cached mode?

All of the mentioned

Continuous collection of data

Data collection and uploading of jobs on different schedules

Less frequent uploads

5. Which of the following wait occurs when a task is waiting for I/Os to finish?

AUDIT_LOGINCACHE_LOCK

ASYNC_NETWORK_IO

AUDIT_ON_DEMAND_TARGET_LOCK

AUDIT_XE_SESSION_MGR

6. The cleanup job is initiated by running the parameterless stored procedure is ___________

none of the mentioned

sp_cdc_cleanup_change_tables

sp_cdc_cleanup_change_job

sp_MScdc_cleanup_job

7. Point out the wrong statement.

Do not change any of the job specifications for the data collection and upload jobs

None of the mentioned

You should change the database name after creation

The Data Collection is performed primarily through SSIS packages that control the collection frequency on the target

8. Which of the following wait type is for internal use only?

AUDIT_LOGINCACHE_LOCK

ASYNC_NETWORK_IO

AUDIT_XE_SESSION_MGR

HADR_DBR_SUBSCRIBER_FILTER_LIST

9. Point out the correct statement.

The function cdc.fn_cdc_get_all_changes_<capture_instance> returns all changes that occurred for the specified interval

The update mask that is returned from a query function is a compact representation that identifies few columns that changed in a row of change data

A typical application scenario for querying for change data is to periodically request change data by using a receiver window bounded by timestamp values

None of the mentioned

10. The maximum size for all caches is a function of the ________ size.

RAM

None of the mentioned

Hard disk

Buffer pool