You are a database developer on an instance of SQL Server 2008. Your Sales database contains the following SalesDetail table: (Click on Exhibits to view table)
Currently, only a unique nonclustered index exists on the SalesOrderDetailID column named PK_SalesDetail. You have the following query that is performing poorly:
SELECT * FROM SalesDetail WITH (INDEX(PK_SalesDetail))
WHERE UnitPrice > 10 AND UnitPrice < 2000 AND CarrierTrackingNumber = ‘0538-491B-B6’;
The query generates the following actual execution plan:
What should you do to improve the query’s performance?
Exhibit:
A. Create a plan guide including the OPTIMIZE FOR hint.
B. Include the MAXDOP query hint in the query.
C. Modify the WHERE clause to use the BETWEEN operator.
D. Create a nonclustered index on the CarrierTrackingNumber and UnitPrice columns.
Correct Answer: D
Explanation/Reference:
In this scenario, the only index that exists is the nonclustered index on the primary key column. The given query uses the CarrierTrackingNumber and UnitPrice columns in WHERE clause conditions. Therefore, you should create a nonclustered index on these columns. This would improve query performance because the optimizer would use an Index Scan or an Index Seek operation instead of an RID Lookup. An Index Scan scans the entire nonclustered index, and an Index Seek does not. Therefore, to optimize query performance, you might also ensure that an Index Seek is performed. In this scenario, you could use the following statement to create a nonclustered index on the two columns:
CREATE INDEX IX_TrackingUnitPrice ON SalesDetail(CarrierTrackingNumber, UnitPrice);
Then, you could remove the INDEX hint in your query so that the optimizer would not be forced to use the PK_SalesDetail nonclustered index.
Another index which is also good:
CREATE NONCLUSTERED INDEX IX_SalesDetail_CarrierTrackingNumber_incl_UnitPrice
ON SalesDetail (CarrierTrackingNumber) INCLUDE (UnitPrice)
You should not create a plan guide including the OPTIMIZE FOR hint. Plan guides can be used to optimize queries without modifying the query directly. Plan guides are helpful when you need to specify query hints but cannot directly access the query, such as when it is embedded within an application. In a plan guide, you include the query to be optimized, and the query hints or a query plan that should be used for optimization. TheOPTIMIZE FOR query hint forces the optimizer to use a specific parameter value when optimizing the query.
You should not include the MAXDOP query hint in the query. The MAXDOP query hint specifies an integer value that identifies the number of processors on which a single query may run. If the query is run on a computer with a single processor, the query hint is ignored. You can also accomplish this by setting themax degree of parallelism configuration option on the SQL Server instance, but this setting would affect all queries.
You should not modify the WHERE clause to use the BETWEEN operator. The query would still access the same underlying data and would use a lookup operator to do so.
