Microsoft Microsoft Certifications 70-762 Questions & Answers

• Question 91:

  You have a database with multiple tables. You must insert data into the tables by using views.

  Which of the following two statements best describes an updatable view?

  NOTE: Each correct selection is worth one point.

  A. The view must not include the primary key of the table.

  B. The view may include GROUP BY or HAVING statements.

  C. The view may include a composite field.

  D. Each view must reference columns from one table.

  E. The view must not include subqueries.

  Correct Answer: BD

  B: The columns being modified must not be affected by GROUP BY, HAVING, or DISTINCT clauses.

  D: Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table. Incorrect Answers:

  A: Primary keys are allowed.

  C: The columns cannot be derived in any other way, such as through the following:

  A computation. The column cannot be computed from an expression that uses other columns.

  E: The restrictions apply to any subqueries in the FROM clause of the view, just as they apply to the view itself.

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-2017

• Question 92:

  You manage a database that supports an Internet of Things (IoS) solution. The database records metrics from over 100 million devices every minute. The database requires 99.995% uptime.

  The database uses a table named Checkins that is 100 gigabytes (GB) in size. The Checkins table stores metrics from the devices. The database also has a table named Archive that stores four terabytes (TB) of data. You use stored

  procedures for all access to the tables.

  You observe that the wait type PAGELATCH_IO causes large amounts of blocking.

  You need to resolve the blocking issues while minimizing downtime for the database.

  Which two actions should you perform? Each correct answer presents part of the solution.

  A. Convert all stored procedures that access the Checkins table to natively compiled procedures.

  B. Convert the Checkins table to an In-Memory OLTP table.

  C. Convert all tables to clustered columnstore indexes.

  D. Convert the Checkins table to a clustered columnstore index.

  Correct Answer: AB

  Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code that access memory-optimized tables. Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure.

  SQL Server In-Memory OLTP helps improve performance of OLTP applications through efficient, memory-optimized data access, native compilation of business logic, and lock- and latch free algorithms. The In-Memory OLTP feature includes memory-optimized tables and table types, as well as native compilation of Transact-SQL stored procedures for efficient access to these tables.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/natively-compiled-stored-procedures

  https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/memory-optimized-tables

• Question 93:

  You have a table that stores transactions partitioned by year. Users occasionally experience performance issues when they access the table. The table is locked exclusively when the records are updated. You need to prevent exclusive locks

  on the table and maintain data integrity.

  What should you do?

  A. Set the LOCK_EXCALATION property to DISABLE

  B. Set the DATA_COMPRESSION property to ROW at the partition level

  C. Set the LOCK_EXCALATION property to AUTO

  D. Set the LOCK_EXCALATION property to TABLE

  E. Set the DATA_COMPRESSION property to PAGE at the partition level

  Correct Answer: C

  The default lock escalation mode is called TABLE, it implements SQL Server's lock escalation on all types of tables whether partitioned or not partitioned.

  There are two more lock escalation modes: AUTO and DISABLE.

  The AUTO mode enables lock escalation for partitioned tables only for the locked partition. For non-partitioned tables it works like TABLE.

  The DISABLE mode removes the lock escalation capability for the table and that is important when concurrency issues are more important than memory needs for specific tables.

  References:

  https://www.mssqltips.com/sqlservertip/4359/altering-lock-escalation-for-sql-server-tables/

• Question 94:

  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 examine delays in executed threads, including errors with queries and batches.

  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.

  Correct Answer: G

  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. Incorrect Answers:

  A: sys.dm_os_waiting_tasks returns information about the wait queue of tasks that are waiting on some resource.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql

• Question 95:

  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. a DML trigger

  B. a schema-bound view

  C. a stored procedure

  D. a DDL trigger

  Correct Answer: A

  When you create a view, you must obey the following conditions in order to make the view updateable:

  The columns being modified in the view have to directly reference the underlying table columns. As a consequence, the view's columns being modified cannot be the result of an aggregate function or a computed column.

  The only way to make data changes on a non-updateable view is by using INSTEAD OF DML triggers. This way you can use procedural code to overcome the limitation.

  References:

  https://www.mssqltips.com/sqlservertip/5984/sql-server-trigger-on-view-example/

• Question 96:

  You have a data warehouse fact table that has a clustered columnstore index.

  You have multiple CSV files that contain a total of 3 million rows of data.

  You need to upload the data to the fact table. The solution must avoid the delta group when you import the data.

  Which solution will achieve the goal in the least amount of time?

  A. Load the source data to a staging table. Load the data to the fact table by using the INSERT_SELECT statement and specify the TABLOCK option on the staging table

  B. Create a Microsoft SQL Server Integration Services (SSIS) package. Use multiple data flow tasks to load the data in parallel.

  C. Load the source data to the fact table by running bcp.exe and specify the �?TABLOCK option

  D. Load the source data to the fact table by using the BULK INSERT statement and specify the TABLOCK option

  Correct Answer: A

  If you are loading data only to stage it before running more transformations, loading the table to heap table will be much faster than loading the data to a clustered columnstore table. In addition, loading data to a [temporary table][Temporary]

  will also load much faster than loading a table to permanent storage.

  A common pattern for data load is to load the data into a staging table, do some transformation and then load it into the target table using the following command

  INSERT INTO

  SELECT FROM

  This command loads the data into the columnstore index in similar ways to BCP or Bulk Insert but in a single batch. If the number of rows in the staging table < 102400, the rows are loaded into a delta rowgroup otherwise the rows are directly

  loaded into compressed rowgroup. One key limitation was that this INSERT operation was single threaded. To load data in parallel, you could create multiple staging table or issue INSERT/SELECT with non-overlapping ranges of rows from

  the staging table. This limitation goes away with SQL Server 2016 (13.x). The command below loads the data from staging table in parallel but you will need to specify TABLOCK.

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-data-loading-guidance?view=sql-server-2017#plan-bulk-load-sizes-to-minimize-delta-rowgroups

• Question 97:

  You have a Microsoft Azure SQL Database. You enable Query Store for the database and configure the store to use the following settings:

  SIZE_BASED_CLEANUP_MODE = OFF

  STALE_QUERY_THRESHOLD_DAYS = 60

  MAX_STORAGE_SIZE_MB = 100

  QUERY_CAPTURE_MODE = ALL

  You use Azure Query Performance Insight to review queries. You observe that new queries are not displayed after 15 days and that the Query Store is set to read-only mode.

  If the Query Store runs low on data space, the store must prioritize queries that run regularly or queries that consume applicant resources.

  You must set the Query Store to read_write mode and determine the performance of queries from the past 60 days.

  Which three actions should you perform? Each correct step presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. Set the value of the CLEANUP_POLICY setting to (STALE_QUERY_THRESHOLD_DAYS = 75)

  B. Set the value of the QUERY_CAPTURE_MODE setting to AUTO

  C. Increase the value for the MAX_STORAGE_SIZE_MB setting

  D. Set the value of the SIZE_BASED_CLEANUP_MODE setting to AUTO

  E. In the Azure portal, navigate to Query Performance Insight. Use the Custom tab to select a period of 2 months.

  Correct Answer: BCD

  B: Capture mode:

  All ?Captures all queries. This is the default option.

  Auto ?Infrequent queries and queries with insignificant cost are ignored. (Ad hoc recommended)

  None ?Query Store stops capturing new queries.

  C: Max Size (MB): Specifies the limit for the data space that Query Store can consume within the database. This is the most important setting that directly affects operation mode of the Query Store.

  While Query Store collects queries, execution plans and statistics, its size in the database grows until this limit is reached. When that happens, Query Store automatically changes the operation mode to read-only and stops collecting new

  data. You should monitor this closely to make sure you have sized the store appropriately to contain the full history you'd like to retain.

  D: Size Based Cleanup Mode: Specifies whether automatic data cleanup will take place when Query Store data size approaches the limit.

  It is strongly recommended to activate size-based cleanup to makes sure that Query Store always runs in read-write mode and collects the latest data.

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store

• Question 98:

  Database users report that SELECT statements take a long time to return results. You run the following Transact-SQL statement:

  

  You need to create one nonclustered covering index that contains all of the columns in the above table. You must minimize index key size. Which Transact-SQL statement should you run?

  A. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserName);

  B. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus) INCLUDE (UserName);

  C. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus, UserName);

  D. CREATE NONCLUSTERED INDEX IX_User ON Users (UserStatus, CountryCode) INCLUDE (UserName);

  Correct Answer: D

  Use the UserStatus as the first column in the index, as it is an in_equality column. Incorrect Answers:

  A: UserStatus is not included.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns

• Question 99:

  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_tran_database_transaction dynamic management view query

  C. an Extended Events session that uses Query Editor

  D. a Microsoft SQL Profiler trace

  Correct Answer: C

  Extended Events: considered as "the best way" by the SQL Server purists. You can configure Extended Events to find Locking Issues in SQL Server. Incorrect Answers:

  D: SQL Trace and SQL Server Profiler are deprecated. The Microsoft.SqlServer.Management.Trace namespace that contains the Microsoft SQL Server Trace and Replay objects are also deprecated.

  This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

  Use Extended Events instead.

  References:

  https://www.mssqltips.com/sqlservertip/5752/configuring-extended-events-to-find-locking-issues-in-sql-server/

• Question 100:

  You have multiple queries that take a long time to complete.

  You need to identify the cause by using detailed information about the Transact-SQL statements in the queries. The Transact-SQL statements must not run as part of the analysis.

  Which Transact-SQL statement should you run?

  A. SET STATISTICS IO ON

  B. SET SHOWPLAN_TEXT ON

  C. SET STATISTICS XML ON

  D. SET STATISTICS PROFILE ON

  Correct Answer: B

  SET SHOWPLAN_TEXT ON causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed. Incorrect Answers:

  C: SET STATISTICS XML ON causes Microsoft SQL Server to execute Transact-SQL statements and generate detailed information about how the statements were executed in the form of a well-defined XML document.

  D: When STATISTICS PROFILE is ON, each executed query returns its regular result set, followed by an additional result set that shows a profile of the query execution.

  The additional result set contains the SHOWPLAN_ALL columns for the query and these additional columns.

  Column name, Description Rows: Actual number of rows produced by each operator Executes: Number of times the operator has been executed

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-text-transact-sql