Home » Microsoft » 70-461 v.2 » Which three actions should you perform from SQL Server Management Studio?
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 10 execution setting to the query.
D. Add the STATISTICS PROFILE execution setting to the query.
E. Enable the Include Actual Execution Plan option.
Correct Answer: BCE
Explanation/Reference:
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 10 causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements. This is useful information for 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
= <value>.
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_lndex_Key_Column = <value>.
Scan count is N when N is the number of different seek/scan started towards the leftor right side at the 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: https://www.simple-talk.com/sql/performance/execution-plan-basics/
https://msdn.microsoft.com/en-us/librarv/msl 84361 .aspx
