Microsoft Recertification for MCSE Data Platform Set 2

Question - 15 Maximum mark : - 26

Home
All Exams
Microsoft Recertification for MCSE Data Platform Online Mock Tests
Microsoft Recertification for MCSE Data Platform Set 2

You need to implement a solution that addresses the page split issues. Which statement should you execute?

ALTER INDEX IX_Orders_ShipDate ON Orders REBUILD WITH (PAD_INDEX=OFF, DROP_EXISTING = ON);

✘

ALTER INDEX IX_Orders_ShipDate ON OrdersREBUILD WITH (FILLFACTOR=50, DROP_EXISTING = ON);

✘

ALTER INDEX IX_Orders_ShipDate ON OrdersREBUILD WITH (FILLFACTOR = 0, DROP_EXISTING = ON);

✘

ALTER INDEX IX_Orders_ShipDate ON OrdersREBUILD WITH (PAD_INDEX=ON, DROP_EXISTING = ON);

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to ensure that usp_AddXMLOrder can be used to validate the XML input from the retailers. Which parameters should you add to usp_AddXMLOrder on line 04 and line 05? (Each correct answer presents part of the solution. Choose all that apply.)

@schema varbinary(100). @items varchar(max). @schema sysname. @items varbinary(max). @items xml.

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to implement a solution that addresses the index monitoring requirements. What should you do?

Schedule a SQL Server Agent job that saves data from the dynamic management views to a table in the database. ✘ Create a SQL Server Audit that saves data to a log file, and then create a SQL Server Audit Specification that gathers data from the DATABASE_OPERATIONgroup. ✘ Create a performance monitor Data Collector Set (DCS) that monitors the SQL Server counters. ✘ Schedule a SQL Server Profiler trace, and then save the trace data to a table in the database. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to implement a solution that solves the performance issues of usp_GetOrdersAndItems. Which statements should you execute?

CREATE INDEX IX_Orders_Active ON Orders(ShipDate, DeliveryDate, Amount)

✘

CREATE INDEX IX_Orders_Active ON Orders(DeliveryDate) INCLUDE(Amount)WHERE ShipDate IS NULL

✘

CREATE INDEX IX_Orders_Active ON Orders(DeliveryDate, Amount) WHERE ShipDateIS NULL

✘

CREATE INDEX IX_Orders_Active ON Orders(ShipDate, DeliveryDate)INCLUDE( Amount)

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to modify usp.GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated. What should you add to usp.GetOrdersAndItems?

Add WITH (NOLOCK) to the end of line 47. ✘ Add SET TRANSACTION ISOLATION LEVEL READ COMMITTED to line 44. ✘ Add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to line 44. ✘ Add WITH (READPAST) to the end of line 47. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to ensure that a new execution plan is used by usp_GetOrdersByProduct each time the stored procedure runs. What should you do?

Execute sp_help usp_GetOrdersByProduct\ ✘ Add WITH (FORCESEEK) to line 69 in usp.GetOrdersByProduct. ✘ Add WITH RECOMPILE to line 64 in usp.GetOrdersByProduct. ✘ Execute sp_recompile usp.GetOrdersByProduct'. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to monitor the health of your tables and indexes in order to implement the required index maintenance strategy. What should you do?

Query system DMVs to monitor avg_chain_length and max_chain_length. Create alerts to notify you when these values converge. ✘ Create a SQL Agent alert when the File Table: Avg time per file I/O request value is increasing. ✘ Query system DMVs to monitor total_bucket_count. Create alerts to notify you when this value increases. ✘ Query system DMVs to monitor total_bucket_count. Create alerts to notify you when this value decreases. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to implement security for the restore and audit process. What should you do?

Grant the COFFECORP\Auditors group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permissions. Grant the COFFECORP\StoreAgent group ALTER ANY CONNECTION and IMPERSONATE ANY LOGIN permissions.

✘

Grant the COFFECORP\Auditors group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permissions. Grant the COFFECORP\StoreAgentgroup CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permissions.

✘

Grant the COFFECORP\Auditors group ALTER ANY CONNECTION and IMPERSONATE ANY LOGIN permissions. Grant the COFFECORP\StoreAgent group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permissions.

✘

Grant the COFFECORP\Auditors group CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permissions. Grant the COFFECORP\StoreAgent group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permissions.

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to modify the stored procedure usp_LookupConcurrentUsers. What should you do?

Add a clustered index to the summary table. ✘ Add a nonclustered index to the summary table. ✘ Add a clustered columnstore index to the summary table. ✘ Use a table variable instead of the summary table. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

10.

You need to optimize the index structure that is used by the tables that support the fraud detection services. What should you do?

Add a hashed nonclustered index to CreateDate. ✘ Add a not hash nonclustered index to CreateDate. ✘ Add a not hash clustered index on POSTransactionId and CreateDate. ✘ Add a hashed clustered index on POSTransactionId and CreateDate. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Prev Next

Congratulations

you have successfully completed the test

Back to Question View Result

Detail Form

We will send your result on your email id and phone no. please fill detail

Name

Email ID Note: Report will be send to the above Email ID

Phone No

Microsoft Recertification for MCSE Data Platform Set 2

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

COMPANY

Products

OTHERS

Partner