NEW QUESTION 1
You manage a Microsoft SQL Server instance named SQL1 that has 32 gigabytes (GB) of total memory. The instance supports an app named App1 that only uses a single thread. App1 frequently queries the database using the same index. The operating system and App1 combined require 8 GB of memory to function.
You need to ensure that the SQL Server does not limit the performance of App1. What configuration option should you set?

• A. min memory per query to 4 GB
• B. index create memory to 16 GB
• C. max worker threads to 1
• D. max server memory to 16 GB

Answer: B

Explanation: The index creates memory option controls the maximum amount of memory initially allocated for sort operations when creating indexes. The default value for this option is 0 (self-configuring). If more memory is later needed for index creation and the memory is available, the server will use it; thereby, exceeding the setting of this option. If additional memory is not available, the index creation will continue using the memory already allocated.
References:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-indexcreate-memory-ser

NEW QUESTION 2
You administer a Microsoft SQL Server 2014.
A process that normally runs in less than 10 seconds has been running for more than an hour. You examine the application log and discover that the process is using session ID 60.
You need to find out whether the process is being blocked. Which Transact-SQL statement should you use?

• A. EXEC sp_who 60
• B. SELECT * FROM sys.dm_exec_sessions WHERE sessionid = 60
• C. EXEC sp_helpdb 60
• D. DBCC INPUTBUFFER (60)

Answer: A

Explanation: sp_who provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.
Example: Displaying a specific process identified by a session ID EXEC sp_who '10' --specifies the process_id;
References:https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-who-transact-

NEW QUESTION 3
You plan to migrate a Microsoft sql server instance between physical servers. You must migrate the metadata associated with the database instance.
You need to ensure that the new instance retains the existing jobs and alerts. Solutions: You restore the model database.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: The model database does not handle alerts and jobs. It is used as the template for all databases created on an instance of SQL Server.
The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/msdb-database?view=sql-server-2021

NEW QUESTION 4
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 below, identify the parameter that must be configured for each databases. Select one option for DB1, and one option for DB2. Select one option for each column.

Answer:

