SQL Server 2008 Odd Query Execution Plans (bugs?)
- by Scott Whigham on June 12, 2009 1:13 PMI’m trying to understand what SQL Server 2008 is up to… So here’s what’s happening: I create a stored proc (or a view – both have this problem) and, when executing the proc/view, the cost is 10-20x more expensive than directly executing the query that the proc/view is built upon.
- In one example that used a view, the cost of a query with a WHERE clause was 1.0. The cost of the query that the view is built on by itself (without referencing the view) returns 0.05 seconds.
- In another example, I execute a stored procedure and the cost is 3.5. I run the query that is inside the stored procedure by itself and the cost is 0.023.
Here are the results of my tests using the exact same database across multiple versions:
The tables:
- dbo.Downloads – 1,000,000 rows (INTs, BITs, and DATETIMEs)
- dbo.Classes – 20 rows, each about 5k
- dbo.Videos – 2300 rows, each about 3k
- dbo.ClassVideos – 2300 rows, each about 500b
The servers:
- Server #1
- Environment: SQL Server 2005 Developer Edition
- Build: Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) (SP3)
- Database compatibility level: 90
- Server #2
- Environment: SQL Server 2008 Standard Edition
- Build: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0
- Database upgraded to compatibility level 10
- Server #3
- Environment: SQL Server 2008 Developer Edition
- Build: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0
- Database upgraded to compatibility level 10
- Server #4
- Environment: SQL Server 2008 Developer Edition
- Build: Microsoft SQL Server 2008 (SP1) - 10.0.2714.0
- Database upgraded to compatibility level 10
- Server #5
- Environment: SQL Server 2008 Developer Edition
- Build: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
- Database upgraded to compatibility level 10
Database and connection settings
- Collation: SQL_Latin1_General_CP1_CI_AS
- Auto create stats: ON
- Auto update stats: ON
- ANSI NULL default: OFF
- ANSI NULLs: ON
- ANSI Padding: ON
- ANSI Warnings: ON
- Arith Abort: ON
- Concat NULLs Yields NULL: ON
- Numeric Roundabort: OFF
The Queries
The “base” query is this:
DECLARE @Class INT;
SET @Class = 232
DECLARE @RequestedVideo INT;
SET @RequestedVideo = 102
DECLARE @NumberOfVideosToReturn INT;
SET @NumberOfVideosToReturn = 5
SELECT DISTINCT TOP(@NumberOfVideosToReturn) v.Video, v.Title, v.VideoSkillLevelId
, cv.Class, cv.NumberOfDownloads
FROM dbo.ClassVideo cv JOIN dbo.Downloads dl
ON cv.Video = dl.Video
AND cv.Class = dl.Class
JOIN dbo.Videos v
ON v.Video = dl.Video
WHERE dl.Class = @Class
AND dl.Video <> @RequestedVideo
ORDER BY cv.NumberOfDownloads DESC, v.Title
If this looks like a query from LearnItFirst.com, my online training video company, you’re right!
Now, If I wrap it into a stored procedure, it simply becomes:
CREATE PROC TestMe (@NumberOfVideosToReturn INT, @Class INT, @Video INT)
AS
SELECT DISTINCT TOP(@NumberOfVideosToReturn) v.Video, v.Title, v.VideoSkillLevelId
, cv.Class, cv.NumberOfDownloads
FROM dbo.ClassVideo cv JOIN dbo.Downloads dl
ON cv.Video = dl.Video
AND cv.Class = dl.Class
JOIN dbo.Videos v
ON v.Video = dl.Video
WHERE dl.Class = @Class
AND dl.Video <> @RequestedVideo
ORDER BY cv.NumberOfDownloads DESC, v.Title
GO
The Results
| SQL 2005 SP3 | SQL 2008 DE RTM | SQL 2008 DE SP1 | SQL 2008 DE SP1 CU2 | SQL 2008 SE SP1 | |
| Base query cost | 0.115 | 0.113 | 0.113 | 0.113 | 0.113 |
| Stored proc cost | 0.022 | 3.132 | 3.132 | 3.132 | 3.103 |
WTH?
So what the hell? I don’t get it. Why is SQL Server 2008 so much slower than SQL Server 2005 in this instance? I honestly don’t have a clue :(
If I were to hazard a guess, I'd start with looking into parameter sniffing. If the distribution for the value being stored in the plan doesn't represent a typical value distribution then the plan can be sub-optimal.
Try the following for the procedure:
CREATE PROC TestMe (@NumberOfVideosToReturn INT, @Class INT, @Video INT)
AS
DECLARE @VarClass int
,@VarVideo int
SEELCT @VarClass = @Class
,@VarVideo = @Video
SELECT DISTINCT TOP(@NumberOfVideosToReturn) v.Video, v.Title, v.VideoSkillLevelId
, cv.Class, cv.NumberOfDownloads
FROM dbo.ClassVideo cv JOIN dbo.Downloads dl
ON cv.Video = dl.Video
AND cv.Class = dl.Class
JOIN dbo.Videos v
ON v.Video = dl.Video
WHERE dl.Class = @VarClass
AND dl.Video > @VarClass
ORDER BY cv.NumberOfDownloads DESC, v.Title
GO
Thanks, Jason. I applied that "technique" (for lack of a better word) and it went from a cost of 3.x down to 0.17. Clearly that was the issue - thank you.
It's odd that this would be the issue here. The query value is a primary key (Class+Video) so the distribution is "1 of n" where "n" is the number of rows in that table.