November 17, 2011 at 3:53 pm
What column is the table partitioned on?
Sql logically treats the partitioning column as a leading column of the indexes when building query plans.
November 17, 2011 at 3:53 pm
My view of index hints is simply that to use it you must somehow think that you're smarter than all the guys at ms combined and all the trillions of tests they made before choosing that path.
You might have a case exception, or not.
We can't help in any way without seeing both "good" and "bad" plans. The actual plans, not the estimates.
November 17, 2011 at 4:13 pm
agiangone (11/17/2011)
Hi Kraig,Table has 400,000,000 records and 1.4M are null but on key_2 having value 2 only 40,000 are null so the percentage is about 50% on the total but that drops to 0.001% when coupled with key_2.
I need row-widths as well. Records/Page will affect this heavily. Also, pardon me, but I'm not understanding your math.
400M records total, 1.4M are Null on Key_1. That's a 0.35% rate of NULL. Coupled with key_2 that gives me a 0.1% occurence rate. Not sure where 50% came in and I think you just forgot to transpose your decimal for the other percentage.
Remember you also need to have a an aggregate like min on column_1 and the table has to be partitioned.
Yeah, but unless you're going to post your full schema, index structure, partition schemas, and partition functions, I was going to leave this aside for the moment and see if I can recreate without the partition.
Let me know if you can replicate it.
Andrea
Will try in a bit.
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 17, 2011 at 4:15 pm
SpringTownDBA (11/17/2011)
What column is the table partitioned on?Sql logically treats the partitioning column as a leading column of the indexes when building query plans.
Hunh, news to me. Do you have a source that goes into more detail on that?
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 17, 2011 at 4:40 pm
Evil Kraig F (11/17/2011)
SpringTownDBA (11/17/2011)
What column is the table partitioned on?Sql logically treats the partitioning column as a leading column of the indexes when building query plans.
Hunh, news to me. Do you have a source that goes into more detail on that?
http://blogs.msdn.com/b/craigfr/archive/2008/08/05/partitioned-indexes-in-sql-server-2008.aspx
http://blogs.msdn.com/b/craigfr/archive/2008/07/15/partitioned-tables-in-sql-server-2008.aspx
From the First link:
Craig Freedman --5 Aug 2008 2:33 PM
In my last post, I looked at how SQL Server 2008 handles scans on partitioned tables. I explained that SQL Server 2008 treats partitioned tables as tables with a logical index on the partition id column and that SQL Server 2008 implements partition elimination by performing a logical index seek on the partition id column. Specifically, I showed some examples using a heap. In this post, I'll continue this discussion and explore how SQL Server 2008 handles scans and seek on partitioned indexes....
Connect bug related to min/max and partitions (may be cause of op's issues):
good luck!
November 17, 2011 at 4:54 pm
SpringTownDBA (11/17/2011)
http://blogs.msdn.com/b/craigfr/archive/2008/08/05/partitioned-indexes-in-sql-server-2008.aspxhttp://blogs.msdn.com/b/craigfr/archive/2008/07/15/partitioned-tables-in-sql-server-2008.aspx
Tasty and chewy, thanks SpringTown. Gods I need to get caught up with things in 2k8... Skip Scan... really?! Wouldn't seem so odd if I didn't play pool at Skip and Jan's...
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 17, 2011 at 7:10 pm
p-nut (11/17/2011)
Craig mentioned the tipping point, here is an interesting link I found on this: http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Tipping-Point-Query-Answers.aspxThat's about all I can find on this issue. I think you will have to use a hint to force it in your specific case. However, before choosing that route be sure to not just look at your SSMS window for execution time because that is misleading. Use the command I gave you before you run each of your tests. Also, make sure to test several times and maybe using OPTION(MAXDOP 1)
Jared
Jared,
very interesting article, I did not know about the "tipping point".
I understand about capturing the execution time, but we are really talking milliseconds vs several minutes.
November 17, 2011 at 7:16 pm
SpringTownDBA (11/17/2011)
What column is the table partitioned on?Sql logically treats the partitioning column as a leading column of the indexes when building query plans.
Interesting comment and that could certainly change things but the I have a different question...why is the DMV suggesting that the partitioning column is included in a missing index?
The answer to your question is:
The table is partitioned on my_column (the same column I have in the select as an aggregate, see initial post)
November 17, 2011 at 7:20 pm
Ninja's_RGR'us (11/17/2011)
My view of index hints is simply that to use it you must somehow think that you're smarter than all the guys at ms combined and all the trillions of tests they made before choosing that path.You might have a case exception, or not.
We can't help in any way without seeing both "good" and "bad" plans. The actual plans, not the estimates.
I feel the same way about hints and that is way I'm trying to understand why the optimizer is behaving as it does in this case.
However, if you read the article posted a couple of posts above (The Tipping point) you see that even someone like Kimberly Tripp is not totally against using them. After all, the same smart people at MS decided to give the users the option to use a hint...
November 17, 2011 at 7:26 pm
agiangone (11/17/2011)
Ninja's_RGR'us (11/17/2011)
My view of index hints is simply that to use it you must somehow think that you're smarter than all the guys at ms combined and all the trillions of tests they made before choosing that path.You might have a case exception, or not.
We can't help in any way without seeing both "good" and "bad" plans. The actual plans, not the estimates.
I feel the same way about hints and that is way I'm trying to understand why the optimizer is behaving as it does in this case.
However, if you read the article posted a couple of posts above (The Tipping point) you see that even someone like Kimberly Tripp is not totally against using them. After all, the same smart people at MS decided to give the users the option to use a hint...
I agree completely, but I think Ninja will too after you mentioned minutes versus milliseconds for the execution time. I think we all know that the optimizer will not be perfect, that's why the hints exist.
Jared
Jared
CE - Microsoft
November 17, 2011 at 7:35 pm
Evil Kraig F (11/17/2011)
agiangone (11/17/2011)
Hi Kraig,Table has 400,000,000 records and 1.4M are null but on key_2 having value 2 only 40,000 are null so the percentage is about 50% on the total but that drops to 0.001% when coupled with key_2.
I need row-widths as well. Records/Page will affect this heavily. Also, pardon me, but I'm not understanding your math.
400M records total, 1.4M are Null on Key_1. That's a 0.35% rate of NULL. Coupled with key_2 that gives me a 0.1% occurence rate. Not sure where 50% came in and I think you just forgot to transpose your decimal for the other percentage.
Remember you also need to have a an aggregate like min on column_1 and the table has to be partitioned.
Yeah, but unless you're going to post your full schema, index structure, partition schemas, and partition functions, I was going to leave this aside for the moment and see if I can recreate without the partition.
Let me know if you can replicate it.
Andrea
Will try in a bit.
I understand now why row width will affect this considerably. All I can say now is that is is very wide (relative term I know).
Math is all wrong..sorry. 35% is correct as it is 140M over 400M and not sure why you say 0.1% but it should be 0.01
Let me know if you are able to replicate it.
Andrea
November 17, 2011 at 7:42 pm
SpringTownDBA (11/17/2011)
Evil Kraig F (11/17/2011)
SpringTownDBA (11/17/2011)
What column is the table partitioned on?Sql logically treats the partitioning column as a leading column of the indexes when building query plans.
Hunh, news to me. Do you have a source that goes into more detail on that?
http://blogs.msdn.com/b/craigfr/archive/2008/08/05/partitioned-indexes-in-sql-server-2008.aspx
http://blogs.msdn.com/b/craigfr/archive/2008/07/15/partitioned-tables-in-sql-server-2008.aspx
From the First link:
Craig Freedman --5 Aug 2008 2:33 PM
In my last post, I looked at how SQL Server 2008 handles scans on partitioned tables. I explained that SQL Server 2008 treats partitioned tables as tables with a logical index on the partition id column and that SQL Server 2008 implements partition elimination by performing a logical index seek on the partition id column. Specifically, I showed some examples using a heap. In this post, I'll continue this discussion and explore how SQL Server 2008 handles scans and seek on partitioned indexes....
Connect bug related to min/max and partitions (may be cause of op's issues):
good luck!
The bug seems very old, I would hope that MS fixed it by now. However I'll look into that.
Now, reading the link posted above I see a possible answer to the original question:
"Ordinarily, SQL Server can only perform an index seek on the second column of a multi-column index if there exists an equality predicate on the first column. "
The key part here being the "equality". Since (as far as I know) NULLs are considered NON Equality this would explain why the index is not being picked up.
However the same blogger explains that the first column is actually the partitioning one which, in my case, is not part of the predicate.
Are we getting closer to an final answer?
November 17, 2011 at 7:52 pm
agiangone (11/17/2011)
SpringTownDBA (11/17/2011)
What column is the table partitioned on?Sql logically treats the partitioning column as a leading column of the indexes when building query plans.
Interesting comment and that could certainly change things but the I have a different question...why is the DMV suggesting that the partitioning column is included in a missing index?
The answer to your question is:
The table is partitioned on my_column (the same column I have in the select as an aggregate, see initial post)
It recommends including it because a new index doesn't have to be added to the same partition schema as the rest of the table.
November 17, 2011 at 8:55 pm
SpringTownDBA (11/17/2011)
agiangone (11/17/2011)
SpringTownDBA (11/17/2011)
What column is the table partitioned on?Sql logically treats the partitioning column as a leading column of the indexes when building query plans.
Interesting comment and that could certainly change things but the I have a different question...why is the DMV suggesting that the partitioning column is included in a missing index?
The answer to your question is:
The table is partitioned on my_column (the same column I have in the select as an aggregate, see initial post)
It recommends including it because a new index doesn't have to be added to the same partition schema as the rest of the table.
Understood thanks. So what the DMV really means is add that column to the index if you are not going to allign it, otherwise you may skip it. Is that so?
Is that also why, the DMV keeps suggesting such index even after it has been created?
November 17, 2011 at 9:40 pm
agiangone (11/17/2011)
Understood thanks. So what the DMV really means is add that column to the index if you are not going to allign it, otherwise you may skip it. Is that so?Is that also why, the DMV keeps suggesting such index even after it has been created?
I wouldn't read too much intelligence into the rationale behind the DMV's recommendations.
I'd guess that it's recommending an index on (Key_2(equality), Key_1 (inequality)) include (my_column). my_column is the clustering key, so from a practical standpoint, there is no reason to list it as an included column. But, if you were to drop the clustered index, the missing index recommendation would still be valid.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply