Skip Navigation Links
 

Microsoft - 70-765: Provisioning SQL Databases

Sample Questions

Question: 183
Measured Skill: Implement SQL in Azure (40–45%)

You have Microsoft SQL Server on a Microsoft Azure virtual machine. You have two Windows accounts named serviceAccount1 and ServiceAccount2. The SQL Server Agent runs as ServiceAccount1.

You need to run SQL Server Agent job steps by using ServiceAccount2.

Which cmdlet should you run first?

ASet-ADServiceAccount
B Set-SqlCredential
C New-ADServiceAccount
D New-SqlCredential

Correct answer: D

Explanation:

To run the SQL Server Agent job step in the context of a particular security principal, you must create a SQL Server Agent proxy. Saved credentials are required to create the proxy. These can be created by using the T-SQL CREATE CREDENTIAL statement or by using the PowerShell cmdlet New-SqlCredential.

See also: Create a SQL Server Agent Proxy

Question: 184
Measured Skill: Manage Storage (30–35%)

You have an on-premises Microsoft SQL server that has a database named DB1. DB1 contains several tables that are stretched to Microsoft Azure.

A network administrator upgrades the hardware firewalls on the network.

You need to verify whether data migration still runs successfully.

Which stored procedure should you run?

ASys_sp_testlinkedserver
B Sys_sp_rda_test_connection
C Sys_sp_rda_reauthorized_db
D Sp_set_firewall_rule

Correct answer: B

Explanation:

The Sys_sp_rda_test_connection stored procedur tests the connection from SQL Server to the remote Azure server and reports problems that may prevent data migration.

Reference: sys.sp_rda_test_connection (Transact-SQL)

Question: 185
Measured Skill: Manage databases and instances (30-35%)

You have a database named DB1 that contains a table named Table1. Tabe1 has a non-clustered index named index1.

You discover that index1 is corrupt. You need to repair index1.

Which statement should you execute?

ADBCC CHECKDB (‘db1’, REPAIR_FAST)
B ALTER INDEX index1 ON table1 REBUILD WITH ONLINE = ON
C ALTER INDEX index1 ON table1 REORGANIZE
D DBCC CHECKDB (‘db1’, DATA_PURITY)

Correct answer: B

Explanation:

ALTER INDEX modifies an existing table or view index (relational or XML) by disabling, rebuilding, or reorganizing the index; or by setting options on the index.

Rebuilding (REBUILD) an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.

Reorganizing (REORGANIZE) an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.

Note: DBCC CHECKDB REPAIR_FAST maintains syntax for backward compatibility only. No repair actions are performed.

Question: 186
Measured Skill: Manage databases and instances (30-35%)

You are a database administrator for your company. You are migrating two databases named DB1 and DB2 from an older SQL Server installation to a newly installed SQL Server 2016 instance.

After migration is completed, you identify the following issues:
  • Database DB1 must use two CPU cores.

  • Queries that were running on database DB2 prior to migration do not complete.
You need to configure the databases.

In the table shown in the exhibit, identify the parameter that must be configured for each database.

(Select one option for DB1, and one option for DB2. Select one option for each column.)

www.cert2brain.com

ADB1: QUERY_OPTIMIZER_HOTFIXES
DB2: CLEAR PROCEDURE_CACHE
B DB1: MAXDOP
DB2: PARAMETER_SNIFFING
C DB1: QUERY_OPTIMIZER_HOTFIXES
DB2: QUERY_OPTIMIZER_HOTFIXES
D DB1: MAXDOP
DB2: LEGACY_CARDINALITY_ESTIMATION

Correct answer: D

Explanation:

max degree of parallelism (MAXDOP)

When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. SQL Server considers parallel execution plans for queries, index data definition language (DDL) operations, parallel insert, online alter column, parallel stats collectiuon, and static and keyset-driven cursor population.

To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors. To suppress parallel plan generation, set max degree of parallelism to 1. Set the value to a number from 1 to 32,767 to specify the maximum number of processor cores that can be used by a single query execution.

Cardinality Estimation

The Cardinality Estimation predicts how many rows your query will likely return. The cardinality prediction is used by the Query Optimizer to generate the optimal query plan. With more accurate estimations, the Query Optimizer can usually do a better job of producing a more optimal query plan.

In 1998, a major update of the CE was part of SQL Server 7.0, for which the compatibility level was 70. 

Legacy CE: For a SQL Server database set at compatibility level 120 and above, the CE version 70 can be can be activated by using the at the database level by using the ALTER DATABASE SCOPED CONFIGURATION.



Question: 187
Measured Skill: Manage databases and instances (30-35%)

You have a Microsoft SQL Server that has a database named DB1. DB1 has a data files on drive E: and transaction logs on drive L:.

Drive L: fails and is replaced.

You need to recover DB1. The solution must minimize data loss.

Which three statements should you execute in sequence?

(To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

www.cert2brain.com

ASequence: 6, 3, 4
B Sequence: 1, 7, 4
C Sequence: 2, 3, 4
D Sequence: 1, 3, 4

Correct answer: A

Explanation:

Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance actions. If other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning.

The termination option WITH ROLLBACK IMMEDIATE will cause all incomplete transactions to be rolled back and any other connections to the DB1 database to be immediately disconnected.

The DBCC CHECKDB parameter REPAIR_REBUILD performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index. This argument does not repair errors involving FILESTREAM data.



 
Tags: exam, examcollection, exam simulation, exam questions, questions & answers, training course, study guide, vce, braindumps, practice test
 
 

© Copyright 2014 - 2019 by cert2brain.com