You have several SQL Server queries.

You plan to optimize the queries to improve performance.

You run the queries in SQL Server Management Studio.

You need to compare

query runs to expose the indexing issues of the queries.

Which three actions should you perform from SQL Server Management Studio? Each correct answer presents part of the solution.

A. Enable the Debug option.

B. Add the STATISTICS TIME execution setting

to the query.

C. Add the STATISTICS IO execution setting to the query.

D. Add the STATISTICS PROFILE execution setting to the query.

E. Enable the Include Actual Execution Plan option.

Explanation:

E: An execution plan is theresult of the

query optimizer-s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted. To generate the first execution plan, you can enable the Include Actual Execution Plan option.

B: SET STATISTICS TIME

displays the number of milliseconds required to parse, compile, and execute each statement.

C: STATISTICS IO causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements. This is useful information fo

r optimizing queries.

The information include Scan count:

Number of seeks/scans started after reaching the leaf level in any direction to retrieve all the values to construct the final dataset for the output.

Scan count is 0 if the index used is a unique

index or clustered index on a primary key and you are seeking for only one value. For example WHERE Primary_Key_Column = .

Scant count is 1 when you are searching for one value using a non-unique clustered index which is defined on anon-primary key

column. This is done to check for duplicate values for the keyvalue that you are searching for. For example WHERE Clustered_Index_Key_Column = .

Scan count is N when N is the number of different seek/scan started towards the leftor right side at t

he leaf level after locating a key value using the index key.

Incorrect Answers:

A: The Debug option is used the find programming errors, and is not used to increase performance.

D: Graphical Plans are quick and easy to read but the detailed data for the

plan is masked. Both Estimated and Actual execution plans can be viewed in graphical format.

Text plans are a bit harder to read, but more information is immediately available.

There are three text plan formats:

SHOWPLAN_ALL: a reasonably complete set of

data showing the Estimated execution plan for the query

SHOWPLAN_TEXT: provides a very limited set of data for use with tools like osql.exe. It too only shows the Estimated execution plan

STATISTICS PROFILE: similar to SHOWPLAN_ALL except it represents the

data for the Actual execution plan

References: