http://https://www.examslabs.com/Microsoft/MCSA-SQL-2016/best-70-764-exam-dumps.html (120 Q&As Dumps, 30%OFF Special Discount: bmzblwH7 )

NEW QUESTION NO: 8
You have a database named DB1 that stores more than 700 gigabyte (GB) of data and serves millions of requests per hour.
Queries on DB1 are taking longer than normal to complete.
You run the following Transact-SQL statement:
SELECT * FROM sys.database_query_store_options
You determine that the Query Store is in Read-Only mode.
You need to maximize the time that the Query Store is in Read-Write mode.
Which Transact-SQL statement should you run?
A. ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL)
B. ALTER DATABASE DB1SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 50)
C. ALTER DATABASE DB1SET QUERY_STORE (CLEANUP_POLICY
(STALE_QUERY_THRESHOLD_DAYS = 14));
D. ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = NONE)
Answer: C
Explanation/Reference:
Explanation:
Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries.
By default, Query Store is configured to keep the data for 30 days which may be unnecessarily long for your scenario.
Avoid keeping historical data that you do not plan to use. This will reduce changes to read-only status. The size of Query Store data as well as the time to detect and mitigate the issue will be more predictable. Use Management Studio or the following script to configure time-based cleanup policy:
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));
References: https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the- query-store

NEW QUESTION NO: 9
You administer a Microsoft SQL Server 2016 database named Contoso on a server named Server01.
You need to prevent users from disabling server audits in Server01.
What should you create?
A. a Resource Pool
B. a Database Audit Specification
C. an Alert
D. an Extended Event session
E. a Server Audit Specification
F. a SQL Profiler Trace
G. a Policy
Answer: G

NEW QUESTION NO: 10
DRAG DROP
You have a test server that contains a database named DB1. Backups of the database are written to a single backup device. The backup device has a full, differential, and transaction log backup.
You discover that the database is damaged. You restore the database to the point at which the differential backup was taken.
You need to rebuild the database with data stored in the latest transaction logs.
How should you complete the Transact-SQL statement? To answer. drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:

Answer: 

Explanation/Reference:
Explanation:
Box 1: RESTORE
Box 2: RECOVERY
The RESTORE ... WITH RECOVERY option puts the database into a useable state, so users can access a restored database.
References: https://www.mssqltips.com/sqlservertutorial/112/recovering-a-database-that-is-in-the- restoring-state/

NEW QUESTION NO: 11
You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.
You create a user account in the msdb database for the AgentPortalUser login.
You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages.
What should you do?
A. In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser account.
B. Disable the guest user in the msdb database.
C. Use the sysmail_help_profileaccount_sp stored procedure to add accounts to the Database Mail profile.
D. In the Database Mail Configuration Wizard, create an email account for each recipient's email address in the Database Mail profile.
Answer: A
Explanation/Reference:
You enable and configure Database Mail using the Database Mail Configuration Wizard.
Profiles are either public or private. A private profile is accessible only to specific users or roles.
References: https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/configure-database- mail

NEW QUESTION NO: 12
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 collect performance metrics on multiple Microsoft SQL Server instances and store the data in a single repository.
You need to examine disk usage, query statistics, and server activity without building custom counters.
What should you use?
A. Activity Monitor
B. Sp_who3 stored procedure
C. Object Explorer in the Microsoft SQL Server Management Studio (SSMS)
D. SQL Server Data Collector
E. SQL Server Data Tools (SSDT)
F. SQL Server Configuration Manager
Answer: D
Explanation/Reference:
Explanation:
The data collector is a core component of the data collection platform for SQL Server 2017 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited to performance data

NEW QUESTION NO: 13
You are designing a monitoring application for a new SQL Server 2014 instance.
You need to recommend a solution to generate a report that displays the 10 most frequent wait types that occur for the instance.
What should you include in the recommendation? More than one answer choice may achieve the goal.
Select the BEST answer.
A. The SQL Server error log
B. The sys.dm_os_wait_stats dynamic management view
C. The DBCC SQLPERF(WAITSTATS) command
D. SQL Server Profiler
Answer: B
Explanation/Reference:
Explanation:
sys.dm_os_wait_stats
Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.
Columns include:
waiting_tasks_count
Number of waits on this wait type.
This counter is incremented at the start of each wait.

NEW QUESTION NO: 14
You administer a Microsoft SQL Server 2016 database instance.
You create a new user named UserA. You need to ensure that UserA is able to create SQL Server Agent jobs and to execute SQL Server Agent jobs.
To which role should you add UserA?
A. DatabaseMailUserRole
B. Securityadmin
C. SQLAgentUserRole
D. RSExecRole
Answer: C

NEW QUESTION NO: 15
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:
Costs for new licenses must be minimized.

Private information that is accessed by Application must be stored in a secure format.

Development effort must be minimized whenever possible.

The storage requirements for databases must be minimized.

System administrators must be able to run real-time reports on disk usage.

The databases must be available if the SQL Server service fails.

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

corruption.
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?
A. Read committed
B. Repeatable read
C. Read uncommitted
D. Serializable
Answer: B
Explanation/Reference:
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.

NEW QUESTION NO: 16
You administer a Microsoft SQL Server 2016 default instance. The instance is hosted by a server that has a local firewall configured.
The firewall only allows inbound connections on port 1433. The server only hosts a single instance of SQL Server.
You need to ensure that the instance is configured to allow remote connections even if the SQL Server is unresponsive to client connections.
What should you do?
A. Enable inbound connections on TCP port 1434 in the Windows Firewall on the server.
B. Execute the following Transact-SQL command: sp_configure 'remote admin connections',
C. Execute the Reconfigure command.
D. Execute the following Transact-SQL command: sp_configure 'remote access', 1
E. Restart the SQL Server Agent Service.
F. Enable inbound connections on TCP port 135 in the Windows Firewall on the server.
Answer: A,B,C
Explanation/Reference:
SQL Server provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection. By default, the DAC is only available from a client on the server. To enable client applications on remote computers to use the DAC, use the remote admin connections option of sp_configure.
By default, the DAC only listens on the loop-back IP address (127.0.0.1), port 1434 The following example enables the DAC from a remote computer.
sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO
References: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/remote-admin- connections-server-configuration-option

NEW QUESTION NO: 17
You plan to install a Microsoft SQL Server 2016 instance.
The instance will support a database that has the following requirements:
Store Excel workbooks on the file system.

Access the workbooks through Transact-SQL.

Include the workbooks in database backups.

During installation, you need to ensure that the requirements will be met.

Which feature should you use?
A. SQL Server Integration Services (SSIS)
B. Excel Services
C. FILESTREAM
D. OpenXML
Answer: C

NEW QUESTION NO: 18
Which feature should you enable and configure so session requests addressed to a specific instance can be allocated different processor resources based on session request properties?
A. Resource Governor
B. Windows System Resource Manager
C. Processor affinity
D. I/O affinity
Answer: A
Explanation/Reference:
Resource Governor enables you to allocate session requests to different resources based on the characteristics of the session request properties.
References: https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource- governor