You administer an instance of SQL Server 2014.
You are tasked with tuning a common set of queries. You have the results of several test
executions, along with query plans. The schema and the data for all database object(s) used
remain unchanged between executions. The QueryTime column is defined as a computed
column that uses the GETDATE() system function. The query plans and results are shown below:
You need to make an initial diagnosis of the situation, based solely on this input
Which two statements can you make about the performance characteristics of this query?
Each correct answer presents a complete solution. Choose two.
A.
The queries would perform better if the index named AccountNumber included the Name
and QueryTime column.
B.
The queries would perform worse if the index named AccountNumber included the
NameColumn.
C.
The queries would perform better if the index named AccountNumber included the Name
column.
D.
The object Account is a table, with an index having a leading column of AccountNumber
and a Clustered Index named PKAccount.
E.
The object Account is an indexed view, with an index having a leading column of
AccountNumber and a Clustered Index named PKAccount.
F.
The object Account is a view, joining the Account-AccountNumber and
Account.PKAccount objects together.
I am sure its C,D.
It is. B&D
I think B, D
When you try to execute this:
CREATE NONCLUSTERED INDEX [NonClusteredIndex] ON [dbo].[Account]
(
[AccountNumber] ASC
)
INCLUDE (
[Name],
[QueryTime])
GO
you get an error:
Msg 2729, Level 16, State 1, Line 10
Column ‘QueryTime’ in table ‘dbo.Account’ cannot be used in an index or statistics or as a partition key because it is non-deterministic.
So that is why it cannot be A
Why B&D and not C&D?
“C” would be right, if you exclude the “QueryTime” column from SELECT. But they perform “SELECT * FROM dbo.ACCOUNT”.
I’ ve tested it: The query would NOT perform worse, if you include the “Name” column in index. So “B” is wrong. My intention says C&D, it’s not right but the best answer :(.
Hmpf!
“C”&”D” is right! During my test (see post above) I’ve not correct defined the computed column. After correction (“alter table test add ti as (getdate())”) I could successfull test answer “C”. If you include the “Name” column, the Primary Key index will not used and the cost are decreased for 50%.
B,D is right. C should also include AccountID.
B will make the index bigger, if AccountID is not included, still need to get the AccountID from the clustered index.
AccountID is included automaticly because it is the PrimaryKey
1. Whether one filters the SELECT statement by a single or a range of ‘AccountNumber’, the Query Plan is still the same: there’s a Key Lookup of ‘AccountID’ for every row returned for an AccountNumber (also depicted by the INNER LOOP join).
2. With a SELECT * query, the inclusion of the ‘Name’ column in the ‘AccountNumber’ index, will not improve query execution, except all other referenced columns (of the Select clause) are “covered” by the index.
3. Because the ‘QueryTime’ column is a non-deterministic column, and could not be included in the ‘covering’ index, every other columns (AccountNumber, AccountID, Name) would have had to be included in the Account.AccountNumber index, in order to see any improved query performance.
Hence, option C is mute. I’ll take B & D.
C and D. According my tests.