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?

Reveal Solution Hide Solution

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?

Reveal Solution Hide Solution

HOTSPOT

You are maintaining statistics for a database table named tblTransaction. The table contains more than 10 million records.

You need to create a stored procedure that meets the following requirements:

– On weekdays, update statistics for a sample of the total number of records in the table.

– On weekends, update statistics by sampling all rows in the table.

A maintenance task will call this stored procedure daily.

How should you complete the stored procedure? To answer, select the appropriate Transact-SQL segments in the answer area. NOTE: Each correct selection is worth one point.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Box 1: UPDATE STATISTICS

Box 2: SAMPLE 20 PERCENT

UPDATE STATISTICS tablenameSAMPLE number { PERCENT | ROWS }

Specifies the approximate percentage or number of rows in the table or indexed view for the query optimizer to use when it updates statistics. For PERCENT, number can be from 0 through 100 and for ROWS, number can be from0 to the total number of rows.

Box 3: UPDATE STATISTICS

Box 4: WITH FULLSCAN

FULLSCAN computes statistics by scanning all rows in the table or indexed view. FULLSCAN and SAMPLE 100 PERCENT have the same results. FULLSCAN cannot be used with the SAMPLE option.

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

DRAG DROP

You have a database named MyDatabase. You must monitor all the execution plans in XML format by using Microsoft SQL Trace.

The trace must meet the following requirements:

– Capture execution plans only for queries that run the MyDatabase database.

– Filter out plans with event duration of less than or equal to 100 microseconds.

– Save trace results to a disk on the server.

You need to create the trace.

In which order should you arrange the Transact-SQL segments to develop the solution? To answer, move all Transact-SQL segments to the answer area and arrange them in the correct order.

NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

The following system stored procedures are used to define and manage traces:

* sp_trace_create is used to define a trace and specify an output file location as well asother options that I’ll cover in the coming pages. This stored procedure returns a handle to the created trace, in the form of an integer trace ID.

* sp_trace_setevent is used to add event/column combinations to traces based on the trace ID, as well as toremove them, if necessary, from traces in which they have already been defined.

* sp_trace_setfilter is used to define event filters based on trace columns.

* sp_trace_setstatus is called to turn on a trace, to stop a trace, and to delete a trace definitiononce you’re done with it. Traces can be started and stopped multiple times over their lifespan.

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

HOTSPOT

You are analyzing the performance of a database environment. You need to find all unused indexes in the current database.

How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL

segments in the answer area.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Example: Following query helps you to find all unused indexes within database using sys.dm_db_index_usage_stats DMV.

— Ensure a USE statement has been executed first.

SELECT u.*

FROM [sys].[indexes] i

INNER JOIN [sys].[objects] o ON (i.OBJECT_ID = o.OBJECT_ID)

LEFT JOIN [sys].[dm_db_index_usage_stats] u ON (i.OBJECT_ID = u.OBJECT_ID)

AND i.[index_id] = u.[index_id]

AND u.[database_id] = DB_ID() –returning the database ID of the current database

WHERE o.[type] <> ‘S’ –shouldn’t be a system base table

AND i.[type_desc] <> ‘HEAP’

AND i.[name] NOT LIKE ‘PK_%’

AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0

AND u.[last_system_scan] IS NOT NULL

ORDER BY 1 ASC

References: https://basitaalishan.com/2012/06/15/find-unused-indexes-using-sys-dm_db_index_usage_stats/

HOTSPOT

You are reviewing the execution plans in the query plan cache.

You observe the following:

– There are a large number of single use plans.

– There are a large number of simple execution plans that use multiple CPU cores.

You need to configure the server to optimize query plan execution.

Which two setting should you modify on the properties page for the Microsoft SQL Server instance? To answer, select the appropriate settings in the answer area.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

* Optimize for ad hoc workloads

The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

* Cost Threshold for Parallelism

Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration.

5 means 5 seconds, but is is 5 seconds on a machine internal to Microsoft from some time in the 1990s. There’s no way to relate it to execution time on your current machine, so we treat it as a pure number now. Raising it to 50 is a common suggestion nowadays, so that more of your simpler queries run on a single thread.

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 is independent of the other questions in the series. Information and details provided in a question apply only to that question.

You are developing an application to track customer sales.

You need to create a database object that meets the following requirements:

– Return a value of 0 if data inserted successfully into the Customers table.

– Return a value of 1 if data is not inserted successfully into the Customers table.

– Support logic that is written by using managed code.

What should you create?

Reveal Solution Hide Solution

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 is independent of the other questions in the series. Information and details provided in a question apply only to that question.

You are developing an application to track customer sales.

You need to create a database object that meets the following requirements:

– Return a value of 0 if data inserted successfully into the Customers table.

– Return a value of 1 if data is not inserted successfully into the Customers table.

– Support TRY…CATCH error handling

– Be written by using Transact-SQL statements.

What should you create?

Reveal Solution Hide Solution

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 is independent of the other questions in the series. Information and details provided in a question apply only to that question.

You are developing an application to track customer sales.

You need to create a database object that meets the following requirements:

– Launch when table data is modified.

– Evaluate the state a table before and after a data modification and take action based on the difference.

– Prevent malicious or incorrect table data operations.

– Prevent changes that violate referential integrity by cancelling the attempted data modification.

– Run managed code packaged in an assembly that is created in the Microsoft.NET Framework and located into Microsoft SQL Server.

What should you create?

Reveal Solution Hide Solution

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 is independent of the other questions in the series. Information and details provided in a question apply only to that question.

You are developing and application to track customer sales.

You need to return the sum of orders that have been finalized, given a specified order identifier. This value will be used in other Transact-SQL statements. You need to create a database object.

What should you create?

Reveal Solution Hide Solution