70-762 무료 덤프문제 온라인 액세스

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 collect query performance data while minimizing the performance impact on the SQL Server.
What should you do?

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

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 need to identify the indexes that are not being used so that you can remove them from the database.
Solution: You run the sys.dm_db_index_usage_stats dynamic management view.
Does the solution meet the goal?

• A.No
• B.Yes

You are designing a data warehouse that will be clustered across four servers. Several of the tables in the data warehouse contain transient data that you can rebuild as needed.
The schema for the data changes periodically. You must minimize the work required to make schema changes and deploy those changes across the server farm. Administrators must be able to make the following schema changes:
* Adding columns to tables
* Removing columns from tables
* Adding tables
* Removing tables
Changes to the transient data are done as singleton operations. You must make the data available on all the servers in the server farm. Data movement between servers must occur in real time or near real time.
You must be able to run SELECT statements against the data from any server at any time.
You need to configure the environment.
Which technologies should you implement? To answer, select the appropriate technologies in the answer area.
NOTE: Each correct selection is worth one point.

정답:

Explanation

Box 1: Durable in-Memory OLTP
Databases containing memory-optimized tables, with or without native compiled stored procedures, are fully supported with Always On Availability Groups. There is no difference in the configuration and support for databases which contain In-Memory OLTP objects as compared to those without.
Box 2: Always on Availability groups
The Always On availability groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Always On availability groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.
An availability group fails over at the level of an availability replica.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/high-availability-support-for-in-memo
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have a database named Sales that contains the following database tables: Customer, Order, and Products.
The Products table and the Order table are shown in the following diagram.

The customer table includes a column that stores the data for the last order that the customer placed.
You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records.
Storage requirements for the Leads table must be minimized.
The Leads table must include the columns described in the following table.

The data types chosen must consume the least amount of storage possible.
You need to select the appropriate data types for the Leads table.
In the table below, identify the data type that must be used for each table column.
NOTE: Make only one selection in each column.

정답:

Explanation

Bit is a Transact-SQL integer data type that can take a value of 1, 0, or NULL.
Smallint is aT ransact-SQL integer data type that can take a value in the range from -32,768 to 32,767.
int, bigint, smallint, and tinyint (Transact-SQL)
Exact-number data types that use integer data.

References: https://msdn.microsoft.com/en-us/library/ms187745.aspx
https://msdn.microsoft.com/en-us/library/ms177603.aspx

You need to implement triggers to automate responses to the following events:
* SQL Server logons
* Database schema changes
* Database updates
Which trigger types should you use? To answer, drag the appropriate trigger types to the appropriate scenarios.
Each trigger type 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: Each correct selection is worth one point.

정답:

Explanation

Box 1: LOGON
Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server.
Box 2: INSTEAD OF INSERT
An "INSTEAD of trigger" is executed instead of the original operation, and not combining with the operation.
INSTEAD OF triggers override the standard actions of the triggering statement. It can be used to bypass the statement and execute a whole different statement, or just help us check and examine the data before the action is done.
Box 3: DDL
DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database Note:
You can write triggers that fire whenever one of the following operations occurs:
* DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user
* DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database
* Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database References:
https://docs.oracle.com/cd/B19306_01/server.102/b14220/triggers.htm
https://social.technet.microsoft.com/wiki/contents/articles/28152.t-sql-instead-of-triggers.aspx
https://docs.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers?view=sql-server-2017