June 2, 2009 at 8:54 am
Hi,
I have a stored procedure with the following code:
ALTER PROCEDURE [dbo].[page_fixtures_getSeasonFixtures]
@season AS int,
@teamID AS int
AS
BEGIN
DECLARE @start DATETIME
DECLARE @end DATETIME
SELECT TOP 1 @start = startTime FROM seasons WITH (NOLOCK) WHERE id = @season
SET @end = DATEADD(d,108,@start)
SELECT matchid, awayScore, homeScore, startTime, endTime, matchtype, hometeamid, awayteamid, player1a, player1h, teamnamea, teamnameh, isnull(teamnamea, '') as teamnametest, isnull(endTime,'2099-01-01') AS endTimeXML
FROM match m WITH (NOLOCK)
WHERE (season = @season ) AND (homeTeamID = @teamID OR awayTeamID = @teamID)
UNION ALL
SELECT matchid, awayscore, homescore, startTime, endTime, 100 AS matchtype, leagueid AS hometeamid, leagueid AS awayteamid, adc11 AS player1a, hdc11 AS player1h,
CASE WHEN hostteamconf=1 THEN 'Big 8' ELSE 'Great 8' END AS teamnameh,
CASE WHEN hostteamconf=1 THEN 'Great 8' ELSE 'Big 8' END AS teamnamea,
'' as teamnametest, isnull(endTime,'2099-01-01') AS endTimeXML
FROM allstar_match m WITH (NOLOCK)
WHERE m.season = @season AND m.leagueid = (SELECT leagueid FROM teams WITH (NOLOCK) WHERE teamid=@teamid)
ORDER BY startTime
END
When I run this stored procedure with some pair of values (in this case, 9,22793), it takes an average of 45 seconds to run with the attached actual execution plan sp_executionplan.
In the management studio, I can run the following identical code:
DECLARE @season INT
SET @season = 9
DECLARE @teamid INT
SET @teamid=22793
DECLARE @start DATETIME
DECLARE @end DATETIME
SELECT TOP 1 @start = startTime FROM seasons WITH (NOLOCK) WHERE id = @season
SET @end = DATEADD(d,108,@start)
SELECT matchid, awayScore, homeScore, startTime, endTime, matchtype, hometeamid, awayteamid, player1a, player1h, teamnamea, teamnameh, isnull(teamnamea, '') as teamnametest, isnull(endTime,'2099-01-01') AS endTimeXML
FROM match m WITH (NOLOCK)
WHERE season = @season AND (homeTeamID = @teamID OR awayteamID = @teamid)
UNION ALL
SELECT matchid, awayscore, homescore, startTime, endTime, 100 AS matchtype, leagueid AS hometeamid, leagueid AS awayteamid, adc11 AS player1a, hdc11 AS player1h,
CASE WHEN hostteamconf=1 THEN 'Big 8' ELSE 'Great 8' END AS teamnameh,
CASE WHEN hostteamconf=1 THEN 'Great 8' ELSE 'Big 8' END AS teamnamea,
'' as teamnametest, isnull(endTime,'2099-01-01') AS endTimeXML
FROM allstar_match m WITH (NOLOCK)
WHERE m.season = @season AND m.leagueid = (SELECT leagueid FROM teams WITH (NOLOCK) WHERE teamid=@teamid)
ORDER BY startTime
This code takes only 200 ms and has the attached actual execution plan console_executionplan.
Obviously these two are different, and the SP execution plan is much, much slower than the console one. Any idea why the SP has chosen the wrong execution plan? More to the point, what is the best way to prevent this from happening?
Thank you for the help,
-Charles
June 2, 2009 at 9:03 am
Parameter sniffing?
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2009 at 9:27 am
Thank you for the suggestion.
I think this might be the explanation. In the console (short) version, only the 376 rows of match (the big table involved here) with (hometeamid=@teamid OR awayteamid=@teamid) were scanned, of which 28 were returned. I'm not sure how it knew to only look at those 376.
In the SP (long) version, it looked at all 1977491 rows with season = 9 in PK_match.
When looking at how many entries there are in match by season, I get:
season
----------- -----------
3 0
64157 1
262626 2
1225174 3
1883409 4
2047626 5
2070000 6
2227317 7
2379209 8
1977497 9
Those 3 with season=0 are test entries, but I suppose might have caused the compiler to decide that 3 was a typical number of entries and to optimize accordingly?
I moved those entries to a different season and ran freeproccache (this was presumably not the only SP that would have been confused by something like this; there were a couple of others with similar behavior).
However, upon doing so the same problem remained; it did the season lookup first.
Anyway, as much as I'd like to understand the problem, this is also a live site where I'd like to patch it ASAP and that probably comes first. What's the best way to hint at the right execution plan in this case so that it will do its seek on the proper piece of the WHERE clause first?
But also, parameter sniffing made a lot of sense as the explanation, so I'm wondering whether why moving the matches and recompiling would not have fixed it. Is there something I missed, or does that actually rule this out as an explanation? It certainly made a lot of sense...
Thank you again,
-Charles
June 2, 2009 at 9:39 am
Could be bad estimates. Haven't checked exec plans in detail. Check that the statistics are up to date (maybe do an update with full scan). If not, go with the variable suggestion in the blog post that I linked.
In the proc, the optimiser can see the values of the parameters and compiles based on those. In the query it can't (they're variables) and as such the query is compiled for average row distribution.
I wouldn't go sticking hints in without a lot of testing first.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply