Get Full Version of the Exam
http://www.EnsurePass.com/70-762.html
Question No.81
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have a database that contains a table named Employees. The table stored information about the employees of your company.
You need to implement the following auditing rules for the Employees table:
Record any changes that are made to the data in the Employees table. Customize the data recorded by the audit operations.
Solution: You implement a stored procedure on the Employees table. Does the solution meet the goal?
-
Yes
-
No
Correct Answer: B
Explanation:
We should use table-valued functions, not procedures, to customize the recorded change data. References: https://msdn.microsoft.com/en-us/library/cc645858.aspx
Question No.82
Note: This question is part of a series of questions that use the same or similar answer choices. An Answer choice may be correct for more than one question in the series. Each question independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.
You must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.
You need to ensure that the performance of each instance is consistent for the same queried and query plans.
What should you do?
-
Create a sys.dm_os_waiting_tasks query.
-
Create a sys.dm_exec_sessions query.
-
Create a Performance Monitor Data Collector Set.
-
Create a sys.dm_os_memory_objects query.
-
Create a sp_configure `max server memory#39; query.
-
Create aSQL Profiler trace.
-
Create asys.dm_os_wait_stats query.
-
Create an Extended Event.
Correct Answer: H
Explanation:
Advanced Viewing of Target Data from Extended Events in SQL Server
When your event session is currently active, you might want to watch the event data in real time, as it is received by the target.
Management gt; Extended Events gt; Sessions gt; [your-session] gt; Watch Live Data.
The query_post_execution_showplan extended event enables you to see the actual query plan in the SQL Server Management Studio (SSMS) UI. When the Details pane is visible, you can see a graph of the query plan on the Query Plan tab. By hovering over a node on the query plan, you cansee a list of property names and their values for the node.
References: https://msdn.microsoft.com/en-us/library/mt752502.aspx
Question No.83
HOTSPOT
You are analyzing the performance of a database environment. You need to find all unused indexes in the current database.
How should you complete the Transact-SQL statement?
To answer, select the appropriate Transact-SQL segments in the answer area.
Correct Answer:
Question No.84
DRAG DROP
You have a database named MyDatabase. You must monitor all the execution plans in XML format by using Microsoft SQL Trace. The trace must meet the following requirements:
Capture execution plans only for queries that run the MyDatabase database. Filter out plans with event duration of less than or equal to 100 microseconds. Save trace results to a disk on the server.
You need to create the trace.
In which order should you arrange the Transact-SQL segments to develop the solution? To answer, move all Transact-SQL segments to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.
DECLARE @traceEventId int = 122;
DECLARE @traceColumnIdForTextData int = 1; DECLARE @durationFilter bigint = 100 DECLARE @databaseID int;
SELECT @databaseId = DB_ID(`MyDatabase#39;);
Correct Answer:
Question No.85
HOTSPOT
You create a database table named FactSales by running the following Transact-SQL statements:
You must optimize the indexes without making changes to the ix_FactSales_EmployeeKey index. You need to implement a columnstore index for the table.
How should you complete the Transact-SQL code?
To answer, select the appropriate Transact-SQL segments in the answer area. NOTE: Each correct selection is worth one point.
Correct Answer:
Question No.86
You maintain a Microsoft Azure SQL Database instance.
You grant User1 the SELECT and EXECUTE permissions for all objects in the dbo schema. You must create a stored procedure that allows User1 to view the following information:
details for each connection to the database
a list of all active user connections and internal tasks
You need to create the stored procedure for User1 and ensure that User1 can run the stored procedure without any error.
How should you complete the Transact-SQL statements?
To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact- SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: More than one combination of answer choices is correct. You will receive credit for any of the correct combinations you select.
Correct Answer:
Question No.87
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database named DB1. There is no memory-optimized filegroup in the database.
You have a table and a stored procedure that were created by running the following Transact- SQL statements:
The Employee table is persisted on disk. You add 2,000 records to the Employee table.
You need to create an index that meets the following requirements:
Optimizes the performance of the stored procedure. Covers all the columns required from the Employee table. Uses FirstName and LastName as included columns.
Minimizes index storage size and index key size. What should you do?
-
Create a clustered index on the table.
-
Create a nonclustered index on the table.
-
Create a nonclustered filtered index on the table.
-
Create a clustered columnstore index on the table.
-
Create a nonclustered columnstore index on the table.
-
Create a hash index on the table.
Correct Answer: B
Explanation:
https://technet.microsoft.com/en-us/library/jj835095(v=sql.110).aspx
Question No.88
You suspect deadlocks on a database.
Which two trace flags in the Microsoft SQL Server error log should you locate? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A. |
1204 |
B. |
1211 |
C. |
1222 |
D. |
2528 |
E. |
3205 |
Correct Answer: AC
Explanation:
Trace flag 1204 returns the resources and types of locks participating in a deadlock and also the current command affected.
Trace flag 1222 returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.
References:
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags- transact-sql?view=sql-server-2017
Question No.89
You have a table named Person.Address that includes the following columns:
AddressID AddressLine1 AddressLine2 City StateProvinceID PostakCode
RowGuid ModifiedDate
You need to create a nonclustered index on PostalCode named IX_Address_PostalCode that uses the following included columns:
AddressLine1 AddressLine2 City StateProvinceID
How should you complete the Transact-SQL statement?
To answer, select the appropriate Transact_SQL segments in the answer are.
Correct Answer:
Question No.90
Case study Background
You have a database named HR1 that includes a table named Employee.
You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run. You must monitor the database to identify issues that prevent the reports from running.
You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.
Employee Table
You use the following Transact-SQL statements to create, configure, and populate the Employee table:
Application
You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:
UspA: This stored procedure updates only the EmployeeStatus column. UspB: This stored procedure updates only the EmployeePayRate column.
The application uses views to control access to data. Views must meet the following requirements:
Allow user access to all columns in the tables that the view accesses. Restrict updates to only the rows that the view returns.
You observe that the four indexes require a large amount of disk space. You must reduce the amount of disk space that the indexes are using.
You need to create a single index to replace the existing indexes.
Which three Transact-SQL segments should you use to develop the solution?
To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.
Correct Answer:
Get Full Version of the Exam
70-762 Dumps
70-762 VCE and PDF