Note: This question is part of a series of questions that use the same answer choices. An answer choice may be correct for more than one question on the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You work on an OLTP database that has no memory-optimized file group defined.

You have a table names tblTransaction that is persisted on disk and contains the information described in the following table:

Users report that the following query takes a long time to complete.

You need to create an index that:

– improves the query performance

– does not impact the existing index

– minimizes storage size of the table (inclusive of index pages).

What should you do?

Reveal Solution Hide Solution

Note: This question is part of a series of questions that use the same answer choices. An answer choice may be correct for more than one question on the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You work on an OLTP database that has no memory-optimized file group defined.

You have a table names tblTransaction that is persisted on disk and contains the information described in the following table:

Users report that the following query takes a long time to complete.

You need to create an index that:

– improves the query performance

– does not impact the existing index

– minimizes storage size of the table (inclusive of index pages).

What should you do?

Reveal Solution Hide Solution

Note: This question is part of a series of questions that use the same answer choices. An answer choice may be correct for more than one question on the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You work on an OLTP database that has no memory-optimized file group defined.

You have a table names tblTransaction that is persisted on disk and contains the information described in the following table:

Users report that the following query takes a long time to complete.

You need to create an index that:

– improves the query performance

– does not impact the existing index

– minimizes storage size of the table (inclusive of index pages).

What should you do?

Reveal Solution Hide Solution

FirstName must be added to the index as an included column.

What should you do?

Reveal Solution Hide Solution

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 reporting database that includes a non-partitioned fact table named Fact_Sales. The table is persisted on disk. Users report that their queries take a long time to complete. The system administrator reports that the table takes too much space in the database. You observe that there are no indexes defined on the table, and many columns have repeating values.

You need to create the most efficient index on the table, minimize disk storage and improve reporting query performance.

What should you do?

Reveal Solution Hide Solution

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 use a memory-optimized filegroup. The database contains a table named Table1.

The table must support the following workloads:

You need to add the most efficient index to support the new OLTP workload, while not deteriorating the existing Reporting query performance.

What should you do?

Reveal Solution Hide Solution

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?

Reveal Solution Hide Solution

DRAG DROP

You are analyzing the performance of a database environment.

You suspect there are several missing indexes in the current database.

You need to return a prioritized list of the missing indexes on the current database.

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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Box 1: sys.db_db_missing_index_group_stats

Box 2: group_handle

Example: The following query determines which missing indexes comprise a particular missing index group, and displays their column details. For the sake of this example, the missing index group handle is 24.

SELECT migs.group_handle, mid.*

FROM sys.dm_db_missing_index_group_stats AS migs

INNER JOIN sys.dm_db_missing_index_groups AS mig

ON (migs.group_handle = mig.index_group_handle)

INNER JOIN sys.dm_db_missing_index_details AS mid

ON (mig.index_handle = mid.index_handle)

WHERE migs.group_handle = 24;

Box 3: sys.db_db_missing_index_group_stats

The sys.db_db_missing_index_group_stats table include the required columns for the subquery: avg_total_user_cost and avg_user_impact.

Example: Find the 10 missing indexes with the highest anticipated improvement for user queries

The following query determines which 10 missing indexes would produce the highest anticipated cumulative improvement, in descending order, for user queries.

SELECT TOP 10 *

FROM sys.dm_db_missing_index_group_stats

ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;

DRAG DROP

You are monitoring a Microsoft Azure SQL Database. The database is experiencing high CPU consumption. You need to determine which query uses the most cumulative CPU.

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 one or not at all. You may need to drag the split bar between panes or scroll to view content.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Box 1: sys.dm_exec_query_stats

sys.dm_exec_query_stats returns aggregateperformance statistics for cached query plans in SQL Server.

Box 2: highest_cpu_queries.total_worker_time DESC

Sort on total_worker_time column

Example: The following example returns information about the top five queries ranked by average CPU time.

Thisexample aggregates the queries according to their query hash so that logically equivalentqueries are grouped by their cumulative resource consumption.

USE AdventureWorks2012;

GO

SELECT TOP 5 query_stats.query_hash AS "Query Hash",

SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",

MIN(query_stats.statement_text) AS "Statement Text"

FROM

(SELECT QS.*,

SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(ST.text)

ELSE QS.statement_end_offset END

– QS.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS QS

CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle)as ST) as query_stats

GROUP BY query_stats.query_hash

ORDER BY 2 DESC;

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

DRAG DROP

You are analyzing the memory usage of a Microsoft SQL Server instance.

You need to obtain the information described on the following table.

Which performance counter should you use for each requirement? To answer, drag the appropriate performance counters to the correct requirements. Each performance counter may be used once, more than once or not at all. You may need to drag the split bat between panes or scroll to view content.

NOTE: Each correct selection is worth one point.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Requirement1: SQL Server: Memory Manager: Total Server Memory (KB)

This counter specifies theamount of memory the server has committed using the memory manager.

Requirement2: SQL Server: Memory Manager: Granted Workspace Memory (KB)

Specifies the total amount of memory currently granted to executing processes, such as hash, sort, bulk copy, andindex creation operations.

Requirement3: Process: working Set

Each time a process is created, it reserves the minimum working set size for the process. The virtual memory manager attempts to keep enough memory for the minimum working set resident when the process is active, but keeps no more than the maximum size.

References:

https://msdn.microsoft.com/en-us/library/ms190924.aspx

https://blogs.technet.microsoft.com/askperf/2007/05/18/sql-and-the-working-set/