June 6, 2011 at 11:17 am
I have two tables. One table contains more than 10000000 records. Second table contains five records. When we join it, the result comes just 2000 records. But this takes too much time, very high IO and fills lot of space. We don't have clustered index on the big table, but 2 nonclustered indexes which are getting used by the query [It shows index seek in the execution plan]. The table has partitioning base don round robin but all the data exists in the same partition.
June 6, 2011 at 11:27 am
can you post the execution plan as a .sqlplan file here as an attachment?
maybe we can see something you overlooked? a second pair of eyes never hurts!
also, why is there no clustered index on the table? there must be a reason, I'm sure.
Lowell
June 7, 2011 at 6:46 am
Unless those are covering non-clustered indexes (meaning, all columns needed by the query are included in the index), then you're probably also seeing an RID operation. Posting the execution plan will help us help you figure that out.
"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
June 8, 2011 at 4:24 am
Check your execution plan, and Find out any bulk data movement by the thickness of line.
Otherwise change in join order of tables in query may solve your issue..
Cheers
-Saurabh
June 8, 2011 at 5:16 am
sql@sql (6/8/2011)
Check your execution plan, and Find out any bulk data movement by the thickness of line.Otherwise change in join order of tables in query may solve your issue..
Cheers
-Saurabh
A change in the join order is only likely to help if you're getting optimizer timeouts on the query. Otherwise, the optimizer itself tries different join orders as part of the optimization process, so you don't need to sweat it. Plus, just random changes to the join order are unlikely to help. Instead you need to focus on a join order that eliminates excess rows through inner joins as fast as possible with outer joins left until later. But again, you're better off letting the optimizer handle this.
"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
June 9, 2011 at 8:02 am
1) are you getting partition elimination in the query? my guess is not - either your query isn't written to allow this or you are getting screwed by the suboptimal partitioning implementation in sql 2005
2) are indexes partition aligned?
3) hmm, how do you have partitioning without clustered index??
4) are you getting a CONVERT_IMPLICIT that is causing an index scan instead of a seek?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 9, 2011 at 9:07 am
BOL says you can partition a heap...not that anyone would, would they? Uggh.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 10, 2011 at 1:45 pm
opc.three (6/9/2011)
BOL says you can partition a heap...not that anyone would, would they? Uggh.
Guess that should have been a "why" instead of a "how". :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 10, 2011 at 1:59 pm
TheSQLGuru (6/10/2011)
opc.three (6/9/2011)
BOL says you can partition a heap...not that anyone would, would they? Uggh.Guess that should have been a "why" instead of a "how". :hehe:
:hehe: I only picked up on it because the details are fresh in my mind (at the moment, they'll fall out of my left ear pretty soon). This is the third thread in as many days I have seen where a heap was involved in a performance issue...go figure right?
And it does not discriminate when it comes to the size of the table either. This thread is a good read Kevin: http://www.sqlservercentral.com/Forums/Topic1122650-391-1.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 13, 2011 at 2:22 am
is there any other load of DML on these tables ?
Provide Execution Plan ?
Check Statistics of these tables are updated ?
Check Fragementation of Indexes of these tables ?
Create Cluster index on numeric or int data type if possoble
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 17, 2011 at 2:48 am
Hello All, I don't know why but changing the order of join made the difference. The query is running quite fast now. How does it works anyways ? Anyone ?
June 17, 2011 at 3:22 am
Not seeing the query or the execution plan, I have no idea.
As a guess, I'll go back to what I said before, you're getting timeouts on the optimization process and changing the join order (assuming that's all you did, and not changing join types, filter criteria, or all kinds of other stuff) puts the joins in an order that the optimizer would have found on it's own if it hadn't timed out.
But I'm just speculating because I can't see what's 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
June 18, 2011 at 9:35 am
sqlnaive (6/17/2011)
Hello All, I don't know why but changing the order of join made the difference. The query is running quite fast now. How does it works anyways ? Anyone ?
Most folks will insist that the join order absolutely doesn't matter and, in theory, they'd be correct... that the Optimizer will try a few different ways. While the Optimizer will, indeed, try a few different ways, it's not a "perfection" optimizer and it will "settle" for what it thinks is a "good enough" plan.
Now, people like Grant and Gail will probably admonish me for saying so, but what I've found in practice is that the order of the joins does matter. It's like giving the optimizer the ultimate "hint" as to how to attack the problem. (As a side bar, it's my nature to always resolve the smallest number of rows using the smallest tables first because it's easier for my tired old brain to understand that way :-P). Still, the optimizer may decide to ignore such a powerful hint and the next time the optimizer has to recompile a plan for the query, it could choose the longer method as a "good enough" plan.
So, if you're absolutely 100% sure that you're smarter (in this case) than the folks who built the optimizer and that the order of joins you establish will be the right one even as scale increases, you can force the Optimizer to do things your way by using OPTION(FORCE ORDER).
As a bit of a sidebar and quite contrary to current sensibilities, I've never had to use the FORCE ORDER query hint simply because I do, in fact, write my joins so that the smallest tables usually come first and the fewest number of rows are resolved as quickly as possible especially in multi-join queries.
I'll also add that it's quite possible for the reverse (largest table first) to be true depending on the indexes involved and a whole bunch of other things. I'll end this post with my normal "It Depends" and, like the Optimizer does, you should try more than one order in the joins. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2011 at 11:13 am
Jeff, I'd never admonish you... much.
However, in this case, I agree, but only to a point. JOIN order does matter, and your choice of JOIN order, especially on more complex queries, can absolutely impact the execution plan that gets delivered. Up to there, we're in 100% agreement.
I'm hesitant to suggest using the hint though. I've seen situations where it not only made for a faster query, but sped up the optimizer as well. However, those queries were already edge cases, joining too many tables (>50 is too many, <30 is no problem) together and they needed help. I just like to be very cautious about suggesting the use of hints. I've seen way too many times where they were used in one query to fix one problem and then became a standard fixture in every query going forward, despite the fact tht they weren't helping those queries and were, in fact, hurting them.
Other than that nit though, we're 100% in agreement.
"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
June 19, 2011 at 5:30 pm
Grant Fritchey (6/18/2011)
I've seen way too many times where they were used in one query to fix one problem and then became a standard fixture in every query going forward, despite the fact tht they weren't helping those queries and were, in fact, hurting them.
I absolutely agree. I didn't take the human-element into account. Reminds me of what some folks did with WITH(NOLOCK)... Despite my protests, it actually became a requirement at one of my old jobs.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply