May 13, 2009 at 4:26 am
Hi,
I have a table with about 350000 records. I'm using a stored procedure to query the table and order the results by one of the columns.
The query usually requests one or two items and is like this
Select TOP(@numItems) t1.id, t3.name, t1.value
FROM t1
JOIN t2 on t2.id = t1.id
LEFT JOIN t3.name on t2.name = t3.name
ORDER BY t1.value
Creating an index on t1.value has little effect.
When asking for just 2 items to be returned, at best, the query can take about half a second and executes with around 5000 reads.
However, if I re-compile the stored procedure, even without changing the code or data in the tables, the query will often take up to 7 seconds and requires about 1.8million reads! The same happens if the server is restarted
The only way I can restore it to the faster execution, is by rebuilding any index on any of the referenced tables. I don't understand why this changes the execution, because no data has changed in the database.
Having studied the execution plans for the two different results, it shows that when the query takes a long time to execute, its estimated execution plan thinks that it only needs to deal with one or two rows, but the actual execution handles about 300000 rows and therefore opts not to use parallel execution.
When the query executes quickly, the estimated number of rows affected is accurate and the execution plan uses parallelism.
Can someone please help me understand what is going on and how SQL Server can get the query so wrong after just recompiling the stored procedure?
Also why rebuilding an index would change that, even though nothing had changed in the database between the last time the index was built and the recompilation of the stored procedure?
Thanks,
Simon ;O)
May 13, 2009 at 7:14 am
I'm guessing that SQL's making a bad estimate based on that top. Not sure why. Can you post the two execution plans? (Saved as .sqlplan files, zipped and attached)
Suggestion on some indexes, might help:
t1 (value, id) -- also try opposite order
t2 (id, name) -- also try opposite order
t3 (name)
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
May 13, 2009 at 7:19 am
Maybe look into Plan Guides.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 13, 2009 at 7:44 am
The actual plans for the long and short queries are attached.
Thanks 🙂
May 14, 2009 at 2:51 am
Thanks Jack,
I'll look into that, though it would be good to know why the problem is occurring in the first place.
Simon ;O)
May 14, 2009 at 6:39 am
well as the number of possible rows can vary according to the parameter the query would likely vary quite a bit, I have an example which generates three different plans from the same query based upon three different number of rows ( a bit more complex but on the same lines as yours ) you may well have to put a with recompile in the proc and take the hit - or write procs to deal with the different numbers of returned rows.
This is a classic example of queries that catch you out and is perfectly normal ( I'd say )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 14, 2009 at 6:43 am
actually I probably didn't explain myself very well .. I'd expect this type of behavior and without "seeing" the code running in front of me it's hard to make a solid suggestion.
you could try creating a covered index and see if that helps - or even an indexed view of the base query to see what happens ( as a test )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 15, 2009 at 9:25 pm
I presume you use a where clause somewhere along the way. I tend to see this type of issue when the statistics get out of date and I would assume that caching is involved somewhere in you issue as the restart and recompile makes things worse. Is the data changing frequently? I have found that SQL Server does not always update stats even when auto update is switched on as it depends on how busy the server is. We have had to run a night job to force updating of stats. So check the stats on the three tables concerned to see when they were last updated and see if updating them makes a difference.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply