Prev Next Question 135/256 - 70-764.v2019-07-01

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:

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

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 solution to minimize the amount of time it takes to execute USP_5. What should you include in the recommendation?

LEAVE A REPLY

Your email address will not be published. Required fields are marked *

Question List (256q)
Question 1: You maintain a database named DB1 that has a nonpartitioned ...
Question 2: You plan to create a database. The database will be used by ...
Question 3: General Overview You are the Senior Database Administrator (...
Question 4: General Overview You are the Senior Database Administrator (...
Question 5: You have two databases named DB1 and DB2 that are located on...
Question 6: You have a SQL Server 2014 environment That includes four se...
Question 7: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 8: Note: This question is part of a series of questions that us...
Question 9: Overview General Overview ADatum Corporation has offices in ...
Question 10: You work as a Database Administrator (DBA) for a company nam...
Question 11: Note: This question is part of a series of questions that us...
Question 12: Note: This question is part of a series of questions that pr...
Question 13: You administer a Microsoft SQL Server 2016 database named Co...
Question 14: Note: This question is part of a series of questions that us...
Question 15: Overview You are a database administrator for a company name...
Question 16: Overview Application Overview Contoso, Ltd., is the develope...
Question 17: Note: This question is part of a series of questions that pr...
Question 18: You have the following Microsoft SQL Server instances: (Exhi...
Question 19: Your company has a SQL Azure subscription. You implement a d...
Question 20: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 21: You install a Microsoft SQL Server 2016 instance. The instan...
Question 22: You administer a Microsoft SQL Server 2016 database instance...
Question 23: Note: This question is part of a series of questions that pr...
Question 24: You create a stored procedure that retrieves all of the rows...
Question 25: You work as a Database Administrator (DBA) at ABC.com. All d...
Question 26: Note: This question is part of a series of questions that us...
Question 27: You manage a Microsoft SQL Server environment. You plan to e...
Question 28: Overview Application Overview Contoso, Ltd., is the develope...
Question 29: You administer a Microsoft SQL Server 2016 database. Users r...
Question 30: Note: This question is part of a series of questions that pr...
Question 31: You are the administrator of a Microsoft SQL Server 2016 ser...
Question 32: You maintain a database named DB1 that has a nonpartitioned ...
Question 33: Overview General Overview ADatum Corporation has offices in ...
Question 34: Note: This question is part of a series of questions that pr...
Question 35: Overview General Overview ADatum Corporation has offices in ...
Question 36: You have a database named DB1 that is configured to use the ...
Question 37: General Overview You are the Senior Database Administrator (...
Question 38: Note: This question is part of a series of questions that pr...
Question 39: You have a database named DB1. Users report that a database ...
Question 40: You administer a Microsoft SQL Server 2016 database named Co...
Question 41: You deploy a database by using SQL Server 2014. The database...
Question 42: You are implementing a SQL Server 2016 five-node failover cl...
Question 43: You have the following servers: (Exhibit) SQL1 hosts a datab...
Question 44: Note: This question is part of a series of questions that pr...
Question 45: You are designing a SQL Server database for an order fulfill...
Question 46: Overview Application Overview Contoso, Ltd., is the develope...
Question 47: You are designing a Windows Azure SQL Database for an order ...
Question 48: Which task should you perform before you can create a utilit...
Question 49: Overview You are a database administrator for a company name...
Question 50: You manage a SQL Server 2014 instance that contains a databa...
Question 51: Note: This question is part of a series of questions that pr...
Question 52: Note: This question is part of a series of questions that pr...
Question 53: Note: This question is part of a series of questions that us...
Question 54: You administer two instances of Microsoft SQL Server 2016. Y...
Question 55: You administer a Microsoft SQL Server 2016 server that has S...
Question 56: Note: This question is part of a series of questions that pr...
Question 57: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 58: You have a customer who has several SQL Server 2012 database...
Question 59: You work as a Developer at ABC.com. All databases are hosted...
Question 60: You work as a Database Administrator (DBA) for a company nam...
Question 61: You are the new database administrator for a SQL Server 2014...
Question 62: You plan to implement a fault tolerance solution for a Micro...
Question 63: Overview Application Overview Contoso, Ltd., is the develope...
Question 64: You deploy a database by using SQL Server 2012. The database...
Question 65: Which feature should you enable and configure so session req...
Question 66: A company has an on-premises Microsoft SQL Server environmen...
Question 67: You administer two Microsoft SQL Server 2012 servers. Each s...
Question 68: Note: This question is part of a series of questions that pr...
Question 69: Note: This question is part of a series of questions that pr...
Question 70: Note: This question is part of a series of questions that us...
Question 71: Note: This question is part of a series of questions that pr...
Question 72: Your company stored customer data, including credit card num...
Question 73: Note: This question is part of a series of questions that pr...
Question 74: Note: This question is part of a series of questions that pr...
Question 75: You administer a Microsoft SQL Server 2016 instance. The ins...
Question 76: Note: This question is part of a series of questions that pr...
Question 77: You have a database hosted on SQL Server 2012 R2. The databa...
Question 78: Note: This question is part of a series of questions that us...
Question 79: General Overview You are the Senior Database Administrator (...
Question 80: You administer a Microsoft SQL Server 2012 instance. You nee...
Question 81: Overview Application Overview Contoso, Ltd., is the develope...
Question 82: You have a table that has grown in the past six months. A us...
Question 83: You administer a Microsoft SQL Server 2016 server. The MSSQL...
Question 84: General Overview You are the Senior Database Administrator (...
Question 85: You configure log shipping on two servers named SQL1 and SQL...
Question 86: Note: This question is part of a series of questions that us...
Question 87: You administer a SQL Server 2016 database instance. You need...
Question 88: Note: This question is part of a series of questions that pr...
Question 89: You are planning on deploying a server that will be dedicate...
Question 90: Note: This question is part of a series of questions that pr...
Question 91: Note: This question is part of a series of questions that pr...
Question 92: You administer all the deployments of Microsoft SQL Server 2...
Question 93: Note: This question is part of a series of questions that pr...
Question 94: You administer two Microsoft SQL Server 2016 servers named P...
Question 95: You use Microsoft SQL Server 2016 to write code for a transa...
Question 96: You want to simulate read, write, checkpoint, backup, sort, ...
Question 97: Note: This question is part of a series of questions that pr...
Question 98: You administer a single server that contains a Microsoft SQL...
Question 99: Which feature should you enable and configure so session req...
Question 100: Overview General Overview ADatum Corporation has offices in ...
Question 101: Your company has offices in Seattle and Montreal. The networ...
Question 102: You administer a SQL Server 2014 instance. Users report that...
Question 103: Overview Application Overview Contoso, Ltd., is the develope...
Question 104: Note: This question is part of a series of questions that us...
Question 105: You use SQL Server 2014 to maintain the data used by applica...
Question 106: Note: This question is part of a series of questions that us...
Question 107: Note: This question is part of a series of questions that pr...
Question 108: Note: This question is part of a series of questions that pr...
Question 109: You administer a Microsoft SQL Server 2016 server that hosts...
Question 110: You have a database named DB1 that contains two tables. You ...
Question 111: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 112: You are troubleshooting an application that runs a query. Th...
Question 113: Note: This question is part of a series of questions that pr...
Question 114: You work as a Database Administrator (DBA) for a company nam...
Question 115: Note: This question is part of a series of questions that pr...
Question 116: You administer a Microsoft SQL Server 2016 database that inc...
Question 117: You are a database administrator for an organization. You mu...
Question 118: Note: This question is part of a series of questions that pr...
Question 119: You administer a Microsoft SQL Server 2016 server that hosts...
Question 120: You administer a Microsoft SQL Server 2016 database that con...
Question 121: Note: This question is part of a series of questions that pr...
Question 122: You administer a Microsoft SQL Server 2016 database. You hav...
Question 123: Overview General Overview ADatum Corporation has offices in ...
Question 124: You have two SQL Server 2012 instances named SQLDev and SQLP...
Question 125: You have a database named Saleshistory that records sales tr...
Question 126: You administer a Microsoft SQL Server database named Sales. ...
Question 127: You need to address the Sales Director's requirements regard...
Question 128: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 129: You administer a Windows Azure SQL Database database named I...
Question 130: You are a database administrator for Contoso, Ltd. You confi...
Question 131: Note: This question is part of a series of questions that us...
Question 132: You administer a Microsoft SQL Server 2016 database. You pro...
Question 133: You work as a Database Administrator (DBA) for a company nam...
Question 134: Note: This question is part of a series of questions that pr...
Question 135: Overview General Overview ADatum Corporation has offices in ...
Question 136: You administer a Microsoft SQL Server 2016 server. One of th...
Question 137: Note: This question is part of a series of questions that pr...
Question 138: You administer a Microsoft SQL Server 2016 instance. You dis...
Question 139: Note: This question is part of a series of questions that us...
Question 140: You have five servers that run Microsoft SQL Server. Each se...
Question 141: You maintain a database named DB1 that has a nonpartitioned ...
Question 142: You have two SQL Server instances named SQLDev and SQLProd t...
Question 143: Note: This question is part of a series of questions that pr...
Question 144: You maintain three datacenters in different geographical reg...
Question 145: Overview Application Overview Contoso, Ltd., is the develope...
Question 146: You administer a Microsoft SQL Server 2016 database that has...
Question 147: You work as a Developer at ABC.com. All databases are hosted...
Question 148: You create an availability group that has replicas named HA/...
Question 149: You have a database that stores information for a shipping c...
Question 150: You administer all the deployments of Microsoft SQL Server 2...
Question 151: You administer a Microsoft SQL Server 2016 database. You wan...
Question 152: Overview You are a database administrator for a company name...
Question 153: You have four databases that are accessed by using an Online...
Question 154: You need to validate rows before they are added to a table e...
Question 155: You administer a Microsoft SQL Server 2016 server. When tran...
Question 156: Overview General Overview ADatum Corporation has offices in ...
Question 157: You have configured Resource Governor with three resource po...
Question 158: You need to address the requirements for disc usage monitori...
Question 159: You are creating a database that will store usernames and pa...
Question 160: Overview General Overview ADatum Corporation has offices in ...
Question 161: Overview Application Overview Contoso, Ltd., is the develope...
Question 162: You administer a Microsoft SQL Server 2016 instance. After a...
Question 163: You have an SQL Server 2014 server named SQL1. You are desig...
Question 164: You create an availability group named HaContoso that has re...
Question 165: You administer a Microsoft SQL Server 2016 database named Co...
Question 166: You administer a Microsoft SQL Server 2016 database named Co...
Question 167: You have a server that has SQL Server 2014 installed. The se...
Question 168: Note: This question is part of a series of questions that pr...
Question 169: You are using dynamic management views to monitor an SQL Ser...
Question 170: Note: This question is part of a series of questions that us...
Question 171: You administer all the deployments of Microsoft SQL Server 2...
Question 172: Note: This question is part of a series of questions that us...
Question 173: Note: This question is part of a series of questions that us...
Question 174: Note: This question is part of a series of questions that us...
Question 175: Note: This question is part of a series of questions that pr...
Question 176: Note: This question is part of a series of questions that pr...
Question 177: You are creating a database that will store usernames and cr...
Question 178: Overview General Overview ADatum Corporation has offices in ...
Question 179: Note: This question is part of a series of questions that us...
Question 180: Note: This question is part of a series of questions that us...
Question 181: You have a SQL Server 2012 database named DB1. You have a ba...
Question 182: You administer a Microsoft SQL Server 2016 database named Co...
Question 183: Note: This question is part of a series of questions that pr...
Question 184: You are troubleshooting an application that runs a query. Th...
Question 185: You administer a Windows Azure SQL Database database named H...
Question 186: Note: This question is part of a series of questions that us...
Question 187: You use SQL Azure to store data used by an e-commerce applic...
Question 188: Note: This question is part of a series of questions that pr...
Question 189: You configure the Microsoft SQL Server Agent service to star...
Question 190: You plan to integrate an on-premises Microsoft SQL Server en...
Question 191: You administer a Microsoft SQL Server 2016 instance. You nee...
Question 192: You plan to integrate an on-premises Microsoft SQL Server en...
Question 193: You have a server named Server1 that has 2 processors. You p...
Question 194: You have a Microsoft SQL Server instance that has a database...
Question 195: You are designing a database named DB1. Changes will be depl...
Question 196: You need to provide a group of users from the IT and Manufac...
Question 197: Note: This question is part of a series of questions that us...
Question 198: Note: This question is part of a series of questions that us...
Question 199: A company has an on-premises Microsoft SQL Server environmen...
Question 200: You administer a Microsoft SQL Server 2016 instance that has...
Question 201: You have a database named DB1 that stores more than 700 giga...
Question 202: Note: This question is part of a series of questions that us...
Question 203: You need to grant access to an OLTP database regardless of t...
Question 204: Note: This question is part of a series of questions that us...
Question 205: You administer a Microsoft SQL Server 2016 Enterprise Editio...
Question 206: You have a database named DB1. You plan to create a stored p...
Question 207: A Microsoft SQL Server database named DB1 has two filegroups...
Question 208: Note: This question is part of a series of questions that us...
Question 209: Overview Application Overview Contoso, Ltd., is the develope...
Question 210: Overview You are a database administrator for a company name...
Question 211: You administer a Microsoft SQL Server 2016 instance that con...
Question 212: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 213: You work as a Database Administrator (DBA) for a company nam...
Question 214: You have an application that queries a database. Users repor...
Question 215: You have a server named Server1 that has 16 processors. You ...
Question 216: You administer a Microsoft SQL Server 2016 server. You plan ...
Question 217: Overview Application Overview Contoso, Ltd., is the develope...
Question 218: Note: This question is part of a series of questions that pr...
Question 219: You are a database administrator for a Microsoft SQL Server ...
Question 220: Note: This question is part of a series of questions that pr...
Question 221: You have a SQL Server 2014 environment That contains 20 serv...
Question 222: You are building a stored procedure for a SQL Azure database...
Question 223: You run the sp_whosystem stored procedure. A process is bloc...
Question 224: Note: This question is part of a series of questions that us...
Question 225: Note: This question is part of a series of questions that us...
Question 226: Note: This question is part of a series of questions that pr...
Question 227: You are a database administrator at Contoso, Ltd. You are pr...
Question 228: General Overview You are the Senior Database Administrator (...
Question 229: You administer a Microsoft SQL Server 2016 database named Or...
Question 230: You administer a single server that contains a Microsoft SQL...
Question 231: You administer a Microsoft SQL Server 2016 database. You nee...
Question 232: You administer a Microsoft SQL Server 2016 database named Co...
Question 233: You administer several Microsoft SQL Server 2016 database se...
Question 234: You create a Management Data Warehouse instance and enable d...
Question 235: Overview Application Overview Contoso, Ltd., is the develope...
Question 236: You work as a Database Administrator (DBA) for a company nam...
Question 237: General Overview You are the Senior Database Administrator (...
Question 238: Note: This question is part of a series of questions that us...
Question 239: You install Microsoft SQL Server 2016 on a new server. After...
Question 240: You have a SQL Server 2012 database named DB1. You plan to i...
Question 241: Note: This question is part of a series of questions that pr...
Question 242: Note: This question is part of a series of questions that pr...
Question 243: General Overview You are the Senior Database Administrator (...
Question 244: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 245: You have a Microsoft SQL Server database named DB1. Nested a...
Question 246: You have a query that is used by a reporting dashboard. User...
Question 247: You work as a Database Administrator (DBA) for a company nam...
Question 248: Overview You are a database administrator for a company name...
Question 249: You are a database administrator for a Microsoft SQL Server ...
Question 250: Note: This question is part of a series of questions that pr...
Question 251: Overview You are a database administrator for a company name...
Question 252: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 253: You deploy a SQL Server instance named SQLProd that uses SQL...
Question 254: You administer a Microsoft SQL Server 2016 failover cluster....
Question 255: You are migrating a database named Orders to a new server th...
Question 256: You administer a Microsoft SQL Server 2016 database instance...