Interesting Query Optimization in SQL Server 2005

Here is an interesting query optimization problem that I recently encountered on a large production system. The FooBarSubscription table has 32 million rows, while the FooBarInfo table has 1.2 million rows.  The two queries in each batch are identical except for the use of a LOOP JOIN hint.

On it’s own, the query optimizer wanted to do a MERGE JOIN, which will cause much less logical IO and will "cost" less, but it takes 23 seconds instead of 1 second to execute.  So which is more important here, execution time or execution cost and logical IO?

I decided to try adding a "covering", unique, non-clustered index on the FooBarID, UserID and FeedID of the FooBarSubscription table. Using the Online Index option (available in SQL Server Enterprise Edition), with MAXDOP set to two, it took just under four minutes to create this index on the 32 million row table.

After adding the new index, the execution time went from 23 seconds to 13 seconds (measured at my desktop over a fairly low bandwidth connection), while the logical IOs went from 92K to 67K. After talking to the developer, we decided to go with the MERGE JOIN version, even though it still takes much longer to run, because of the much lower logical IO footprint. Luckily, the query is only run once every fifteen minutes.

This shows how important it is to have good lines of communication with talented developers, since no DBA can work in a vacuum. I am lucky that I have a very talented and dedicated developer named Jeff T.  His only vice is that he really likes Fritos!

SET STATISTICS IO ON

-- Before making adding index *****************************

-- 1 second execution time, 65% of cost of batch
-- Clustered Index Seek on FooBarSubscription (82%), Index Seek on FooBarInfo (0%), Nested Loops (17%)
-- Table 'FooBarSubscription'. Scan count 145930, logical reads 620737, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'FooBarInfo'. Scan count 1, logical reads 508, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SELECT FeedID    
FROM dbo.FooBarSubscription AS MS WITH (NOLOCK)    
INNER JOIN dbo.FooBarInfo AS MI WITH (NOLOCK)    
ON MS.FooBarID = MI.FooBarID 
AND MS.UserID = MI.UserID   
WHERE MI.FooBarIdStr = 'EMail Alerts'   OPTION (LOOP JOIN) -- Force a LOOP JOIN


-- 23 second execution time, 35% of cost of batch
-- Clustered Index Scan on FooBarSubscription (60%), Index Seek on FooBarInfo (0%), Merge Join (40%)
-- Table 'FooBarSubscription'. Scan count 1, logical reads 92356, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'FooBarInfo'. Scan count 1, logical reads 508, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT FeedID    
FROM dbo.FooBarSubscription AS MS WITH (NOLOCK)    
INNER JOIN dbo.FooBarInfo AS MI WITH (NOLOCK)    
ON MS.FooBarID = MI.FooBarID 
AND MS.UserID = MI.UserID   
WHERE MI.FooBarIdStr = 'EMail Alerts' 


-- After adding unique non-clustered index on FooBarID, UserID, FeedID of FooBarSubscription table  ***************

-- 1 second, 63% of cost of batch
-- Index Seek on FooBarSubscription (80%), Index Seek on FooBarInfo (0%), Nested Loops (20%)
-- Table 'FooBarSubscription'. Scan count 145949, logical reads 465557, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'FooBarInfo'. Scan count 1, logical reads 508, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT FeedID    
FROM dbo.FooBarSubscription AS MS WITH (NOLOCK)    
INNER JOIN dbo.FooBarInfo AS MI WITH (NOLOCK)    
ON MS.FooBarID = MI.FooBarID 
AND MS.UserID = MI.UserID   
WHERE MI.FooBarIdStr = 'EMail Alerts'   OPTION (LOOP JOIN) -- Force a LOOP JOIN


-- 13 seconds, 37% of cost of batch
-- Index Scan on FooBarSubscription (52%), Index Seek on FooBarInfo (0%), Sort on FooBarInfo (7%), Merge Join (41%)
-- Table 'FooBarSubscription'. Scan count 1, logical reads 67360, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'FooBarInfo'. Scan count 1, logical reads 508, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT FeedID    
FROM dbo.FooBarSubscription AS MS WITH (NOLOCK)    
INNER JOIN dbo.FooBarInfo AS MI WITH (NOLOCK)    
ON MS.FooBarID = MI.FooBarID 
AND MS.UserID = MI.UserID   
WHERE MI.FooBarIdStr = 'EMail Alerts' 
 
Technorati Tags:

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s