Get Full Version of the Exam
You manage a Microsoft Azure SQL Database that has the Standard tier plan. The database size has increased and users experience slow performance.
You need to identify usage for the following resources:
CPU utilization Disk storage Memory utilization Disk I/O
sys . dm_db_resource_stats
Correct Answer: BC
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?
SET STATISTICS PROFILE OFF
SET SHOWPLAN_TEXT OFF
SET SHOWPLAN_ALL ON
SET STATISTICS PROFILE ON
Correct Answer: C
SET SHOWPLAN_ALL ON causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements.
You have the following stored procedure:
The Numbers table becomes unavailable when you run the stored procedure. The stored procedure obtains an exclusive lock on the table and does not release the lock.
What are two possible ways to resolve the issue? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
Remove the implicit transaction and the SET ANSI_DEFAULTS ON statement.
Set the ANSI_DEFAULT statement to OFF and add a COMMIT TRANSACTION statement after the INSERT statement.
Add a COMMIT TRANSACTION statement after the INSERT statement.
Remove the SET ANSI DEFAULTS ON statement.
Correct Answer: CD
SET ANSI_DEFAULTS is a server-side setting that the client does not modify. When enabled (ON), this option enables SET IMPLICIT_TRANSACTIONS (and some other options).
The SET IMPLICIT_TRANSACTIONS, when ON, the system is in implicit transaction mode.
This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction. It is equivalent to an unseen BEGIN TRANSACTION being executed first: ALTER TABLE, FETCH, REVOKE, BEGIN TRANSACTION, GRANT, SELECT, CREATE, INSERT, TRUNCATE TABLE, DELETE, OPEN, UPDATE, DROP.
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 table that has a clustered index and a nonclustered index. The indexes use different columns from the table. You have a query named Query1 that uses the nonclustered index.
Users report that Query1 takes a long time to report results. You run Query1 and review the following statistics for an index seek operation:
You need to resolve the performance issue. Solution: You drop the nonclustered index.
Does the solution meet the goal?
Correct Answer: B
You have several real-time applications that constantly update data in a database. The applications run more than 400 transactions per second that insert and update new metrics from sensors.
A new web dashboard is released to present the data from the sensors. Engineers report that the applications take longer than expected to commit updates.
You need to change the dashboard queries to improve concurrency and to support reading uncommitted data.
What should you do?
Use the NOLOCK option.
Execute the DBCC UPDATEUSAGE statement.
Use the max worker threads Option.
Use a table-valued parameter.
Set SET ALLOW SNAPSHOT ISOLATION to ON.
Set SET XACTJVBORT to ON.
Execute the alter table ti set (Lock_ESCALATION = auto); statement.
Use the output parameters.
Correct Answer: A
The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes.
This can improve query performance, but also introduces the possibility of dirty reads.
You are designing a solution for a company that operates retail stores. Each store has a database that tracks sales transactions. You create a summary table in the database at the corporate office. You plan to use the table to record the quantity of each product sold at each store on each day. Managers will use this data to identify reorder levels for products.
Every evening, stores must transmit sales data to the corporate office. The data must be inserted into the summary table that includes the storeID, Production. Qtysold. Totprodsales, and Datesold columns.
You need to prevent duplicate rows in the summary table. Each row must uniquely identify the store that sold the product and the total amount sold for that store on a specific date.
What should you include in your solution?
Create a column store index.
Create a check constraint.
Create a unique constraint.
Create a rule and bind it to the ProductID column.
Correct Answer: D
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
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.
You run the following Transact-SQL statement:
There are multiple unique OrderlD values. Most of the UnitPrice values for the same OrderlD are different.
You need to create a single index seek query that does not use the following operators:
Nested loop Sort
CREATE INDEX IX_OrderLines_l ON OrderLines (OrderlD, UnitPrice) INCLUDE(Description, Quantity)
CREATE INDEX IX_OrderLines_l ON OrderLines (OrderlD, UnitPrice) INCLUOE(Quantity)
CREATE INDEX IX_OrderLines_l ON OrderLines (OrderlD, UnitPrice, Quantity)
CREATE INDEX IX_OrderLines_l ON OrderLines (UnitPrice, OrderlD) INCLUDE(Description, Quantity)
Correct Answer: A
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?
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.
Load the source data to a staging table that has a clustered index on the primary key. Copy the data to the fact table by using the insert_select statement.
Load the source data to the fact table by running bcp.exe and specify the _ Tablock option.
Load the source data to the fact table by using the bulk insert statement and specify the Tablock option.
Correct Answer: B
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 lt;columnstore indexgt;
SELECT lt;list of columnsgt; FROM lt;Staging Tablegt;
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 lt; 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.
You need to build a function that meets the following requirements:
Returns multiple rows
Optimizes the performance of the query within the function
How should you complete the Transact-SQL statement? 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.