Microsoft 70-764 exam ready here! Get the latest 70-764 exam exercise questions and exam dumps pdf for free! 100% pass the exam to select
the full Microsoft 70-764 dumps: https://www.leads4pass.com/70-764.html the link to get VCE or PDF. All exam questions are updated!
Lead4pass offers the latest Microsoft 70-764 Google Drive
[Latest updates] Free Microsoft 70-764 dumps pdf download from Google Drive: https://drive.google.com/file/d/1KYy4EiDiOsYTHHpMV7B3ec18ghTvaa6g/
Vcecert Exam Table of Contents:
- Microsoft 70-764 Practice testing questions from Youtube
- latest updated Microsoft 70-764 exam questions and answers
- Lead4Pass Microsoft discount code
- About lead4pass
latest updated Microsoft 70-764 exam questions and answers
QUESTION 1
You work as a Database Administrator (DBA) for a company named ABC.com.
The company uses a Microsoft SQL Server 2012 infrastructure. Computer users use custom applications that store data
in SQL Server databases hosted on a server named ABC-SQL1.
You discover that the Windows Application event log on ABC-SQL1 contains error events.
You need to configure a solution that will run an application whenever an event with a specific event ID number is
generated in the Windows Application event log.
What should you configure?
A. You should configure an alert and a job.
B. You should configure an alert and a maintenance plan.
C. You should configure a trigger and a maintenance plan.
D. You should configure a trigger and a job.
E. You should configure an alert and a trigger.
Correct Answer: A
QUESTION 2
You have a database named DB1 that is configured to use the full recovery model. You have a full daily backup job that
runs at 02:00. The job backs up data from DB1 to the file B:\DB1.bak.
You need to restore the DB1 database to the point in time of May 25, 2016 at 02:23 and ensure that the database is
functional and starts to accept connections.
Which Transact-SQL statement should you run?
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: B
QUESTION 3
You plan to deploy SQL Server 2012. You must create two tables named Table 1 and Table 2 that will have the
following specifications:
Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time.
Table2 will contain a column named Column2 that is the product of two other columns in Table2.
Both Table1 and Table2 will contain more than 1 million rows.
You need to recommend which options must be defined for the columns. The solution must minimize the storage
requirements for the tables.
Which options should you recommend? To answer, drag the appropriate options to the correct column in the answer
area.
Select and Place:
Column1 – Sparse; Column2 – Computed
-Sparse columns are ordinary columns that have optimized storage for null values.
Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values.
Consider using sparse columns when the space saved is at least 20 percent to 40 percent.
-A Persisted column would be faster to retrieve.
– A computed column is computed from an expression that can use other columns in the same table. The expression
can be a noncomputed column name, constant, function, and any combination of these connected by one or more
operators. Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table.
Their values are recalculated every time they are referenced in a query. The Database Engine uses the PERSISTED
keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table.
Their values are updated when any columns that are part of their calculation change.
References: http://msdn.microsoft.com/en-us/library/cc280604.aspx http://msdn.microsoft.com/enus/library/ms186241.aspx
QUESTION 4
HOTSPOT
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is
repeated in each question. Each question presents a different goal and answers choices, but the next of scenario is
exactly the same in each question in this series. Start of repeated scenario
Contoso, Ltd. has Microsoft SQL Server databases that support a custom application. The current SQL Server
environment consists of two servers: ContosoSQL1 and ContosoSQL2. These two servers participate in an Always On
Availability
Group named ContosoAG1 that is configured to use synchronous-commit with automatic failover. The secondary replica
is not configured for read-only access.
The application performs both transactional processing and historical data retrieval in a database named ContosoDB.
The application includes an inventory management module. The inventory management module and database have
experienced performance issues.
Users report that a query named InventoryQuery1 takes a long time to complete. The query is shown as follows:
SELECT ProductNumber, Name, ProductLine
FROM Production.Product
WHERE ProductNumber = N\\’\\’
The query plan used by SQL Server for this query is shown in the exhibit. (Click the Exhibit tab.) Various performance
issues, including frequent long-term blocking episodes, prevent business users from completing their daily tasks. You
suspect the tempdb database resources could be responsible. You must create Blocking reports for the ContosoDB
database to identify issues.
Exhibit.
Hot Area:
Correct Answer:
Box 1: Key Lookup (clustered)
The Key Lookup (clustered) should be less than 50%.
Box 2: Change the table\\’s primary key to the Product.ProductNumber column.
Incorrect: Non-Clustered Index Seek
Non-Clustered Index Seek occurs when Columns part of non-clustered index accessed in query and rows located in the
B+ tree.
It is good and ideal to have a Non-Clustered Index Seek.
Action: Evaluate other operators
References:
https://www.sqlshack.com/sql-server-query-execution-plans-beginners-non-clustered-index-operators/
QUESTION 5
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains
a unique solution that might meet the stated goals. Some question sets might have more than one correct solution,
while
others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear on the review screen.
You support an application that stores data in a Microsoft SQL Server database. You have a query that returns data for
a report that users run frequently.
The query optimizer sometimes generates a poorly-performing plan for the query when certain parameters are used.
You observe that this is due to the distribution of data within a specific table that the query uses.
You need to ensure that the query optimizer always uses the query plan that you prefer.
Solution: You force the desired plan.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: B
KEEPFIXED PLAN should be used as it forces the query optimizer not to recompile a query due to changes in
statistics.
When FORCE PLAN is set to ON, the SQL Server query optimizer processes a join in the same order as the tables
appear in the FROM clause of a query. In addition, setting FORCE PLAN to ON forces the use of a nested loop join
unless other types of joins are required to construct a plan for the query, or they are requested with join hints or query
hints. References: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017
QUESTION 6
You administer a Microsoft SQL Server 2016 database named Contoso on a server named Server01.
You need to collect data for a long period of time to troubleshoot wait for statistics when querying Contoso.
You also need to ensure minimum impact on the server.
What should you create?
A. An Alert
B. A Resource Pool
C. An Extended Event session
D. A Server Audit Specification
E. A SQL Profiler Trace
F. A Database Audit Specification
G. A Policy
Correct Answer: C
QUESTION 7
You administer a Microsoft SQL Server 2016 server.
The MSSQLSERVER service uses a domain account named CONTOSO\SQLService.
You plan to configure Instant File Initialization.
You need to ensure that Data File Autogrow operations use Instant File Initialization.
What should you do? Choose all that apply.
A. Restart the SQL Server Agent Service.
B. Disable snapshot isolation.
C. Restart the SQL Server Service.
D. Add the CONTOSO\SQLService account to the Perform Volume Maintenance Tasks local security policy.
E. Add the CONTOSO\SQLService account to the Server Operators fixed server role.
F. Enable snapshot isolation.
Correct Answer: CD
How To Enable Instant File Initialization
1.
Open Local Security Policy and go to Local Policies? gt; User Rights Assignment.
2.
Double click Perform Volume Maintenance Tasks and add your SQL Server database engine service account.
3.
Restart the SQL Server service using SQL Server Configuration Manager and this setting should now be enabled.
References: http://msdn.microsoft.com/en-us/library/ms175935.aspx
QUESTION 8
You create a stored procedure that retrieves all of the rows from a table named Table1.
You need to recommend a solution to ensure that all of the statements in the stored procedure can be executed if
another transaction is modifying rows in Table1 simultaneously.
What should you recommend?
A. Snapshot isolation
B. A database snapshot
C. Filegroups
D. Indexes
Correct Answer: A
Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique
transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version.
The transaction works with the most recent row versions having a sequence number before the sequence number of the
transaction.
Newer row versions created after the transaction has begun are ignored by the transaction.
QUESTION 9
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 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 for the planned changes to the customer classifications. What should you
recommend? (Each correct answer presents part of the solution. Choose all that apply.)
A. Add a row to the Customers table each time a classification changes.
B. Add columns for each classification to the Customers table.
C. Add a table to track any changes made to the classification of each customer.
D. Add a column to the Classifications table to track the status of each classification.
E. Implement change data capture.
Correct Answer: CD
Scenario:
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.
Incorrect Answers:
E: Change data capture provides information about DML changes on a table and a database. By using change data
capture, you eliminate expensive techniques such as user triggers, timestamp columns, and join queries.
QUESTION 10
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may
be correct for more than one question in the series. Each question is independent of the other questions in this series.
Information and details provided in a question apply only to that question.
You need to examine information about logins, CPU times, and Disk I/O on a particular database in Microsoft Azure.
What should you use?
A. Activity Monitor
B. sp_who3 stored procedure
C. SQL Server Management Studio (SSMS) Object Explorer
D. SQL Server Data Collector
E. SQL Server Data Tools (SSDT)
F. SQL Server Configuration Manager
Correct Answer: A
Activity Monitor displays information about SQL Server processes and how these processes affect the current instance
of SQL Server. Activity Monitor is a tabbed document window with the following expandable and collapsible panes:
Overview, Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries.
The Activity User Tasks Pane shows information for active user connections to the instance, including the following
column:
*
Login: The SQL Server login name under which the session is currently executing.
The Recent Expensive Queries Pane shows information about the most expensive queries that have been run on the instance over the last 30 seconds, including the following column:
*
CPU (ms/sec): The rate of CPU used by the query
References: https://technet.microsoft.com/en-us/library/cc879320(v=sql.105).aspx
QUESTION 11
You administer a Microsoft SQL Server 2016 server.
You plan to deploy new features to an application.
You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.
What should you do?
A. Query the sys.dm_db_index_usage_stats DMV.
B. Query the sys.dm_db_missing_index_details DMV.
C. Use the Database Engine Tuning Advisor.
D. Query the sys.dm_db_missing_index_columns DMV.
Correct Answer: C
The Microsoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can
use to optimize query performance. You can use the Database Engine Tuning Advisor to select and create an optimal
set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the
internals of SQL Server. Using the DTA, you can perform the following tasks. Troubleshoot the performance of a
specific problem query Tune a large set of queries across one or more databases Perform an exploratory what-if
analysis of potential physical design changes Manage storage space References: https://docs.microsoft.com/enus/sql/relational-databases/performance/database-engine-tuning-advisor
QUESTION 12
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains
a unique solution that might meet the stated goals. Some question sets might have more than one correct solution,
while
others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear on the review screen.
You need to configure a Microsoft SQL Server instance to ensure that a user named Mail1 can send mail by using
Database Mail.
Solution: You add the DatabaseMailUserRole to Mail1 in the msdb database.
Does the solution meet the goal?
A. Yes
B. No
Correct Answer: A
Database Mail is guarded by the database role DatabaseMailUserRole in the msdb database in order to prevent anyone
from sending arbitrary emails. Database users or roles must be created in the msdb database and must also be a
member of DatabaseMailUserRole in order to send emails with the exception of sysadmin who has all privileges.
Note: Database Mail was first introduced as a new feature in SQL Server 2005 and replaces the SQL Mail feature found
in previous versions.
References: http://www.idevelopment.info/data/SQLServer/DBA_tips/Database_Administration/DBA_20.shtml
QUESTION 13
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may
be correct for more than one question in the series. Each question is independent of the other questions in this series.
Information and details provided in a question apply only to that question.
You observe that several indexes are fragmented.
You need to rebuild the indexes.
What should you use?
A. Activity Monitor
B. sp_who3 stored procedure
C. Object Explorer in the SQL Server Management Studio (SSMS)
D. SQL Server Data Collector
E. SQL Server Data Tools (SSDT)
F. SQL Server Configuration Manager
Correct Answer: C
How to: Rebuild an Index (SQL Server Management Studio)
To rebuild an index
In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
Expand Databases, expand the database that contains the table with the specified index, and then expand Tables.
Expand the table in which the index belongs and then expand Indexes.
Right-click the index to rebuild and then click Rebuild.
To start the rebuild operation, click OK.
References: https://technet.microsoft.com/en-us/library/ms187874(v=sql.105).aspx
Lead4Pass Microsoft discount code
About lead4pass
Lead4Pass has 7 years of exam experience! A number of professional Microsoft exam experts! Update exam questions throughout the year! The most complete exam questions and answers! The safest buying experience! The greatest free sharing of exam practice questions and answers!
Our goal is to help more people pass the Microsoft exam! Exams are a part of life, but they are important!
In the study, you need to sum up the study! Trust Lead4Pass to help you pass the exam 100%!
Summarize:
VceCert free to share Microsoft 70-764 exam exercise questions, 70-764 pdf, 70-764 exam video! Lead4pass updated exam questions and answers throughout the year!
Make sure you pass the exam successfully. Select lead4Pass 70-764 to pass Microsoft 70-764 exam “Administering a SQL Database Infrastructure”.
ps.
Latest update Lead4pass 70-764 exam dumps: https://www.leads4pass.com/70-764.html (452 Q&As)
[Latest updates] Free Microsoft 70-764 Dumps pdf download from Google Drive: https://drive.google.com/file/d/1KYy4EiDiOsYTHHpMV7B3ec18ghTvaa6g/