March 26, 2007 at 2:54 am
Hi,
I have two databases DB_A and DB_B that are almost the same from what I can tell from the Management Studio database properties.
I am executing a query (select *) on two views VW_A and VW_B having the same structure and accessing almost identical data (the data used in VW_A is a copy from the data used in VW_B).
The execution plans are almost the same (appart from the different databases), 99% of the time is being used for just one index scan on a table that is shared between both views!
But: VW_A takes 5 minutes, VW_B only 7 seconds.
Rebuild of all indexes didn't help.
The client statistics differ only in one aspect: The client execution time on A is 300000 on B 4000 (the total execution time being slightly higher).
Have you any idea how to find out the cause for that gap?
Thanks in advance
Alexander
March 26, 2007 at 12:23 pm
What about scans & I/O? Are they on identical servers? What about weirder stuff like tempdb usage or log backups or other processes causing locks, etc.
Still, the difference between almost identical and absolutely identical can be the base cause.
"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
March 26, 2007 at 1:22 pm
So many factors to compare. Hardware 1for1 on each, hardware configured exactly the same, server exactly the same, same resources running or otherwise in use on each. If if they are on the same server you have to be sure of the memory footprint being used by your actions, just so much that can cause this that it could be real interest.
Also if seperate servers if called from server b then all the data may be being read to server b then filtered. Could keep going.
March 27, 2007 at 1:45 am
Are the statistics up to date on both databases? Are the indexes on one more fragmented than on he other?
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
March 28, 2007 at 5:58 am
This is another repeating post we seem to constantly run and rarely get any feedback as to why the differences. identical databases on identical servers will run the same. Any slight variation and you may see differences - this is a basic rule of stress and uat testing as anyone who runs a test environment will tell you.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply