Microsoft 70-762 시험

Question No : 1

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.
What should you do?
A. A. Create asys.dm_os_waiting_tasks query.
B. Create a sys.dm_exec_sessions query.
C. Create a Performance Monitor Data Collector Set.
D. Create a sys.dm_os_memory_objects query.
E. Create a sp_configure ‘max server memory’ query.
F. Create a SQL Profiler trace.
G. Create a sys.dm_os_wait_stats query.
H. Create an Extended Event.

답을 확인하기

정답: B
Explanation:
sys.dm_exec_sessions returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more. Use sys.dm_exec_sessions to first view the current system load and to identify a session of interest, and then learn more information about that session by using other dynamic management views or dynamic management functions.
Examples of use include finding long-running cursors, and finding idle sessions that have open transactions.

Question No : 2

Note: This question is part of a series of questions that use the same or similar answer choices. AnAnswer 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 observe that many deadlocks appear to be happening during specific times of the day.
You need to monitor the SQL environment and capture the information about the processes that are causing the deadlocks. Captured information must be viewable as the queries are running.
What should you do?
A. A. Create a sys.dm_os_waiting_tasks query.
B. Create a sys.dm_exec_sessions query.
C. Create a PerformanceMonitor Data Collector Set.
D. Create a sys.dm_os_memory_objects query.
E. Create a sp_configure ‘max server memory’ query.
F. Create a SQL Profiler trace.
G. Create a sys.dm_os_wait_stats query.
H. Create an Extended Event.

답을 확인하기

정답: F
Explanation:
To view deadlock information, the Database Engine provides monitoring tools in the form of two trace flags, and the deadlock graph event in SQL Server Profiler.
Trace Flag 1204 and Trace Flag 1222
When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each nodeinvolved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable bothtrace flags to obtain two representations of the same deadlock event.
References: https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

Question No : 3

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 and optimize the SQL Server to maximize throughput, response time, and overall SQL performance. You need to identify previous situations where a modification has prevented queries from selecting data in tables.
What should you do?

• A.Create a sys.dm_os_waiting_tasks query.
• B.Create a sys.dm_exec_sessions query.
• C.Create a Performance Monitor Data Collector Set.
• D.Create a sys.dm_os_memory_objects query.
• E.Create a sp_configure ‘max server memory’ query.
• F.Create a SQL Profiler trace.
• G.Create a sys.dm_os_wait_stats query.
• H.Create an Extended Event.

답을 확인하기

정답:
Explanation:
sys.dm_os_wait_stats returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.

Question No : 4

DRAG DROP
You are creating a stored procedure which will insert data into the table shown in the Database schema exhibit. (Click the exhibit button.)



You need to insert a new customer record into the tables as a single unit of work.
Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments to the answer area and arrange the, 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.

답을 확인하기

정답:


Explanation:
The entities on the many side, of the 1-many relations, must be added before we add the entities on the 1­side.
We must insert new rows into BusinessEntityContact and BusinessEntityAddress tables, before we insert the corresponding rows into the BusinessEntity and AddressType tables.

Question No : 5

You have a view that includes an aggregate.
You must be able to change the values of columns in the view. The changes must be reflected in the tables that the view uses. You need to ensure that you can update the view.
What should you create?

• A.table-valued function
• B.a schema-bound view
• C.a partitioned view
• D.a DML trigger

답을 확인하기

정답:
Explanation:
When you use the SchemaBinding keyword while creating a view or function you bind the structure of any underlying tables or views. Itmeans that as long as that schemabound object exists as a schemabound object (ie you don’t remove schemabinding) you are limited in changes that can be made to the tables or views that it refers to.
References: https://sqlstudies.com/2014/08/06/schemabinding-what-why/

Question No : 6

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 the series. Information and details provided in a question apply only to that question.
You are developing and application to track customer sales.
You need to create an object that meet the following requirements:
- Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.
- Run written a transaction and roll back if a future occurs.
- Run when a table is created or modified.
What should you create?

• A.extended procedure
• B.CLR procedure
• C.user-defined procedure
• D.DML trigger
• E.scalar-valued function
• F.table-valued function

답을 확인하기

정답:
Explanation:
The common language runtime (CLR) is the heart of the Microsoft .NET Framework andprovides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code.
With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures,
triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

Question No : 7

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 the series. Information and details provided in a question apply only to that question.
You are developing and application to track customer sales.
You need to return the sum of orders that have been finalized, given a specified order identifier. This value will be used in other Transact-SQL statements. You need to create a database object.
What should you create?

• A.extended procedure
• B.CLR procedure
• C.user-defined procedure
• D.DML trigger
• E.scalar-valued function
• F.table-valued function

답을 확인하기

정답:
Explanation:
User-defined scalar functions return a single data value of the type defined in the RETURNS clause.
References: https://technet.microsoft.com/en-us/library/ms177499(v=sql.105).aspx

Question No : 8

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 the series. Information and details provided in a question apply only to that question.
You are developing an application to track customer sales.
You need to create a database object that meets the following requirements:
- Launch when table data is modified.
- Evaluate the state a table before and after a data modification and take action based on the difference.
- Prevent malicious or incorrect table data operations.
- Prevent changes that violate referential integrity by cancelling the attempted data modification.
- Run managed code packaged in an assembly that is created in the Microsoft.NET Framework and located into Microsoft SQL Server.
What should you create?

• A.extended procedure
• B.CLR procedure
• C.user-defined procedure
• D.DML trigger
• E.scalar-valued function
• F.table-valued function

답을 확인하기

정답:
Explanation:
You can create a database object inside SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR). Database objects that can leverage the rich programmingmodel provided by the CLR include DML triggers, DDL triggers, stored procedures, functions, aggregate functions, and types.
Creating a CLR trigger (DML or DDL) in SQL Server involves the following steps:
Define the trigger as a class in a .NETFramework-supported language. For more information about how to program triggers in the CLR, see CLR Triggers. Then, compile the class to build an assembly in the .NET Framework using the appropriate language compiler.
Register the assembly in SQL Server using the CREATE ASSEMBLY statement. For more information about assemblies in SQL Server, see Assemblies (Database Engine).
Create the trigger that references the registered assembly.
References: https://msdn.microsoft.com/en-us/library/ms179562.aspx

Question No : 9

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 the series. Information and details provided in a question apply only to that question.
You are developing an application to track customer sales.
You need to create a database object that meets the following requirements:
- Return a value of 0 if data inserted successfully into the Customers table.
- Return a value of 1 if data is not inserted successfully into the Customers table.
- Support TRY…CATCH error handling
- Be written by using Transact-SQL statements.
What should you create?

• A.extended procedure
• B.CLR procedure
• C.user-defined procedure
• D.DML trigger
• E.scalar-valued function
• F.table-valued function

답을 확인하기

정답:
Explanation:
DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements. DML triggers can be usedto enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.
References: https://msdn.microsoft.com/en-us/library/ms178110.aspx

Question No : 10

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 the series. Information and details provided in a question apply only to that question.
You are developing an application to track customer sales.
You need to create a database object that meets the following requirements:
- Return a value of 0 if data inserted successfully into the Customers table.
- Return a value of 1 if data is not inserted successfully into the Customers table.
- Support logic that is written by using managed code.
What should you create?

• A.extended procedure
• B.CLR procedure
• C.user-defined procedure
• D.DML trigger
• E.DDL trigger
• F.scalar-valued function
• G.table-valued function

답을 확인하기

정답:
Explanation:
DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements.DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.
A CLR trigger is a type of DDL trigger. A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger canalso be a DDL trigger. Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL Server.
References: https://msdn.microsoft.com/en-us/library/ms178110.aspx

Question No : 11

HOTSPOT
You are reviewing the execution plans in the query plan cache.
You observe the following:
- There are a large number of single use plans.
- There are a large number of simple execution plans that use multiple CPU cores.
You need to configure the server to optimize query plan execution.
Which two setting should you modify on the properties page for the Microsoft SQL Server instance? To answer, select the appropriate settings in the answer area.

답을 확인하기

정답:


Explanation:
* Optimize for ad hoc workloads
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.
* Cost Threshold for Parallelism
Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration.
5 means 5 seconds, but is is 5 seconds on a machine internal to Microsoft from some time in the 1990s. There's no way to relate it to execution time on your current machine, so we treat it as a pure number now. Raising it to 50 is a common suggestion nowadays, so that more of your simpler queries run on a single thread.

Question No : 12

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.

답을 확인하기

정답:


Explanation:
Example: Following query helps you to find all unused indexes within database using sys.dm_db_index_usage_stats DMV.
-- Ensure a USE statement has been executed first.
SELECT u.*
FROM [sys].[indexes] i
INNER JOIN [sys].[objects] o ON (i.OBJECT_ID = o.OBJECT_ID)
LEFT JOIN [sys].[dm_db_index_usage_stats] u ON (i.OBJECT_ID = u.OBJECT_ID)
AND i.[index_id] = u.[index_id]
AND u.[database_id] = DB_ID() --returning the database ID of the current database
WHERE o.[type] <> 'S' --shouldn't be a system base table
AND i.[type_desc] <> 'HEAP'
AND i.[name] NOT LIKE 'PK_%'
AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0
AND u.[last_system_scan] IS NOT NULL
ORDER BY 1 ASC
References: https://basitaalishan.com/2012/06/15/find-unused-indexes-using-sys-dm_db_index_usage_stats/

Question No : 13

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.

답을 확인하기

정답:


Explanation:
The following system stored procedures are used to define and manage traces:
* sp_trace_create is used to define a trace and specify an output file location as well asother options that I’ll cover in the coming pages. This stored procedure returns a handle to the created trace, in the form of an integer trace ID.
* sp_trace_setevent is used to add event/column combinations to traces based on the trace ID, as well as toremove them, if necessary, from traces in which they have already been defined.
* sp_trace_setfilter is used to define event filters based on trace columns.
* sp_trace_setstatus is called to turn on a trace, to stop a trace, and to delete a trace definitiononce you’re done with it. Traces can be started and stopped multiple times over their lifespan.
References: https://msdn.microsoft.com/en-us/library/cc293613.aspx

Question No : 14

HOTSPOT
You are maintaining statistics for a database table named tblTransaction. The table contains more than 10 million records.
You need to create a stored procedure that meets the following requirements:
- On weekdays, update statistics for a sample of the total number of records in the table.
- On weekends, update statistics by sampling all rows in the table.
A maintenance task will call this stored procedure daily.
How should you complete the stored procedure? To answer, select the appropriate Transact-SQL segments in the answer area. NOTE: Each correct selection is worth one point.

답을 확인하기

정답:


Explanation:
Box 1: UPDATE STATISTICS
Box 2: SAMPLE 20 PERCENT
UPDATE STATISTICS tablenameSAMPLE number { PERCENT | ROWS }
Specifies the approximate percentage or number of rows in the table or indexed view for the query optimizer to use when it updates statistics. For PERCENT, number can be from 0 through 100 and for ROWS, number can be from0 to the total number of rows.
Box 3: UPDATE STATISTICS
Box 4: WITH FULLSCAN
FULLSCAN computes statistics by scanning all rows in the table or indexed view. FULLSCAN and SAMPLE 100 PERCENT have the same results. FULLSCAN cannot be used with the SAMPLE option.
References: https://msdn.microsoft.com/en-us/library/ms187348.aspx

Question No : 15

You are experiencing performance issues with the database server. You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems.
What should you create?

• A.a System Monitor report
• B.a sys.dm_exec_query_stats dynamic management view query
• C.a sys.dm_exec_session_wait_stats dynamicmanagement view query
• D.an Activity Monitor session in Microsoft SQL Management Studio.

답을 확인하기

정답:
Explanation:
sys.dm_exec_session_wait_stats returns information about all the waits encountered by threads that executed for each session. You can use this view to diagnose performance issues with the SQL Server session and also with specific queries and batches.
Note: SQL Server wait stats are, at their highest conceptual level, grouped into two broad categories: signal waits and resource waits. A signal wait is accumulated by processes running on SQL Server which are waiting for a CPU to become available (so called because the process has “signaled” that it is ready for processing). A resource wait is accumulated by processes running on SQL Server which are waiting fora specific resource to become available, such as waiting for the release of a lock on a specific record.