July 20, 2009 at 7:08 am
Hello everyone! As so often in these forums, my story starts with a SP that worked beautifully until the weekend, and then suddenly performed very badly. The database stores school assessment data. For one of the reports I join a temporary table, 15 rows long, PK pupil, clustered index on pupil, to the Pupils table, 3.6 million rows, PK pupil, clustered index on pupil. It has always done a clustered index seek to retrieve the corresponding 15 rows, but has decided to do a scan of all 3.6 million instead.:w00t: Can anyone think why it has done this, and how I can put it back?
According to DBCC CHECKDB, everything is fine.
I have rebuilt the clustered index on Pupils to ensure the statistics are up-to-date.
I've run it with OPTION (RECOMPILE) on the offending query to make sure its not cached something stupid.
I've been out for cakes, but sugar and coffee can't solve everything, it turns out.
I've attached the offending .sqlplan
...OK, how do I make my attachment show up here? I clicked "Edit Attachments", chose my file, uploaded it, it says I'm using 47kb, but nothing appears...help!
Well there you go - it seems it won't attach a file from the desktop. Here's the plan.
July 20, 2009 at 7:14 am
I can't see the .sqlplan file. Can you post it again?
It sounds like you might be seeing parameter sniffing.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 20, 2009 at 7:25 am
Edit: Nevermind....
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
July 20, 2009 at 7:46 am
Hi Grant,
I don't understand how it could be parameter sniffing - surely having an OPTION (RECOMPILE) at the end should prevent this?
Loved the book on dissecting execution plans, by the way - it has made such a difference for me - not least allowing me to work out what had gone wrong here...
July 20, 2009 at 8:01 am
Thanks. I'm glad the book was useful. Post a review on Amazon!
😀
As to this, it does look like the statistics are up to date. I'm not sure what's happening precisely. I'll bet the location of the JOIN against the Pupils table has moved, as well as changing from a seek to a scan. It probably used to be a loop too, not a hash.
Has anything changed on the system recently? New indexes? Dropped old constraints? It looks like you ought to be getting an index seek from what I can tell.
I'm not seeing anything standing out as a major issue. You might try moving the non-join criteria from the join area to a where clause, just an experiment.... I'm not sure.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 20, 2009 at 8:08 am
I can't see anything that's forcing the scan. Makes no sense...
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
July 20, 2009 at 8:14 am
GilaMonster (7/20/2009)
I can't see anything that's forcing the scan. Makes no sense...
Whew! That makes me feel better. I'm digging through this trying to spot something. I figured you were going to come back with some obvious issue that I should have spotted.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 20, 2009 at 8:16 am
Thanks for the replies - I've done a little more experimenting to get the simplest query that will still result in a table scan. Here is my SP:
ALTER PROCEDURE [dbo].[FB_EndASPECTSbaseline3] AS
IF OBJECT_ID('tempdb..#pupils') IS NOT NULL
DROP TABLE #pupils
CREATE TABLE #pupils (pupil INT PRIMARY KEY)
INSERT #pupils
SELECT pupil FROM assessments WHERE class=230894
SELECT p.pfname, p.plname
FROM #pupils z
INNER JOIN pupils p
ON z.pupil=p.pupil
ORDER BY p.pfname, p.plname
OPTION (RECOMPILE)
#pupils now contains 43 rows, but I'd still expect a seek.
So, should I try switching it off and on again?
July 20, 2009 at 8:24 am
Assuming a reasonable distribution of data, I would have expected to see a seek on this...
You're only accessing a few columns on the Pupil table, you might try using an nonclustered index, explicitly on the first & last names since that's the ordering information. Be sure to INCLUDE the other column, dob.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 20, 2009 at 8:32 am
Well, I think its my server, not my query.
I dropped and re-created my original SP and that's now doing a seek and takes 98% less time.:-P
So, thanks for the replies, but I think I can put this one down to SQL Server having a funny turn...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply