Mobile Menu

[Free] 2019(Nov) EnsurePass Microsoft 70-764 Dumps with VCE and PDF 111-120

by admin, November 6, 2019

Get Full Version of the Exam
http://www.EnsurePass.com/70-764.html

Question No.111

You manage a SQL Server 2014 instance that contains a database named DB1. Users report that some queries to DB1 take longer than expected.

Although most queries run in less than one second, some queries take up to 20 seconds to run. You need to view all of the performance statistics for each database file.

Which method should you use?

  1. Query the sys.dm_os_tasks dynamic management view.

  2. Query the sys.dm_os_performance_counters dynamic management view.

  3. Query the sys.dm_io_virtual_file_stats dynamic management function.

  4. Examine the Data File I/O pane in Activity Monitor.

Correct Answer: C

Explanation:

sys.dm_io_virtual_file_stats Returns I/O statistics for data and log files.

Question No.112

Overview

General Overview

ADatum Corporation has offices in Miami and Montreal.

The network contains a single Active Directory forest named adatum.com. The offices connect to each other by using a WAN link that has 5-ms latency.

A. Datum standardizes its database platform by using SQL Server 2014 Enterprise edition.

Databases

Each office contains databases named Sales, Inventory, Customers, Products, Personnel, and Dev.

Servers and databases are managed by a team of database administrators. Currently, all of the database administrators have the same level of permissions on all of the servers and all of the databases.

The Customers database contains two tables named Customers and Classifications.

The following graphic shows the relevant portions of the tables:

image

The following table shows the current data in the Classifications table:

image

The Inventory database is updated frequently. The database is often used for reporting.

A full backup of the database currently takes three hours to complete.

Stored Procedures

A stored procedure named USP_1 generates millions of rows of data for multiple reports. USP_1 combines data from five different tables from the Sales and Customers databases in a table named Table1.

After Table1 is created, the reporting process reads data from Table1 sequentially several times. After the process is complete, Table1 is deleted.

A stored procedure named USP_2 is used to generate a product list. The product list contains the names of products grouped by category.

USP_2 takes several minutes to run due to locks on the tables the procedure accesses. The locks are caused by USP_1 and USP_3.

A stored procedure named USP_3 is used to update prices. USP_3 is composed of several UPDATE statements called in sequence from within a transaction.

Currently, if one of the UPDATE statements fails, the stored procedure fails. A stored procedure named USP_4 calls stored procedures in the Sales, Customers, and Inventory databases.

The nested stored procedures read tables from the Sales, Customers, and Inventory databases. USP_4 uses an EXECUTE AS clause.

All nested stored procedures handle errors by using structured exception handling. A stored procedure named USP_5 calls several stored procedures in the same database. Security checks are performed each time USP_5 calls a stored procedure.

You suspect that the security checks are slowing down the performance of USP_5. All stored procedures accessed by user applications call nested stored procedures.

The nested stored procedures are never called directly.

Design Requirements Data Recovery

You must be able to recover data from the Inventory database if a storage failure occurs. You have a Recovery Time Objective (RTO) of 5 minutes.

You must be able to recover data from the Dev database if data is lost accidentally. You have a Recovery Point Objective (RPO) of one day.

Classification Changes

You plan to change the way customers are classified. The new classifications will have four levels based on the number of orders. Classifications may be removed or added in the future.

Management requests that historical data be maintained for the previous classifications. Security A group of junior database administrators must be able to manage security for the Sales database. The junior database administrators will not have any other administrative rights.

A.Datum wants to track which users run each stored procedure.

Storage

ADatum has limited storage. Whenever possible, all storage space should be minimized for all databases and all backups.

Error Handling

There is currently no error handling code in any stored procedure.

You plan to log errors in called stored procedures and nested stored procedures. Nested stored procedures are never called directly.

You need to recommend a change to USP_3 to ensure that the procedure completes only if all of the UPDATE statements complete. Which change should you recommend?

  1. Set the XACT_ABORT option to off

  2. Set the XACT_ABORT option to on.

  3. Set the IMPLICIT_TRANSACTIONS option to off.

  4. Set the IMPLICIT_TRANSACTIONS option to on.

Correct Answer: B Explanation: Scenario:

A stored procedure named USP_3 is used to update prices. USP_3 is composed of several UPDATE statements called in sequence from within a transaction.

Currently, if one of the UPDATE statements fails, the stored procedure continues to execute.

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

Question No.113

Overview

You are a database administrator for a company named Litware, Inc.

Litware is a book publishing house. Litware has a main office and a branch office.

You are designing the database infrastructure to support a new web-based application that is

being developed.

The web application will be accessed at www.litwareinc.com. Both internal employees and external partners will use the application.

You have an existing desktop application that uses a SQL Server 2008 database named App1_DB.

App1_DB will remain in production.

Requirements Planned Changes

You plan to deploy a SQL Server 2014 instance that will contain two databases named Database1 and Database2.

All database files will be stored in a highly available SAN. Database1 will contain two tables named Orders and OrderDetails.

Database1 will also contain a stored procedure named usp_UpdateOrderDetails.

The stored procedure is used to update order information. The stored procedure queries the Orders table twice each time the procedure executes.

The rows returned from the first query must be returned on the second query unchanged along with any rows added to the table between the two read operations.

Database1 will contain several queries that access data in the Database2 tables. Database2 will contain a table named Inventory.

Inventory will contain over 100 GB of data.

The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index.

The column that is used as the primary key will use the identity property.

Database2 wilt contains a stored procedure named usp_UpdateInventory. usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies. All data in Database2 is recreated each day ad does not change until the next data creation process. Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named Appl_Dbl as soon as changes occur to the data in Database2. Litware plans to use offsite storage for all SQL Server 2014 backups.

Business Requirements

You have the following requirements:

image

Costs for new licenses must be minimized.

image

image

Private information that is accessed by Application must be stored in a secure format. Development effort must be minimized whenever possible.

image

The storage requirements for databases must be minimized.

image

image

System administrators must be able to run real-time reports on disk usage. The databases must be available if the SQL Server service fails.

image

Database administrators must receive a detailed report that contains allocation errors and data corruption.

image

Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables.

You must encrypt the backup files to meet regulatory compliance requirements.

The encryption strategy must minimize changes to the databases and to the applications. You need to recommend an isolation level for usp_UpdateOrderDetails.

Which isolation level should recommend?

  1. Read committed

  2. Repeatable read

  3. Read uncommitted

  4. Serializable

Correct Answer: B

Explanation:

Scenario: Databasel will also contain a stored procedure named usp_UpdateOrderDetails. The stored procedure is used to update order information. The stored procedure queries the Orders table twice each time the procedure executes. The rows returned from the first query must be returned on the second query unchanged along with any rows added to the table between the two read operations.

REPEATABLE READ Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

Question No.114

DRAG DROP

You plan to deploy a database to SQL Azure. You are designing two stored procedures named USP_1 and USP_2 that have the following requirements:

image

image

Prevent data read by USP_1 from being modified by other active processes. Allow USP_2 to perform dirty reads.

You need to recommend the isolation level for the stored procedures. The solution must maximize concurrency.

Which isolation levels should you recommend?

To answer, drag the appropriate isolation level to the correct stored procedure in the answer area.

image

Correct Answer:

image

Question No.115

You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2016.

You attempt to add the [Corpnet\User1] login to the database. However, you receive the following error message:

quot;User already exists in current database.quot;

You need to configure the [Corpnet\User1] login to be able to access the Orders database and retain the original permissions.

You need to achieve this goal by using the minimum required permissions. Which Transact-SQL statement should you use?

  1. DROP USER [User1]; CREATE USER [Corpnet\User1] FOR LOGIN [Corpnet\User1]; ALTER ROLE [db_owner] ADD MEM3ER [Corpnet\User1];

  2. ALTER SERVER RCLS Isysadmin] ADD MEMBER [Corpnet\User1];

  3. ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1];

  4. ALTER ROLE [db owner] ADD MEMBBR [Corpnet\User1];

Correct Answer: C

Question No.116

You are troubleshooting an application that runs a query. The application frequently causes deadlocks. You need to identify which transaction causes the deadlock. What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

  1. Query the sys.dm_exec_requests dynamic management view.

  2. Create a trace in SQL Server Profiler that contains the Deadlock graph event.

  3. Query the sys.dm_exec_sessions dynamic management view.

  4. Create an extended events session to capture deadlock information.

Correct Answer: D Explanation: Troubleshooting deadlocks

You have been receiving reports from users indicating that certain applications are returning deadlock errors. To maximize the effectiveness of troubleshooting these problems, you decide to focus on the deadlocks that are hit most frequently. You create an Extended Events session that:

Configures deadlock event tracking for the session.

Specifies a target that aggregates based on an identifier for the deadlock.