Explanation: Query_optimizer_hotfixes DB1: MAXDOP
You can use the max degree of parallelism (MAXDOP) option to limit the number of processors to use in parallel plan execution.
DB2: LEGACY_CARDINALITY_ESTIMATION
The CE (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.
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.
Example:
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON; GO

NEW QUESTION 5
You plan to create an AlwaysOn availability group that will have two replicas in Microsoft Azure and two on premises replicas.
You need to configure the network to support the availability group listener. Which cmdlet should you run first?

• A. New-AzureRmAvailabilitySet
• B. New-AzureRmLoadBalancer
• C. New-AzureRmSqlDatabaseSecondary
• D. New-AzureRmSqlElasticPool
• E. New-AzureRmVM
• F. New-AzureRmSqlServer
• G. New-AzureRmSqlDatabaseCopy
• H. New-AzureRmSqlServerCommunicationLink

Answer: B

Explanation: An availability group listener is a virtual network name that clients connect to for database access. On Azure virtual machines, a load balancer holds the IP address for the listener. The load balancer routes traffic to the instance of SQL Server that is listening on the probe port. Usually, an availability group uses an internal load balancer.
References:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windowsportal-sql-ps-al

NEW QUESTION 6
You are the database administrator in your company. You plan to create 10 identical environments that use SQL Server 2021 as a database engine. Each environment has the following custom requirements:
Three user databases must be preinstalled.
The tempdb database must contain eight data files that are 1024 MB each.
Trace flag 2371 must be turned at the instance level.
The solution must meet the following requirements:
The instance must be preconfigured.
No other database features are required in the future.
The solution must use the minimum administrative effort.
You need to prepare the environments. What should you do?

• A. Provision 10 Azure virtual machines that each contain SQL Server 2021, installed by using the default settings.
• B. Create an installation configuration file and perform unattended installations of SQL Server 2021.
• C. Create a virtual machine template by using a prepared instance of SQL Server 2021.
• D. Create a virtual machine template by using a complete instance of SQL Server 2021.

Answer: D

Explanation: You should create a virtual machine template by using a complete instance of SQL Server 2021. You use the sysprep tool to prepare a complete instance of SQL Server 2021. By using a complete instance, SQL Server, the network, and the users are all created, and the system cannot be reconfigured during the installation process.

NEW QUESTION 7
You administer a SQL Server 2014 database instance.
You need to configure the SQL Server Database Engine service on a failover cluster. Which user account should you use?

• A. A domain user
• B. The BUILTINSYSTEM account
• C. A local user with Run as Service permissions
• D. The SQLBrowser account

Answer: A

Explanation: References:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/install/create-a-new-sql-server-failover-cluster-s

NEW QUESTION 8
You use a contained database named ContosoDb within a domain.
You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.
Which type of user should you create?

• A. SQL user without login
• B. User mapped to an asymmetric key
• C. Domain user
• D. login mapped to a virtual account

Answer: C

Explanation: If the service must interact with network services, access domain resources like file shares or if it uses linked server connections to other computers running SQL Server, you might use a minimally-privileged domain account. Many server-to-server activities can be performed only with a domain user account.
References:https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-servic

NEW QUESTION 9
You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01. You need to be notified immediately when fatal errors occur on Server01.
What should you create?

• A. A Database Audit Specification
• B. A Policy
• C. An Alert
• D. A SQL Profiler Trace
• E. A Resource Pool
• F. An Extended Event session
• G. A Server Audit Specification

Answer: C

Explanation: SQL Server has alerts that get more important based on the severity of the alert. Anything of severity 16 or below tends to refer to the database and deals with issues that are tied to syntax errors, violations of foreign keys, etc. While those errors are typically important, they don't refer to anything with regards to overall health of the SQL Server. Alerts 17 through 25 do. Those are the ones your health checks are probably firing on.

References: https://www.mssqltips.com/sqlservertip/3384/configuring-critical-sql-server-alerts/

NEW QUESTION 10
You have a server named Serverl that is hosted in an Azure virtual machine. Server1 contains the following:
One instance of SQL Server 2021 Enterprise
10 databases
500 stored procedures
You have a database named Database1 that is hosted on Server1.
Database1 contains 100 queries that are executed dynamically from web applications. You plan to remove data from the procedure cache on Database1.
You have the following requirements:
Changes to Database1 must not affect other databases that are hosted on Server1
Changes to Database1 must not affect the performance of queries that are stored in other databases.
The solution must minimize administrative effort.
You need to remove the data from the procedure cache as quickly as possible. What should you do?

• A. Run DBCC FREEPROCCACHE.
• B. Run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE in thecontext of Database 1.
• C. Run DBCC DROPCLEANBUFFERS.
• D. Write a script that iterates through each stored procedure definition and add WITH RECOMPILE to the definition.

Answer: B

Explanation: You should run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE in the
context of Database! This statement lets you change the settings of a database without affecting other databases that are installed on the instance of SQL Server 2021.

NEW QUESTION 11
You administer a Microsoft SQL Server 2014 instance that has several SQL Server Agent jobs configured. When SQL Server Agent jobs fail, the error messages returned by the job steps do not provide the required
detail.
The following error message is an example error message:
"The job failed. The Job was invoked by User CONTOSOServiceAccount. The last step to run was step 1 (Subplan_1)."
You need to ensure that all available details of the job step failures for SQL Server Agent jobs are retained. What should you do?

• A. Configure output files.
• B. Expand agent logging to include information from all events.
• C. Disable the Limit size of job history log feature.
• D. Configure event forwarding.

Answer: B

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms175488.aspx

NEW QUESTION 12
HOTSPOT
You use Resource Manager to deploy a new Microsoft SQL Server instance in a Microsoft Azure virtual machine (VM) that uses Premium storage. The combined initial size of the SQL Server user database files is expected to be over 200 gigabytes (GB). You must maximize performance for the database files and the log file.
You add the following additional drive volumes to the VM:

You have the following requirements:
You need to deploy the SQL instance.
In the table below, identify the drive where you must store each SQL Server file type. NOTE: Make only one selection in each column. Each correct selection is worth one point.

Answer:

Explanation: Enable read caching on the disk(s) hosting the data files and TempDB.
Do not enable caching on disk(s) hosting the log file. Host caching is not used for log files.

Topic 2, Manage databases and instances

NEW QUESTION 13
You are a database developer for an application hosted on a Microsoft SQL Server 2014 server. The database contains two tables that have the following definitions:

Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders.
Which Transact-SQL query do you use?

• A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY COUNT(OrderAmount) DESC) AS RnkFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDWHERE o.Rnk = 1
• B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM(SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK()OVER (PARTITION BY CustomerIDORDER BY COUNT(o.OrderAmount) ASC) AS RnkFROM Customer cINNER JOIN Orders oON c.CustomerID = o.CustomerIDGROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1
• C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY OrderAmount DESC) AS RnkFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDWHERE o.Rnk = 1
• D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,COUNT(OrderAmount) DESC) AS OrderAmountFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDORDER BY OrderAmount DESC

Answer: A

Explanation: Use descending (DESC) ordering.
To order by the number of orders we use ORDER BY COUNT(OrderAmount). Finally a WHERE close is needed: WHERE o.Rnk = 1

NEW QUESTION 14
You administer a SQL Server 2014 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales. UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that the following requirements are met: Which Transact-SQL statement should you use?

• A. REVOKE SELECT ON Schema::Customers FROM UserA
• B. DENY SELECT ON Object::Regions FROM UserA
• C. EXEC sp_addrolemember 'Sales', 'UserA'
• D. DENY SELECT ON Object::Regions FROM Sales
• E. REVOKE SELECT ON Object::Regions FROM UserA
• F. DENY SELECT ON Schema::Customers FROM Sales
• G. DENY SELECT ON Schema::Customers FROM UserA
• H. EXEC sp_droprolemember 'Sales', 'UserA'
• I. REVOKE SELECT ON Object::Regions FROM Sales
• J. REVOKE SELECT ON Schema::Customers FROM Sales

