Study with 070-459 Actual Test Questions

You have a query that is used by a reporting dashboard.
Users report that the query sometimes takes a long time to run.
You need to recommend a solution to identify what is causing the issue.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.

• A. Create a job that queries the sys.dm_os_waiting_tasks dynamic management view.
• B. Enable trace flag 1204, and then create an alert.
• C. Set the blocked process threshold, and then run SQL Server Profiler.
• D. Set the blocked process threshold, and then create an alert.

You need to recommend a disaster recovery strategy for the Inventory database.
What should you include in the recommendation?

• A. SQL Server Failover Clustering
• B. Log shipping
• C. Always on availability groups
• D. Peer-to-peer replication

You have a text file that contains an XML Schema Definition (XSD). You have a table named Schema1.Table1.
You have a stored procedure named Schema1.Proc1 that accepts an XML parameter named Param1.
You need to store validated XML data in Schema1.Table1. The solution must ensure that only valid XML data is accepted by Param1.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A. Define an XML column in Table1 by using an XML schema collection.
• B. Use the modify method to insert the XML schema into each row of the XML column in Table1.
• C. Declare Param1 var1 as type XML and associate the variable to the XML schema collection.
• D. Create an XML schema collection in the database from the text file.

You have a SQL Server 2012 database named Database1. You execute the following code:

You insert 3 million rows into Sales.
You need to reduce the amount of time it takes to execute Proc1.
What should you do?

• A. Change the WHERE clause to the following: WHERE OrderDate BETWEEN
  CAST(@date1,char(10))
  AND CAST(@date2,char(10))
• B. Run the following:
  DROP INDEX IX_Sales_OrderDate;
  GO
  CREATE INDEX IX_Sales_OrderDate ON Sales(OrderDate);
  GO
• C. Remove the ORDER BY clause from the stored procedure.
• D. Run the following: ALTER TABLE Sales ALTER COLUMN OrderDate datetime NOT
  NULL;

DRAG DROP
You are planning a SQL Server 2012 deployment.
The corporate security policy states that all Windows servers must be installed in an environment that reduces the attack surface.
You plan to deploy two SQL Server instances named SQL1 and SQL2 that must meet the following requirements:
- SQL1 will host databases for a line-of-business application that requires 32 GB of RAM - SQL2 will host SQL Server Reporting Services and application databases that require 16 GB of RAM.
You need to recommend an operating system for each SQL Server instance. The solution must minimize licensing costs.
What should you recommend?
To answer, drag the appropriate operating system to the correct server in the answer area.