Questions Ask for 70-464 braindumps

Act now and download your Microsoft 70-464 test today! Do not waste time for the worthless Microsoft 70-464 tutorials. Download Abreast of the times Microsoft Developing Microsoft SQL Server 2012 Databases exam with real questions and answers and begin to learn Microsoft 70-464 with a classic professional.

2016 Oct 70-464 braindumps:

Q121. You need to implement a solution that addresses the performance issues of the usp_GetOrdersByProduct stored procedure. 

Which statement should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:


Q122. You need to identify the cause of the page split issues. 

Which SQL Server feature should you use? 

A. DBCC REINDEX 

B. SQL Server Profiler 

C. Extended Events 

D. DBCC TRACEOFF 

Answer:

46. You need to design a solution that meets the refactoring requirements. 

Which type of object should you include in the solution? 

A. An indexed view 

B. An aggregate function 

C. A distributed view 

D. A table-valued function 

Answer:


Q123. You need to implement a solution that meets the locking requirements. Which line of code should you modify? 

A. Change line 07 in usp_UpdateOpening to: UPDATE Openings WITH (UPDLOCK) 

B. Change line 09 in usp_GetOpenings to: FROM Openings o (ROWLOCK) 

C. Change line 07 in usp_UpdateOpening to: UPDATE Openings WITH (READPAST) 

D. Change line 09 in usp_GetOpenings to: FROM Openings o (NOLOCK) 

Answer:


Q124. You have a database that is accessed by 300 concurrent users. 

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

. Provide a representation of the deadlock in XML format. 

. Minimize the impact on the server. 

What should you create? 

A. A SQL Server Profiler trace 

B. A script that enables trace flags 

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

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

Answer:

Explanation: Analyze Deadlocks with SQL Server Profiler Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis. To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio. 


Q125. Topic 8) 

You plan to create a custom aggregation function named Function1. 

You plan to deploy Function1 to SQL Server 2012. 

You need to ensure that Function1 can access a web service. The solution must minimize 

the number of changes made to the database. 

You create a Microsoft .NET Framework class that contains the definition of Function1. 

You upload a certificate to SQL Server. 

What three tasks should you perform next? 

To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. 

Answer: 


Q126. You have a database for a mission-critical web application. The database is stored on a SQL Server 2012 instance and is the only database on the instance. 

The application generates all T-SQL statements dynamically and does not use stored procedures. 

You need to maximize the amount of memory available for data caching. 

Which advanced server option should you modify? 

A. Optimize for Ad hoc Workloads 

B. Enable Contained Databases 

C. Allow Triggers to Fire Others 

D. Scan for Startup Procs 

Answer:


Q127. You execute usp_SelectEmployeesByName multiple times, passing strings of varying lengths to @LastName. You discover that usp_SelectEmployeesByName uses inefficient execution plans. 

You need to update usp_SelectEmployeesByName to ensure that the most efficient execution plan is used. 

What should you add at line 31 of StoredProcedures.sql? 

A. OPTION (ROBUST PLAN) 

B. OPTION (OPTIMIZE FOR UNKNOWN) 

C. OPTION (KEEP PLAN) 

D. OPTION (KEEPFIXED PLAN) 

Answer:

Explanation: 

http://msdn.microsoft.com/en-us/library/ms181714.aspx 


Abreast of the times exam 70-464 dumps:

Q128. You have a server that has SQL Server 2012 installed. 

You need to identify which parallel execution plans are running in serial. 

Which tool should you use? 

A. Performance Monitor 

B. Database Engine Tuning Advisor 

C. Data Profile Viewer 

D. Extended Events 

Answer:

Explanation: 

http://msdn.microsoft.com/en-us/library/bb677278.aspx 

http://msdn.microsoft.com/en-us/library/bb630282.aspx http://www.sql-server-performance.com/2006/query-execution-plan-analysis/ http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sqlserver/ http://www.sqlservercentral.com/articles/SQL+Server+2012/At+last%2c+execution+plans+ show+true+thread+reservations./92458/ http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-executionplan.aspx http://sqlblog.com/blogs/paul_white/archive/2012/05/02/parallel-row-goals-gone-rogue.aspx http://msdn.microsoft.com/en-us/library/bb895310.aspx http://msdn.microsoft.com/en-us/library/bb895313.aspx http://msdn.microsoft.com/en-us/library/hh231122.aspx 


Q129. Topic 8) 

You have a SQL Server 2012 database named Database1. Database1 has a data file named Database1_data.mdf and a transaction log named Database1jog.ldf. Database1_data.mdf is 1.5 GB. Database1jog.ldf is 1.5 terabytes. 

A full backup of Database1 is performed every day. 

You need to reduce the size of the log file. The solution must ensure that you can perform transaction log backups in the future. 

Which code segment should you execute? 

To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order. 

Answer: 


Q130. 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. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:


Q131. Topic 8) 

Your network contains a server named Server1 that runs SQL Server 2012. Server1 contains an instance named Instance1. Instance1 contains a database named ContentDatabase. 

ContentDatabase uses transaction log backups. 

The recovery model of ContentDatabase is set to FULL. 

You need to shrink the ContentDatabase_Log log file to 10 MB. The solution must ensure that you can continue to back up the transaction log. 

Which three code segments should you execute? 

To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order. 

Answer: 


Q132. Topic 8) 

You have a SQL Server 2012 database named database1. 

Users report that queries that usually take less than one second to execute, take more than 

30 seconds to execute. 

You need to view the server resource consumption when the queries are executed. 

What should you do? 

To answer, move the appropriate actions from the list of actions to the answer area and 

arrange them in the correct order. 

Answer: 


Q133. You need to modify usp_SelectSpeakersByName to support server-side paging. The solution must minimize the amount of development effort required. 

What should you add to usp_SelectSpeakersByName? 

A. A table variable 

B. An OFFSET-FETCH clause 

C. The ROWNUMBER keyword 

D. A recursive common table expression 

Answer:

Explanation: 

http://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-serverpaging-methods/ http://msdn.microsoft.com/en-us/library/ms188385.aspx http://msdn.microsoft.com/en-us/library/ms180152.aspx http://msdn.microsoft.com/en-us/library/ms186243.aspx http://msdn.microsoft.com/en-us/library/ms186734.aspx http://www.sqlserver-training.com/how-to-use-offset-fetch-option-in-sql-server-order-byclause/-http://www.sqlservercentral.com/blogs/juggling_with_sql/2011/11/30/using-offset-and-fetch/ 


Q134. You execute IndexManagement.sql and you receive the following error message: "Msg 512, Level 16, State 1, Line 12 

Subquery returned more than 1 value. This is not permitted when the subquery follows =,! 

=, <, <= ,>, > = or when the subquery is used as an expression." 

You need to ensure that IndexManagement.sql executes properly. 

Which WHILE statement should you use at line 18? 

A. WHILE SUM(@RowNumber) < (SELECT @counter FROM @indextable) 

B. WHILE @counter < (SELECT COUNT(RowNumber) FROM @indextable) 

C. WHILE COUNT(@RowNumber) < (SELECT @counter FROM @indextable) 

D. WHILE @counter < (SELECT SUM(RowNumber) FROM @indextabie) 

Answer:


Q135. Topic 8) 

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: 

. Identify unused indexes. 

. 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.) 

Answer: 



see more 70-464 dumps