Answer: J

Explanation: Use REVOKE to remove the grant or deny of a permission.
References:https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-

NEW QUESTION 15
You plan to deploy Microsoft SQL Server on a Microsoft Azure Virtual machine. The virtual machine will have a 30-TB database and will have 10 1-TB VHDs for the database.
You need to configure the storage to meet the following requirements:
Evenly distribute read and write operations across the VHDs.
Minimize the read and write time.
Which storage configuration should you use?

• A. a parity storage pool
• B. a simple storage pool
• C. a mirrored storage pool
• D. a striped volume
• E. a RAID-5 volume

Answer: D

Explanation: Data that is written to a striped volume is interleaved to all disks at the same time instead of sequentially. Therefore, disk performance is the fastest on a RAID 0 volume as compared to any other type of disk
configuration.
Reference:
https://support.microsoft.com/en-us/help/323433/how-to-establish-a-striped-volume-raid-0-inwindows-server-20

NEW QUESTION 16
Note: This questions 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 deploy Microsoft SQL Server to a virtual machine in Azure. You distribute the database files and filegroups across multiple Azure storage disks.
You must be able to manage the databases as individual entities by using SQL Server Management Studio. All data in the databases must be stored encrypted. Backups must be encrypted by using the same key as the live copy of the database.
You need to secure the data. What should you implement?

• A. transport-level encryption
• B. cell-level encryption
• C. Transparent Data Encryption
• D. Always Encrypted
• E. Encrypting File System
• F. BitLocker
• G. dynamic data masking

Answer: C

Explanation: Transparent data encryption (TDE) encrypts your databases, associated backups, and transaction log files at rest without requiring changes to your applications.
TDE encrypts the storage of an entire database by using a symmetric key called the database encryption key. In SQL Database the database encryption key is protected by a built-in server certificate. The built-in server certificate is unique for each SQL Database server.
References:https://msdn.microsoft.com/en-us/library/dn948096.aspx

Topic 3, Deploy and migrate applications

NEW QUESTION 17
A company has an on-premises Microsoft SQL Server 2021 environment. All futures databases must meet the following requirements:
The recovery model must be set to simple.
The compatibility level must be set to SQL server 2014 (120).
Your need to configure the SQL server 2021 environment.
In the table below, identify the database you must modify for each requirement.

Answer:

Explanation: Model: Change from full to simple Recovery Model
Newly created user databases use the same recovery model as the model database.
The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. The entire contents of the model database, including database options, are copied to the new database.
Model: Set compatibility level to 120
For all installations of SQL Server, the default compatibility level is set to the version of the Database Engine. Databases are set to this level unless the model database has a lower compatibility level.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/model-database?view=sql-server-2021 https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-se

NEW QUESTION 18
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets stated goals.
You have a mission-critical application that stores data in a Microsoft SQL Server instance. The application runs several financial reports. The reports use a SQL Server-authenticated login named Reporting_User. All queries that write data to the database use Windows authentication.
Users report that the queries used to provide data for the financial reports take a long time to complete. The queries consume the majority of CPU and memory resources on the database server. As a result, read-write queries for the application also take a long time to complete.
You need to improve performance of the application while still allowing the report queries to finish.
Solution: You configure the Resource Governor to limit the amount of memory, CPU, and IOPS used for the pool of all queries that the Reporting_user login can run concurrently.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: SQL Server Resource Governor is a feature than you can use to manage SQL Server
workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.
References:https://msdn.microsoft.com/en-us/library/bb933866.aspx

NEW QUESTION 19
DRAG DROP
You deploy a new Microsoft Azure SQL Database instance to support a variety of mobile applications and public websites. You plan to create a new security principal named User1.
The principal must have access to select all current and future objects in a database named Reporting. The activity and authentication of the database user must be limited to the Reporting database.
You need to create the new security principal.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Answer:

Explanation: Step 1, Step 2:
First you need to create a login for SQL Azure, it's syntax is as follows: CREATE LOGIN username WITH password='password';
This command needs to run in master db. Only afterwards can you run commands to create a user in the database.
Step 3:
Users are created per database and are associated with logins. You must be connected to the database in where you want to create the user. In most cases, this is not the master database. Here is some sample Transact-SQL that creates a user:
CREATE USER readonlyuser FROM LOGIN readonlylogin; References:https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/

NEW QUESTION 20
You use a Microsoft SQL Server 2014 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit.
(Click the Exhibit button.)

You write the following Transact-SQL query:

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail.
You need to improve the performance of the query. What should you do?

• A. Use a FORCESCAN hint in the query.
• B. Add a clustered index on SalesOrderId in SalesOrderHeader.
• C. Use a FORCESEEK hint in the query.
• D. Update statistics on SalesOrderId on both tables.

Answer: D

Explanation: New statistics would be useful.
The UPDATE STATISTICS command updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.
References:
http://msdn.microsoft.com/en-us/library/ms187348.aspx