March 20, 2011 at 5:26 pm
I am so lost here. I dont know what to do. I have a table1 inside a db1, the view using table1 completes less than a second. I have another database db2 with table 2 in it, table1 and table2 are EXACTLY the same(basically they are copies) , when i run the same view against this table2 is takes 32 seconds. Both the tables have exactly same data (checked with Red Gate Data compare), statistics on table2 were missing but i created them just like table 1, db options are exactly the same . I am not sure what else to check . Why is my view running slow against table2? I profiler trace i saw a sort warning. Is there anywhere else i can check?
March 20, 2011 at 6:16 pm
Start with the execution plans, find the difference.
My guess is indexes are different.
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
March 21, 2011 at 5:43 am
If you've validated the data with SQL Data Compare, also validate the structure with SQL Compare. I'll bet other things are missing.
When you're not sure what a query is doing or why it's running slow, you should always look first to your execution plans as Craig said. Rather than try to guess what might be different, you can look at the execution plans and quickly arrive at what exactly is different between the two executions.
Another possibility, are there differences in the physical machines, memory or CPU? Are there differences in transaction load between the two systems? You could be hitting resource contention.
"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 21, 2011 at 10:30 am
I think i should have provided more details. I certainly did look at the execution plan and yes the actual number of rows on one of the tables is which is joining on table2 is wayyyyy more. But the schema,indexes,data,statistics are absolutely same and yes i am running these two databases on the same test server. There is no memory,no cpu,no IO issue at all. MY "guess" is the copy of the database is missing some configuration( these are replicated copies) apparently i couldn't find the difference not even through Red Gate Schema compare.I dont know where else to look for..i am still doing some more testing..i will follow up. Please let me know if there is something i can look at it.
March 21, 2011 at 11:11 am
another pair of eyes can certainly help; can you post the execution plans from each server so we can see them too?
so many talented folks here, one person might see something i might gloss over, and vice versa;
Lowell
March 21, 2011 at 12:37 pm
Another update, now when i drop fk/truncate/insert data back/ add fk the good table(table1) also gives bad performance? Why would that be.. In the interim i will tryt o get exec plan.
March 23, 2011 at 9:12 am
I am still stuck here? Any ideas ?
March 23, 2011 at 9:24 am
Not without execution plans to compare. It's just too hard to know what's going on since we're not sitting there.
"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 23, 2011 at 5:40 pm
sqldba_icon (3/21/2011)
Another update, now when i drop fk/truncate/insert data back/ add fk the good table(table1) also gives bad performance? Why would that be.. In the interim i will tryt o get exec plan.
That sounds like an old plan was compiled in memory, and when you rebuilt the 'good' table, it rebuilt statistics and what not so recompiled the same way as the other one.
Index tipping point, perhaps, with stale statistics combined?
Really need the execution plans and some ddl here to help you out of this, unfortunately.
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
March 24, 2011 at 7:48 am
There are umpteen kajillion reasons why performance varies from one database to another, one query execution to another, etc. Lifetimes have been spent learning what those things are, how to identify them and what to do about them. Without execution-time information (and lots of ancillary information too) we cannot possibly help you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 25, 2011 at 7:38 am
It helps if you give all information properly and completely. From your bits and pieces, the following has come out.
1) The databases are similar, and on the same physical machine
2) Originally you mentioned only Table1 in Db1, and Table2 in Dbs, then mentioned the existence of other joined tables
3) The structure of Table1 and Table2 matches perfectly. (what about joined tables?)
4) One of the *joined* tables have, as you put it: "... one of the tables is which is joining on table2 is wayyyyy more... "
I don't know whether you realize it, but when you have multiple tables in a statement, the data in all the tables affect the query. Thus the indexes may be the same, but if the indexes are not proper, then your query will suffer.
Thus even the execution plan "may" show up as the same (same path, not time), but you'll have different results since the query is hitting on different volumes of data.
So, first make ALL the tables have same data and structures. I repeat, ALL the tables. Now run your query.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply