November 1, 2010 at 2:19 pm
I've got a very strange issue. While tuning a query I added a index on a column in our staging environment and the query on this particular table is doing a whole lot less I/O now. It previously was doing a clustered index seek. Now its doing an index scan on the new index. Our staging database is basically a mirror image of our production database. When I applied the same index to this table in production its still doing a clustered index seek. In this case its cheaper to do the scan than the seek, but, I can't get the optimizer to pick the scan over the seek. Does anyone know why this would be? the databases are on the same server.
November 1, 2010 at 2:45 pm
November 1, 2010 at 3:09 pm
It will seek when selectivity makes sense for it. Now, can you give us more context of if this is the same proc/query on both servers? You might want to force a recompile on the proc then to make sure it sees the new index/statistics.
Need a bit more detail about what's the same, and what you're doing differently, on each server.
At a guess it's statistics based (rebuild your clustered index's statistics on the table), cached procedure/query compile, or something similar to that, since prod's been around longer it sounds like.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2010 at 3:12 pm
Probably SQL is using a cached query plan. Try doing a DBBC DROPCLEANBUFFERS.
Note that this may impact performance while new query plans are built and cached. You can get the query plan handle and only drop that plan if you want.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 1, 2010 at 6:57 pm
I'd have to see the execution plans for both queries before I could hazard a guess. It might be that adding the index caused the query to recompile. It might be adding the index changed the stats... It's just hard to know without the information in front of you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply