July 16, 2011 at 3:20 pm
I have one same query run in 2 different db (dev and live) on same machine, same sql instance, same table structure with same indexes. it returned clustered index seek (seek a clustered id index) from a dev db table, but index scan (scan a different nonclustered index) from live. the difference is dev db has 70k records less than live db, out of 65M total. And actually, fragment % in dev is higher than in live. I can't tell this problem happened before or after very recent upgrade to 2008 R2 (most likely after).
does this 70K records make such a big difference or 2008 R2 does something different? what can I do next? Thanks for any help
July 16, 2011 at 3:43 pm
Have you examined the execution plan?
Have you tried considered updating the statistics, checking the fragmentation, recreating the indexes etc?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 16, 2011 at 7:10 pm
yes, I have checked the execution plan, statistics and fragmentation, but haven't recreated indexes yet. we have a weekly job taking care of indexes on weekends.
July 16, 2011 at 7:30 pm
shanghaigirl-1125377 (7/16/2011)
yes, I have checked the execution plan, statistics and fragmentation, but haven't recreated indexes yet. we have a weekly job taking care of indexes on weekends.
You may want to post the execution plan and provide additional details so that someone can help you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 16, 2011 at 8:47 pm
i wish i could post execution plan.
my assumption is if the same query runs on the same environment same table structure (one with slightly less records in my case) same indexes should return same execution plan. but obviously, my assumption isn't correct. can you explain why? Thanks.
July 16, 2011 at 9:38 pm
We're definately going to need more information to help you puzzle this out.
can you provide the DDL to the table in question, and the actual execution plans? This isn't something that's going to be simple to nail down. My guess is the data distribution is different, you possibly have outdated statistics in prod, and you're going to want to doublecheck all the indexes/constraints/keys, etc in dev vs. prod.
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
July 17, 2011 at 8:41 am
thanks for your suggestions. understood my best is to post ddl and execution plan, but...
I will go back and double check statistics, fk etc.
July 17, 2011 at 6:51 pm
shanghaigirl-1125377 (7/16/2011)
my assumption is if the same query runs on the same environment same table structure (one with slightly less records in my case) same indexes should return same execution plan. but obviously, my assumption isn't correct. can you explain why?
You are correct, your assumption is wrong.
Different number of records means different data distribution for saying the least therefore even if performance statistis are up-to-date in both environments they will present optimizer with a different picture of the content of the table.
Let me give you a very basic example.
Version 1 of the table has 1,000,000 rows - 100,000 of them match WHERE clause condition.
Version 2 of the table has 500,000 rows - 100,000 of them match WHERE clause condition.
In this circumstances optimizer will most likely do an index scan for version 1 of the table but a full table scan for version 2 of it.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 17, 2011 at 11:30 pm
Is it significance difference in records in table of live and dev. database?
July 18, 2011 at 1:35 am
shanghaigirl-1125377 (7/16/2011)
... the difference is dev db has 70k records less than live db, out of 65M total. And actually, fragment % in dev is higher than in live.
It could be due to parameter sniffing. Recompile the SPs and see.
July 18, 2011 at 7:49 am
how do I know at how many records it changes the plan? is there anyway to calculate the threshold?
July 18, 2011 at 8:01 am
shanghaigirl-1125377 (7/16/2011)
yes, I have checked the execution plan, statistics and fragmentation, but haven't recreated indexes yet. we have a weekly job taking care of indexes on weekends.
Did your Indexes get recreated this past weekend?
I thought that you were going to post the information requested?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 20, 2011 at 10:11 am
i think this article sort of answered my question:
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply