Home » Microsoft » MB6-886 » Which indicator would you most likely investigate further?
You are a database developer on an instance of SQL Server 2008. You have a Prod database that contains all inventory-related data for your company.
Your InvTransaction table is defined as follows:
You have a Transact-SQL script that is performing poorly, and you need to determine the cause of the degraded performance. The script executes an initial SELECT query on the InvTransaction table and then uses the retrieved data values as criteria in additional queries. You view the graphical execution plan. Which indicator would you most likely investigate further?
A. a query that has a query cost value of 0
B. an operator that indicates a full table scan
C. an operator with a low cost percentage
D. a sort operator
Correct Answer: B
Explanation/Reference:
Although full table scans on small tables are not significant, full table scans on extremely large tables can be expensive. You can usually improve performance by creating indexes to be used instead of scanning the entire table.
A query that has a query cost value of 0 would not be an issue to investigate further. Each query in a batch of Transact-SQL is assigned a query cost value based on its cost relative to the entire batch. In a batch that includes only one statement, the query cost value is 100. You would choose to investigate queries within a multi-statement batch if they had an extremely high query cost value relative to the batch.
An operator with a low cost percentage would not be an issue you would investigate further. A low cost percentage for an operator indicates that the operation is performed without much performance impact. You would investigate operators that had a high cost percentage.
A sort operator alone would not be cause for further investigation. However, you might want to investigate a sort operator with a high cost percentage. Sorts can consume resources, so you should carefully evaluate how your query performs sort operations. You might choose to eliminate ORDER BY clauses that are not necessary, or choose to create indexes using a specific order.
