20762C: Developing SQL 2016 Databases Quiz Questions and Answers

You have a reporting application that uses a table named Table1. You deploy a new batch update process to perform updates to Table1. The environment is configured with the following properties: The database is configured with the default isolation setting. The application and process use the default transaction handling. You observe the application cannot access any rows that are in use by the process. You have the following requirements: Ensure the application is not blocked by the process. Ensure the application has a consistent view of the data Ensure the application does not read dirty data. You need to resolve the issue and meet the requirements with the least amount of administrative effort. What should you do?

Answer :
  • Enable the database for the READ_COMITTED_SNAPSHOT isolation level.

Explanation :

Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. This activates the mechanism for storing row versions in the temporary database (tempdb). READ COMMITTED is the default isolation level for SQL Server. It prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions. Other transactions can still modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non-repeatable reads, or -phantom- data. Incorrect Answers: A: SERIALIZABLE is the most restrictive isolation level, because it locks entire ranges of keys and holds the locks until the transaction is complete. It encompasses REPEATABLE READ and adds the restriction that other transactions cannot insert new rows into ranges that have been read by the transaction until the transaction is complete. References: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

You are developing a database reporti ng solution for a table that contains 900 million rows and is 103 GB. The table is updated thousands of times a day, but data is not deleted. The SELECT statements vary in the number of columns used and the amount of rows retrieved. You need to reduce t he amount of time it takes to retrieve data from the table. The must prevent data duplication.Which indexing strategy should you use?

Answer :
  • a clustered columnstore index for the table

Explanation :

Columnstore indexes are the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over traditional row-oriented storage. A clustered columnstore index is the physical storage for the entire table. Generally, you should define the clustered index key with a s few columns as possible. A nonclustered index contains the index key values and row locators that point to the storage location of the table data. You can create multiple nonclustered indexes on a table or indexed view. Generally, nonclustered indexes s hould be designed to improve the performance of frequently used queries that are not covered by the clustered index. References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-2017

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?

Answer :
  • a sys.dm_exec_session_wait_stats dynamicmanagement view query

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.

You are creating the following two stored procedures:A natively-compiled stored procedure An interpreted stored procedure that accesses both disk-based and m emory-optimized tables Both stored procedures run within transactions. You need to ensure that cross-container transactions are possible. Which setting or option should you use?

Answer :
  • the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON option for the database

Explanation :

Provide a supported isolation level for the memory-optimized table using a table hint, such as WITH (SNAPSHOT). The need for the WITH (SNAPSHOT) hint can be avoided through the use of the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. When this option is set to ON, access to a memory-optimized table under a lower isolation level is automatically elevated to SNAPSHOT isolation. Incorrect Answers: B: Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. References: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables?view=sql-server-2017

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. What should you do?

Answer :
  • Create a SQL Profiler trace

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 node involved in the deadlock. Tra ce flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event. References: https://technet.microsoft.com/en-us/library/ms178104(v=sql .105).aspx

You use Microsoft SQL Server Profile to evaluate a query named Query1. The Profiler report indicates the following issues: At each level of the query plan, a low total number of rows are processed. The query uses many operations. This results in a high overall cost for the query. You need to identify the information that will be useful for the optimizer. What should you do?

Answer :
  • Start a SQL Server Profiler trace for the event class Performance statistics in the Performance event category.

Explanation :

The Performance Statistics event class can be used to monitor the performance of queries, stored procedures, and triggers that are executing. Each of the six event subclasses indicates an event in the lifetime of queries, stored procedures, and triggers within the system. Using the combination of these event subclasses and the associated sys.dm_exec_query_stats, sys.dm_exec_procedure_stats and sys.dm_exec_trigger_stats dynamic management views, you can reconstitute the performance history of any given query, stored procedure, or trigger. References: https://docs.microsoft.com/en-us/sql/relational-databases/event- classes/performance-statistics-event-class?view=sql-server-2017

Create Table Customer( customerid int identity(1,1) primary key, code char(S) not null, FirstName varchar(50) not null, lastname varchar(50) not null)Customer records may be inserted individually or in bulk from an application. You observe that the application attempts to insert duplicate records. You must ensure that duplicate records are not inserted and bulk insert operations continue without notifications. Which Transact-SQL statement should you run?

Answer :
  • CREATE UNIQUE INDEX IX_CUSTOMER_Code O Customer (Code) WITH (IGNORE_DUP_KEY = ON)

Explanation :

Explanation: IGNORE_DUP_KEY = { ON | OFF } specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF. Incorrect Answers: ONLINE = { ON | OFF } specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2017

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?

Answer :
  • Create a sys.dm_exec_sessions query.

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 al l 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.

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?

Answer :
  • Create an Extended Event.

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 > Extended Events > Sessions > [your-session] > 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 can see a list of property names and their values for the node.

You have a database named DB1. The database does not have a memory optimized filegroup. You create a table by running the following Transact-SQL statement: Create table tblTransaction( transactionid int not null primary key, transactiondate date not null, accountid int not null, valuetype char(3) not null, amount deciaml(20,2) null); The table is currently used for OLTP workloads. The analytics user group needs to perform real-time operational analytics that scan most of the records in the table to aggregate on a number of columns. You need to add the most efficient index to support the analytics workload without changing the OLTP application. What should you do?

Answer :
  • Create a nonclustered columnstore index on the table.

Explanation :

A nonclustered columnstore index enables real-timeoperational analytics in which the OLTP workload uses the underlying clustered index, while analytics run concurrently on the columnstore index. Columnstore indexes can achieve up to 100xbetter performance on analytics and data warehousing workloads and up to 10x better data compression than traditional rowstore indexes. These recommendations will help your queries achieve the very fast query performance that columnstore indexes are designed to provide. References: https://msdn.microsoft.com/en-us/library/gg492088.aspx