DRAG DROP

You administer a Microsoft SQL Server database named Contoso.

You create a stored procedure named Sales.ReviewInvoice by running the following Transact-SQL statement:

You need to create a Windows-authenticated login named ContosoSearch and ensure that ContosoSearch can run the Sales.ReviewInvoices stored procedure.

Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Reveal Solution Hide Solution

Correct Answer:

You have a database that stores information for a shipping company.

You create a table named Customers by running the following Transact-SQL statement. (Line numbers are included for reference only.)

You need to ensure that salespeople can view data only for the customers that are assigned to them.

Which Transact-SQL segment should you insert at line 07?

Reveal Solution Hide Solution

You manage a Microsoft SQL Server environment.

You plan to encrypt data when you create backups.

You need to configure the encryption options for backups.

What should you configure?

Reveal Solution Hide Solution

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?

Reveal Solution Hide Solution

HOTSPOT

You deploy a Microsoft SQL Server instance to support a global sales application. The instance includes the following tables: TableA and TableB.

TableA is a partitioned table that uses an incrementing integer number for partitioning. The table has millions of rows in each partition. Most changes to the data in TableA affect recently added

data. The UPDATE STATISTICS for TableA takes longer to complete than the allotted maintenance window.

Thousands of operations are performed against TableB each minute. You observe a large number of Auto Update Statistics events for TableB.

You need to address the performance issues with each table.

In the table below, identify the action that will resolve the issues for each table. NOTE: Make only one selection in each column.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Table A: Auto_update statistics off

Table A does not change much. There is no need to update the statistics on this table.

Table B: SET AUTO_UPDATE_STATISTICS_ASYNC ON

You can set the database to update statistics asynchronously:

ALTER DATABASE YourDBName

SET AUTO_UPDATE_STATISTICS_ASYNC ON

If you enable this option then the Query Optimizer will run the query first and update the outdated statistics afterwards. When you set this option to OFF, the Query Optimizer will update the outdated statistics before compiling the query. This option can be useful in OLTP environments

References: https://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/

DRAG DROP

You administer a database that is used for reporting purposes. The database has a large fact table that contains three hundred million rows. The table includes a clustered columnstore index and a nonclustered index on the Product ID column. New rows are inserted into the table every day.

Performance of queries that filter the Product ID column have degraded significantly.

You need to improve the performance of the queries.

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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Step 1: Drop the clustered columnstore index

Step 2: Create a clustered rowstore index on ProductID.

Rowstore indexes perform best on queries that seek into the data, searching for a particular value, or for queries on a small range of values. Use rowstore indexes with transactional workloads since they tend to require mostly table seeks instead of table scans.

Step 3: Create a nonclustered index on ProductID

HOTSPOT

You are the database administrator of a Microsoft SQL Server instance. Developers are writing stored procedures to send emails using sp_send_dbmail. Database Mail is enabled.

You need to configure each account’s profile security and meet the following requirements:

– Account SMTP1_Account must only be usable by logins that have been given explicit permissions to use the SMTP1_profile.

– Account SMTP2_Account must only be usable by logins who are a member of the [DatabaseMailUserRole] role in msdb.

In the table below. identify the profile type that must be used for each account.

NOTE: Make only one selection in each column.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

SMTP1_Account1: Private Profile

When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user. If the user does not have a default private profile, sp_send_dbmail uses the default public profile for the msdb database.

SMTP1_Account2: Default Profile

Execute permissions forsp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database.

References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql

HOTSPOT

You have a Microsoft SQL Server instance that hosts a database named DB1 that contains 800 gigabyte (GB) of data. The database is used 24 hours each day. You implement indexes and set the value of the Auto Update Statistics option set to True.

Users report that queries take a long time to complete.

You need to identify statistics that have not been updated for a week for tables where more than 1,000 rows changed.

How should you complete the Transact-SQL statement? To answer, configure the appropriate Transact-SQL segments in the answer area.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Box 1: stats_date

See example below.

Box 2: rowmodctr

See examplebelow.

Box 3: stats_date

You need to identify statistics that have not been updated for a week.

Box 4: rowmodctr

You need to identify that more than 1,000 rows changed.

Rowmodctr counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.

Example: We will query every statistics object which was not updated in the last day and has rows modified since the last update. We will use the rowmodctr field of sys.sysindexes because it shows how many rows were inserted, updated or deleted since the last update occurred. Please note that it is not always 100% accurate in SQL Server 2005 and later, but it can be used to check if any rows were modified.

–Get the list of outdated statistics

SELECTOBJECT_NAME(id),name,STATS_DATE(id, indid),rowmodctr

FROM sys.sysindexes

WHERE STATS_DATE (id, indid)<=DATEADD(DAY,-1,GETDATE())

AND rowmodctr>0

AND id IN (SELECT object_id FROM sys.tables)

GO

After collecting this information, we can decide which statistics require an update.

References: https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysindexes-transact-sql

https://www.mssqltips.com/sqlservertip/2628/how-to-find-outdated-statistics-in-sql-server-2008/

DRAG DROP

You are the database administrator for a Microsoft SQL Server instance. You develop an Extended Events package to look for events related to application performance.

You need to change the event session to include SQL Server errors that are greater than error severity 15.

Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Step 1: ALTER EVENT SESSION Contoso1 ON SERVER

Step 2: ADD EVENT …

Step 3: (ACTION …

Step 4: WHERE…

Step 5: ) GO

Example: To start an Extended Events sessions in order to trap SQL Server errors with severity greater than 10,just run the following script:

CREATE EVENT SESSION [error_trap] ON SERVER

ADD EVENT sqlserver.error_reported

(

ACTION (package0.collect_system_time,package0.last_error,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username, sqlserver.plan_handle,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack,sqlserver.username)

WHERE ([severity]>10)

)

ADD TARGET package0.event_file

(

SET filename=N’D:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLXEventserror_trap.xel’

)

WITH

(

STARTUP_STATE=OFF

)

GO

References: http://sqlblog.com/blogs/davide_mauri/archive/2013/03/17/trapping-sql-server-errors-with-extended-events.aspx

HOTSPOT

You manage a Microsoft SQL Server environment. A server fails and writes the following event to the application event log: MSG_AUDIT_FORCED_SHUTDOWN

You configure the SQL Server startup parameters as shown in the following graphic:

Use the drop-down menus to select the answer choice that answers each question. NOTE: Each correct selection is worth one point.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Box 1: single-user

The startup option -m starts an instance of SQL Server in single-user mode.

Box 2: sysadmin

Starting SQL Server in single-user mode enables anymember of the computer’s local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.

References: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/database-engine-service-startup-options