###BeginCaseStudy###
Case Study: 2
Scenario 2
Application Information
You have two servers named SQL1 and SQL2 that have SQL Server 2012 installed.
You have an application that is used to schedule and manage conferences.
Users report that the application has many errors and is very slow.
You are updating the application to resolve the issues.
You plan to create a new database on SQL1 to support the application. A junior database
administrator has created all the scripts that will be used to create the database. The script that
you plan to use to create the tables for the new database is shown in Tables.sql. The script
that you plan to use to create the stored procedures for the new database is shown in
StoredProcedures.sql. The script that you plan to use to create the indexes for the new
database is shown in Indexes.sql. (Line numbers are included for reference only.)
A database named DB2 resides on SQL2. DB2 has a table named SpeakerAudit that will
audit changes to a table named Speakers.
A stored procedure named usp_UpdateSpeakersName will be executed only by other stored
procedures. The stored procedures executing usp_UpdateSpeakersName will always handle
transactions.
A stored procedure named usp_SelectSpeakersByName will be used to retrieve the names of
speakers. Usp_SelectSpeakersByName can read uncommitted data.
A stored procedure named usp_GetFutureSessions will be used to retrieve sessions that will
occur in the future.
Procedures.sql
Indexes.sql
Tables.sql
###EndCaseStudy###
While testing usp.GetFutureSessions, you discover that IX_Sessions is accessed by a scan
rather than a seek.
You need to minimize the amount of time it takes to execute usp_GetFutureSessions.
What should you do? (Each correct answer presents part of the solution. Choose all that
apply.)
A.
Option A
B.
Option B
C.
Option C
D.
Option D
E.
Option E
F.
Option F
Explanation:
Future delivery dates.
Include (RoomID) on IX_sessions does not make sense. RoomID is not part of the table Sessions.
A,B,E
should be AE?
B will not work
NOT A: not covering index
NOT B: filter predicates must be deterministic
C.
NOT D: not future delivery dates.
E.
NOT F: filter predicates must be deterministic
B and F can be elimited first, as filtered index must have deterministic predicate
D is not the right answer as it does not fulfil future date required
So only ACE left
The query is:
select SpeakerID, RoomID, DeliveryTime from Sessions
with missing predicate, so E is definitly one of the answer
After adding E the query becomes:
select SpeakerID, RoomID, DeliveryTime from Sessions
where GetDate() < DeliveryTime
The predicate here is the "DeliveryTime" and the type of the search is a range search. So the best we can do is to make query conduct a index range scan. I don't see any other option to make the query faster other than this. Please correct me in case I am wrong.
So we need an index. If we create index as in C, which SpeakerID is the leading column of the index, the index won't help us at all for the query. So the only option left is A which "DeliveryTime" is the leading column of the index. The "SessionID" is the second column, where "RoomID" is included in the leaf node of the index.
When above query executes, depends on the statistics, query optimization engine will choose a full table scan in case the data is not selecitve enough, or it will:
1, Execute an index range scan for the IX_Session to find all index leaf node which DeliveryTime is the future date, the index leaf node contains the value of 3 columns: DeliveryTime, SessionID, RoomID and the Row Identifier (because table Session is a Heap table).
Now, the bad thing here is the "SpeakerID" which can only be find in the Session table. So the query execute a Row look up with row identifier contained in the leaf of the index
The query plan will looks like this
Result
|<——- Nested Loop <——- Index Range Scan (IX_Session)
^
|
|
|————— RID Lookup
The answser is AE
The answer above is the logically correct answer, because firstly the query will result a full table scan (because it is a heap table). The original IX_Sessions will not be used with or without "DeliveryTime" in preducate. I don't see any option I can make the query execute a index seek with a range comparison predicate, so thus make the query do an index ranged scan is an improve.
Agree with XXX, not sure what to do with RoomID as it does not exist in the table definition, at least not yet!
CREATE INDEX IX_Sessions ON [Sessions]
(DeliveryTime, SessionID)
CREATE PROCEDURE usp_GetFutureSessions
AS
SELECT SpeakerID,
RoomID,
DeliveryTime
FROM [Sessions]
WHERE GETDATE() < DeliveryTime;
GO