[Free] 2019(Nov) EnsurePass Microsoft 70-464 Dumps with VCE and PDF 51-60

Get Full Version of the Exam
http://www.EnsurePass.com/70-464.html

Question No.127

You have database objects that were created by using the following script:

image

The dbo.Customers table has 1 million rows.

You discover that usp_GetCustomersByDate takes a long time to complete.

The query plan used by the stored procedure is shown in the exhibit. (Click the Exhibit button.)

image

You need to ensure that usp_GetCustomersByDate completes as quickly as possible. What should you do?

  1. Modify the stored procedure to include the OPTIMIZE FOR UNKNOWN query hint.

  2. Execute the sp_recompile #39;dbo.GetCustomersByDate#39; statement.

  3. Execute the ALTER INDEXIX_Customers_CreationDate WITH REBUILD statement.

  4. Modify the stored procedure to include the OPTIMIZE FOR(#39;1/1/2008#39;) query hint.

Correct Answer: A

Question No.128

You have a database named DB1.

You plan to configure change data capture on the existing tables in DB1.

The database file settings for the DB1 are shown in the exhibit. (Click the Exhibit button.)

image

You need to minimize disk contention caused by change data capture. What should you do?

More than one answer choice may achieve the goal. Select the BEST answer.

  1. Increase the autogrowth value of the database file.

  2. Set the database recovery model to simple.

  3. Increase the autogrowth value of the log file.

  4. Configure change data capture to use to a secondary filegroup.

Correct Answer: D

Question No.129

You have a SQL Server 2012 database named DB1 that is accessed by 650 concurrent users.

You need to log all of the queries to DB1 that become deadlocked. The solution must meet the following requirements:

image

image

Provide a representation of the deadlock in XML format. Minimize the impact on the server.

What should you create?

  1. A SQL Server Profiler trace

  2. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views

  3. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views

  4. A script that enables trace flags

Correct Answer: A

Question No.130

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

image

You have the following query. (Line numbers are included for reference only.)

image

Users report that the query takes a long time to complete. You create a full-text index for the Notes column.

You need to recommend changes to the query to reduce the amount of time it takes for the query to complete.

Which code segment should you use to replace line 03?

  1. WHERE FREETEXT(notes, #39;ll%#39;) AND

  2. INNER JOIN FREETEXTTABLE(dbo.table1, notes, #39;call#39;) AS t2 ON dbo.table1.ID = t2.key WHERE

  3. WHERE CONTAINS(notes, #39;call*#39;) AND

  4. WHERE CONTAINS(notes,*ll%#39;gt; AND

Correct Answer: A

Question No.131

You have a SQL Server 2012 instance. You plan to create an application that uses spatial data. You need to create an object that will support the representation of the surface area of all the oceans. Which code segment should you use?

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: A

Question No.132

You use SQL Server 2012 to store data used by an e-commerce application. You develop a stored procedure named sp1. Sp1 is used to read the price of all the products sold on the e- commerce site. You need to ensure that sp1 can read data even while another transaction is modifying the price of a product. Sp1 must only read committed data. Which transaction isolation level should you use in sp1?

  1. Serializable

  2. Snapshot

  3. Repeatable read

  4. Read committed

Correct Answer: B

Question No.133

DRAG DROP

You have a database named database1. Each table in database1 has one index per column. Users often report that creating items takes a long time.

You need to perform the following maintenance tasks:

image

Identify unused indexes.

image

Identify indexes that need to be defragmented. What should you use?

To answer, drag the appropriate function to the correct management task in the answer area. (Answer choices may be used once, more than once, or not at all.)

image

Correct Answer:

image

Question No.134

DRAG DROP

You have a database that contains three tables. The tables are configured as shown in the following table.

image

You have the following query:

image

The execution plan for the query is shown in the exhibit. (Click the Exhibit button.)

image

You need to create one index to minimize the amount of time it takes to execute the query. What should you do?

To answer, drag the appropriate columns to the correct locations in the answer area. (Answer choices may be used once, more than once, or not at all.)

image

Correct Answer:

image

Question No.135

You have a SQL Server 2012 instance that hosts a single-user database. The database does not contain user-created stored procedures or user-created functions. You need to minimize the amount of memory used for query plan caching. Which advanced server option should you modify?

  1. Scan for Startup Procs

  2. Enable Contained Databases

  3. Optimize for Ad hoc Workloads

  4. Allow Triggers to Fire Others

Correct Answer: C

Question No.136

You execute the following code.

image

After populating the Employees table with 10,000 rows, you execute the following query:

image

You need to reduce the amount of time it takes to execute the query. What should you do?

  1. Partition the table and use the JobTitle column for the partition scheme.

  2. Change SUBSTRING(JobTitle,l,l) = #39;C to JobTitle LIKE #39;c%#39;

  3. Change SUBSTRING (JobTitle, 1,1] = #39;c#39; to LEFT(JobTitle ,1) = #39;c#39;.

  4. Replace IX_Employees with a clustered index.

Correct Answer: B

Get Full Version of the Exam
70-464 Dumps
70-464 VCE and PDF

Leave a Reply