May 11, 2016 at 7:28 am
Hello all!
We have a database which sadly is a heap (can't change because it's a vendor database).
We added a non-clustered index to the table in order to speed up a select query against that table (query does not come from the app, it's for reporting).
The query went a lot faster but a different select query went from .5 seconds to 10 seconds.
The odd part is that the second query did not use the new index, it used indexes already there.
We have removed the new index, but I'm at a loss as to why the second query would be affected if it continued to use indexes which already existed. Is this something unique to a heap? I've added indexes before and queries either used the new index or they ignored it and continued to use what was there.
May 11, 2016 at 7:42 am
jigging.tech (5/11/2016)
Hello all!We have a database which sadly is a heap (can't change because it's a vendor database).
We added a non-clustered index to the table in order to speed up a select query against that table (query does not come from the app, it's for reporting).
The query went a lot faster but a different select query went from .5 seconds to 10 seconds.
The odd part is that the second query did not use the new index, it used indexes already there.
We have removed the new index, but I'm at a loss as to why the second query would be affected if it continued to use indexes which already existed. Is this something unique to a heap? I've added indexes before and queries either used the new index or they ignored it and continued to use what was there.
Post actual execution plans please.
😎
May 11, 2016 at 8:07 am
Was this slowness repeatable and consistent? Was the database under load and/or did you NOLOCK your SELECT?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 11, 2016 at 8:20 am
As Eirikur suggested, actual (not the estimated ones) execution plans would be helpful. I suspect that this has something to do with bad/out-of-date statistics.
-- Itzik Ben-Gan 2001
May 11, 2016 at 8:23 am
Without seeing the execution plans, I'd just be guessing.
My guess is, you may have hit some bad parameter sniffing on the query that was running fast previous to the addition of the index because it recompiled differently... Maybe. Like I said, guessing without data.
----------------------------------------------------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
May 11, 2016 at 9:06 am
Please post the Execution plans before and after. I have faced similar situation recently and had created a process to save the execution plans which would come in handy in situations like this.
Please re run the queries and get a consistent results.
Peace.
May 11, 2016 at 9:10 am
Grant Fritchey (5/11/2016)
Without seeing the execution plans, I'd just be guessing.My guess is, you may have hit some bad parameter sniffing on the query that was running fast previous to the addition of the index because it recompiled differently... Maybe. Like I said, guessing without data.
I was hoping that we could avoid this normal "post the actual plans" 5-6 posts, guessing is no good for anything but gathering posting points;-)
😎
May 11, 2016 at 9:19 am
Eirikur Eiriksson (5/11/2016)
Grant Fritchey (5/11/2016)
Without seeing the execution plans, I'd just be guessing.My guess is, you may have hit some bad parameter sniffing on the query that was running fast previous to the addition of the index because it recompiled differently... Maybe. Like I said, guessing without data.
I was hoping that we could avoid this normal "post the actual plans" 5-6 posts, guessing is no good for anything but gathering posting points;-)
😎
Probably right. I just wanted to offer up something, anything, to help out. I'll try to refrain from guesses.
----------------------------------------------------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
May 11, 2016 at 9:32 am
Grant Fritchey (5/11/2016)
Eirikur Eiriksson (5/11/2016)
Grant Fritchey (5/11/2016)
Without seeing the execution plans, I'd just be guessing.My guess is, you may have hit some bad parameter sniffing on the query that was running fast previous to the addition of the index because it recompiled differently... Maybe. Like I said, guessing without data.
I was hoping that we could avoid this normal "post the actual plans" 5-6 posts, guessing is no good for anything but gathering posting points;-)
😎
Probably right. I just wanted to offer up something, anything, to help out. I'll try to refrain from guesses.
I know Grant, I (far too) often do the same
😎
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply