ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-765 All Questions

View all questions & answers for the 70-765 exam
Go to Exam

Exam 70-765 topic 3 question 97 discussion

Actual exam question from Microsoft's 70-765
Question #: 97
Topic #: 3
[All 70-765 Questions]

HOTSPOT -
You have Microsoft SQL Server on a Microsoft Azure virtual machine. The virtual machine has a database named DB1. DB1 contains a table named Table1 that has 4 billion rows.
Users report that a query using Table1 takes longer than expected to execute.
You review the execution plan for the query and discover that the expected number of returned rows is one, while the actual number of returned rows is 1 million.
You need to reduce the amount of time it takes for the query to execute. The solution must prevent additional performance issues from being introduced.
Hot Area:

Show Suggested Answer Hide Answer
Suggested Answer:
When you set the AUTO_CREATE_STATISTICS option on, the Query Optimizer creates statistics on individual columns used in a predicate, if these statistics are not already available. These statistics are necessary to generate the query plan.
References: https://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/
by Varad at Oct. 29, 2019, 9:19 p.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
StillConfused
4 years, 5 months ago
Not sure auto update stats is right as it says you must prevent additional performance issues, which can happen if auto update statistics is on because SQL determines if it needs to update the statistics just before it executes the query, and if it decides it does it can take a long time to complete - especially on large tables. I think disabling parameter sniffing is the best answer here. The query plan could be estimating the wrong number of rows because it is using a cached query plan generated for a different (initial) set of query parameters.
upvoted 1 times
...
Kurtis24K
4 years, 7 months ago
MAXDOP should be modified as well
upvoted 1 times
...
Varad
5 years, 6 months ago
set the AUTO_UPDATE_STATISTICS option ON
upvoted 3 times
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago