QUESTION 11
You are troubleshooting an application that runs a query. The application frequently causes deadlocks. You need to identify which transaction causes the deadlock. What should you do? More than one answer choice may achieve the goal. Select the BEST answer.
A. Query the sys.dm_exec_sessions dynamic management view.
B. Create an extended events session to capture deadlock information.
C. Query the sys.dm_exec_requests dynamic management view.
D. Create a trace in SQL Server Profiler that contains the Deadlock graph event.
Answer: C
QUESTION 12
You plan to create a database. The database will be used by a Microsoft .NET application for a special event that will last for two days. During the event, data must be highly available. After the event, the database will be deleted. You need to recommend a solution to implement the database while minimizing costs. The solution must not affect any existing applications. What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. SQL Server 2012 Enterprise
B. SQL Azure
C. SQL Server 2012 Express with Advanced Services
D. SQL Server 2012 Standard
Answer: B
QUESTION 13
You have a server named Server1 that has 16 processors. You plan to deploy multiple instances of SQL Server 2012 to Server1. You need to recommend a method to allocate processors to each instance. What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.
A. Max Degree of Parallelism
B. Processor affinity
C. Windows System Resource Manager (WSRM)
D. Resource Governor
Answer: B
QUESTION 14
You have a SQL Azure database. You need to identify which keyword must be used to create a view that will be indexed. Which keyword should you identify?
A. DISTINCT
B. DEFAULT
C. SCHEMABINDING
D. VIEW_METADATA
Answer: C
QUESTION 15
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. Use the modify method to insert the XML schema into each row of the XML column in Table1.
B. Define an XML column in Table1 by using an XML schema collection.
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.
Answer: ABD
QUESTION 16
You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP). You discover that many page splits occur when records are inserted or updated in the table. You need to minimize the number of page splits. What should you set from the index options?
A. FILLFACTOR = 0
B. STATISTICS_NORECOMPUTE = ON
C. STATISTICS_NORECOMPUTE = OFF
D. FILLFACTOR = 80
Answer: D
QUESTION 17
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 DEFAULT constraint
B. a Data Definition Language (DDL) trigger
C. a CHECK constraint
D. a FOREIGN KEY constraint
E. a data manipulation language (DML) trigger
Answer: E
QUESTION 18
You have a database named database1. Database developers report that there are many deadlocks. You need to implement a solution to monitor the deadlocks. The solution must meet the following requirements:
– Support real-time monitoring.
– Be enabled and disabled easily.
– Support querying of the monitored data.
What should you implement?
More than one answer choice may achieve the goal. Select the BEST answer.
A. a SQL Server Profiler template
B. an Extended Events session
C. log errors by using trace flag 1204
D. log errors by using trace flag 1222
Answer: B
QUESTION 18
You have a SQL Server 2012 database named DB1. You have a backup device named Device1. You discover that the log file for the database is full. You need to ensure that DB1 can complete transactions. The solution must not affect the chain of log sequence numbers (LSNs). Which code segment should you execute?
A. BACXUP LOG DB1 TO Device1 WITH TRUNCATE_ONLY
B. BACKUP LOG DB1 TO Device1 WITH COPY_ONLY
C. BACKUP LCG DB1 TO Device1 WITH NORECOVERY
D. BACKUP LOG DB1 TC Device1
Answer: D
QUESTION 20
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. Extended Events
D. Data Profile Viewer
Answer: C
If you want to pass Microsoft 70-459 successfully, donot missing to read latest lead2pass Microsoft 70-459 exam questions.
If you can master all lead2pass questions you will able to pass 100% guaranteed.