[Verified] 70-469 Microsoft practice test 136-150 (Mar 2016)

Exam Code: 70-469 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Recertification for MCSE: Data Platform
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-469 Exam.

2016 Mar 70-469 Study Guide Questions:

Q136. 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? 


A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: A 


Q137. 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. Set the blocked process threshold, and then run SQL Server Profiler. 

B. Set the blocked process threshold, and then create an alert. 

C. Enable trace flag 1204, and then create an alert. 

D. Create a job that queries the sys.dm_os_waiting_tasks dynamic management view. 

Answer: B 


Q138. You are creating a table named Orders. 

You need to ensure that every time a new row is added to the Orders table, a table that is used for auditing is updated. 

What should you use? 

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

A. A Data Definition Language (DDL) trigger 

B. A DEFAULT constraint 

C. A CHECK constraint 

D. A FOREIGN KEY constraint 

E. A data manipulation language (DML) trigger 

Answer: E 


Q139. You need to create the object used by the parameter of usp_InsertSessions. 

Which statement should you use? 

A. CREATE SCHEMA SessionDataTable 

B. CREATE TYPE SessionDataTable AS Table 

C. CREATE TABLE SessionDataTable 

D. CREATE XML SCHEMA COLLECTION SessionDataTable 

Answer: A 


Q140. 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: A 


70-469 practice question

Latest 70-469 exam:

Q141. You deploy a SQL Server instance named SQLProd that uses SQL Server 2014. 

You need to recommend a solution to monitor the transactions that are running currently against SQLProd. The solution must minimize the amount of custom code required. 

What should you recommend? 

A. Statistics 

B. A dynamic management view 

C. A trigger 

D. User-defined views 

Answer: B 


Q142. You are testing disaster recovery procedures. 

When you attempt to restore ProductsDB to another server, you receive the following error message: "Msg 33111, Level 16, State 3, Line 5 Cannot find server certificate with thumbprint ' 

0x9D876A3468B911ElBA4CFCBF4724019B\ 

Msg 3013, Level 16, State 1, Line 5 

RESTORE DATABASE is terminating abnormally." 

You need to ensure that you can restore ProductsDB to another server. 

Which code segment should you execute on the other server? 


A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: D 


Q143. You need to resolve the performance issues of the usp_ExportOpenings stored procedure. The solution must minimize the amount of hard disk space used. 

Which statement should you execute on DB1? 

A. EXEC sp_dboption 'DB1', 'auto create statistics', 'TRUE' 


B. CREATE INDEX IX_Exp_Openings ON Openings(PostDate, FilledDate) INCLUDE 

(Description, Title, Salary); 


C. CREATE INDEX IX_Exp_Openings ON Openings(PostDate) INCLUDE (Description, 

Title, 

Salary) WHERE FilledDate IS NULL; 


D. EXEC sp_recompile 'usp_ExportOpenings' 


Answer: C 


Q144. Your company has a main office in London and a branch office in New York. 

Your network contains a server named Server5 that has SQL Server 2012 installed. Server5 contains a database name ContentDB and a table named ContentTable. 

You add an additional server named Server9 that runs SQL Server 2012. 

You need to create a distributed partitioned view. The solution must minimize the amount of network traffic. 

What should you do? (Each correct answer presents part of the solution. Choose all that apply.) 

A. Create the view on Server5. 

B. Add Server9 as a linked server. 

C. Create the view on Server9. 

D. Add the Customers table to Server9. 

E. Add Server9 as a Distributor. 

F. Remove the Customers table from Server5. 

Answer: ABCD 


Q145. You have a SQL Server 2012 environment that contains two servers. The servers are configured as shown in the following table. 


After the failover is complete, a user receives the following error message when connecting to DB1 on Server2: 

"Msg 916, Level 14, State 1, Line 1 The server principal "Account1" is not able to access the database "DB1" under the current security context." 

You verify that there is a server login for Account1 on Server2. 

You need to ensure that Account1 can connect to DB1. 

What should you do? 

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

A. Update the SID for Account1 on DB1. 

B. Add Account1 to the db_datareader role. 

C. Create a new database user on DB1. 

D. Implement Windows authentication. 

Answer: B 


70-469 free exam

High value 70-469 test question:

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


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

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

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

B. Execute the sp_recompile 'dbo.GetCustomersByDate' statement. 

C. Execute the ALTER INDEXIX_Customers_CreationDate WITH REBUILD statement. 

D. Modify the stored procedure to include the OPTIMIZE FOR('1/1/2008') query hint. 

Answer: A 


Q147. You use SQL Server 2014. The physical server is a dedicated database server that has 120GB of RAM available. There is approximately 50GB of storage space available on a slow local disk. 

You create a new stored procedure. You decide you need to temporarily hold approximately 300,000 rows from two tables, from which you will compute two complex business scores. 

The stored procedure will use temporary storage defined as follows: The code will make several passes through the data, applying complex calculations before writing the data to a permanent disk-based table in the same database from which it reads the data. 


For this stored procedure, you need to deal with temporary data in the most efficient way to minimize physical disk pressure. 

What should you do? More than one answer choice may achieve the goal. Select the BEST answer. 


A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: A


Q148. You discover that usp.SelectSpeakersByName executes slowly if usp_UpdateSpeakerName executes simultaneously. 

You need to minimize the execution time of usp.SelectSpeakersByName. The solution must not affect the performance of the other stored procedures. 

What should you update? 

A. Usp_UpdateSpeakerName to use the NOLOCK query hint 

B. Usp_UpdateSpeakerName to use snapshot isolation 

C. Usp_SelectSpeakersByName to use the NOLOCK query hint 

D. Usp_SelectSpeakersByName to use snapshot isolation 

Answer: C 


Q149. You are the administrator for a SQL Server 2014 instance that stores the data for an online transaction processing sales system. 

The company takes full backups every week; differential backups on the days with no full backups; and hourly transaction backups. These backups are stored on a backup server in the company's data center. 

Every week, the company places the full backup on a tape and sends it to a third-party backup storage system. 

The company is worried that a disaster might occur that could destroy their computer center and cause them to lose orders. 

You need to determine the best method for providing the smallest amount of data loss and downtime without leasing or purchasing additional physical locations. 

What should you do? More than one answer choice may achieve the goal. Select the BEST answer. 

A. Set up SQL Server Always On with a SQL Azure database as a replica. 

B. Set up SQL Server Always On by using a SQL Server on a Windows Azure Virtual Machine. 

C. Put the differential backup on tape and send it to the third-party backup storage system. 

D. Use the Microsoft SQL Server Backup to Microsoft Windows Azure Tool to direct all backups to a different geographical location. 

Answer: D 


Q150. You have a database named Database1. Database1 has two stored procedures named Proc1 and Proc2 and a table named Table1. Table1 has millions of rows. 

Proc1 updates data in Table1. Proc2 reads data from Table1. 

You discover that when Proc1 is executed to update more than 4,000 rows, Proc2 is blocked. The block affects all rows, including those that are not being updated by Proc1. 

You need to ensure that when Proc1 is executing, Proc2 can access the data in Table1 that Proc1 is not updating. 

What should you change Proc1 to do? 

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

A. Update less than 4,000 rows simultaneously. 

B. Use the PAGLOCK table hint. 

C. Wait for Proc2 to complete. 

D. Use the ROWLOCK table hint. 

Answer: A 



see more 70-469 - Recertification for MCSE: Data Platform

Microsoft 70-469 Certification Sample Questions and Answers: http://www.braindumpsall.net/70-469-dumps/

P.S. New 70-469 dumps PDF: http://www.4easydumps.com/70-469-dumps-download.html