You run the Extended Events session, and after additional deadlocks are reported you are able to obtain aggregated deadlock information, along with the complete XML deadlock graph for each source. Using this information, you are able to pin point the most common deadlocks and start working on a solution.

Question No.117

You have a SQL Server 2012 database named DB1.

You plan to import a large number of records from a SQL Azure database to DB1.

You need to recommend a solution to minimize the amount of space used in the transaction log during the import operation.

What should you include in the recommendation?

  1. a new log file

  2. a new filegroup

  3. the full recovery model

  4. a new partitioned table

  5. the bulk-logged recovery model

Correct Answer: E

Explanation:

Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions. The bulk-logged recovery model protects against media failure and, for bulk operations, provides the best performance and least log space usage.

Note:

The bulk-logged recovery model is a special-purpose recovery model that should be used only intermittently to improve the performance of certain large-scale bulk operations, such as bulk imports of large amounts of data.

Recovery Models (SQL Server)

Question No.118

Overview

You are a database administrator for a company named Litware, Inc.

Litware is a book publishing house. Litware has a main office and a branch office.

You are designing the database infrastructure to support a new web-based application that is being developed.

The web application will be accessed at www.litwareinc.com. Both internal employees and external partners will use the application.

You have an existing desktop application that uses a SQL Server 2008 database named App1_DB.

App1_DB will remain in production.

Requirements Planned Changes

You plan to deploy a SQL Server 2014 instance that will contain two databases named Database1 and Database2.

All database files will be stored in a highly available SAN. Database1 will contain two tables named Orders and OrderDetails.

Database1 will also contain a stored procedure named usp_UpdateOrderDetails.

The stored procedure is used to update order information. The stored procedure queries the Orders table twice each time the procedure executes.

The rows returned from the first query must be returned on the second query unchanged along with any rows added to the table between the two read operations.

Database1 will contain several queries that access data in the Database2 tables. Database2 will contain a table named Inventory.

Inventory will contain over 100 GB of data.

The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index.

The column that is used as the primary key will use the identity property.

Database2 wilt contains a stored procedure named usp_UpdateInventory. usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies. All data in Database2 is recreated each day ad does not change until the next data creation process. Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named Appl_Dbl as soon as changes occur to the data in Database2. Litware plans to use offsite storage for all SQL Server 2014 backups.

Business Requirements

You have the following requirements:

image

Costs for new licenses must be minimized.

image

image

Private information that is accessed by Application must be stored in a secure format. Development effort must be minimized whenever possible.

image

The storage requirements for databases must be minimized.

image

image

System administrators must be able to run real-time reports on disk usage. The databases must be available if the SQL Server service fails.

image

Database administrators must receive a detailed report that contains allocation errors and data corruption.

image

Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables.

You must encrypt the backup files to meet regulatory compliance requirements.

The encryption strategy must minimize changes to the databases and to the applications.

You need to recommend a solution to synchronize Database2 to App1_Db1. What should you recommend?

  1. Change data capture

  2. Snapshot replication

  3. Master Data Services

  4. Transactional replication

Correct Answer: D Explanation: Scenario:

Data from Database2 will be accessed periodically by an external application named

Application1. The data from Database2 will be sent to a database named App1_Db1 as soon as changes occur to the data in Database2.

All data in Database2 is recreated each day and does not change until the next data creation process.

Question No.119

DRAG DROP

You use SQL Server 2014 Enterprise Edition.

Your database contains a partitioned table named AuditData. AuditData is partitioned by year. Partition 1 contains data from the year 2010 and prior.

Management has decided to archive all AUDITDATA records from 2010 and prior.

Management wants the records to be removed from the database entirely and provided to the backup team as a zipped text file. The data must no longer reside in the database.

There is very little tolerance for performance degradation in your environment. You need to remove all 2010 and prior data from the AuditData table by using the least amount of system resources possible. Develop the solution by selecting and arranging the required SQL actions in the correct order.

You may not need all of the actions.

image

Correct Answer:

image

Question No.120

DRAG DROP

You plan to deploy SQL Server 2014.

Your company identifies the following monitoring requirements for the database:

image

An e-mail message must be sent when a user logs in.

image

An e-mail message must be sent if CPU utilization exceeds 90 percent. You need to identify which feature meets each monitoring requirement. Which features should you identify?

To answer, drag the appropriate feature to the correct monitoring requirement in the answer area.

image

Correct Answer:

image

Get Full Version of the Exam
70-764 Dumps
70-764 VCE and PDF

Categories