You maintain a Microsoft Azure SQL Database instance.
You grant User1 the SELECT and EXECUTE permissions for all objects in the dbo schema. You must create a stored procedure that allows User1 to view the following information:
details for each connection to the database
a list of all active user connections and internal tasks
You need to create the stored procedure for User1 and ensure that User1 can run the stored procedure without any error.
How should you complete the Transact-SQL statements? 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.
NOTE: More than one combination of answer choices is correct. You will receive credit for any of the correct combinations you select.
Solution:
Box 1: Sys.dm_exec_connections
Sys.dm_exec_connections returns information about the connections established to this instance of SQL Server and the details of each connection. Returns server wide connection information for SQL Server. Returns current database connection information for SQL Database.
Box 2: sys.dm_exec_sessions
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.
Box 3: GRANT VIEW DATABASE STATE To User1
SQL Database: Requires VIEW DATABASE STATE to see all connections to the current database. VIEW DATABASE STATE cannot be granted in the master database.
Does this meet the goal?
Correct Answer:
A
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some questions sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that is 130 GB and contains 500 million rows of data.
Granular transactions and mass batch data imports change the database frequently throughout the day. Microsoft SQL Server Reporting Services (SSRS) uses the database to generate various reports by using several filters.
You discover that some reports time out before they complete. You need to reduce the likelihood that the reports will time out. Solution: You partition the largest tables.
Does this meet the goal?
Correct Answer:
B
You have a database that contains both disk-based and memory-optimized tables.
You need to create two modules. The modules must meet the requirements described in the following table.
Which programming object should you use for each module? To answer, select the appropriate object types in the answer area.
Solution:
Module 1: Interpreted stored procedure
An interpreted stored procedure can access both disk-based and memory-optimized tables. Module 2: Natively compiled stored procedure
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.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/natively-compiled-stored-procedures?
Does this meet the goal?
Correct Answer:
A
Note: The 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 question in the series. Information and details provided in a question apply only to that question.
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:
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?
Correct Answer:
E
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
You have a database that users query frequently.
The users report that during peak business hours, the queries take longer than expected to execute.
A junior database administrator uses Microsoft SQL Server Profiler on the database server to trace the session activities.
While performing the trace, the performance of the database server worsens, and the server crashes.
You need to recommend a solution to collect the query run times. The solution must minimize the impact on the resources of the database server.
What should you recommend?
Correct Answer:
A
