October 23, 2011 at 7:26 am
First, I have only one Clustered Index with columns C2, C3, C6 and C8 and table T1 and actually the Table T1 contains columns from C1 to C30. Now when I join the table T1 with a table T2 having just 3 columns C2, C3 and C8, on the execution plan it shows clustered index seek with C8 as Predicate. why is it so if the C8 is already a part of clustered index. Second, I am using row_number() over(order by C22, C25, C28) and this sorting is taking almost 50% of the execution time. I tried creating an index with some columns from clustered index include C22, C25, C28 but it further decreased the performance. Please provide your comments on both point.
October 23, 2011 at 9:53 am
Not enough information
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Also, take a look at this because, as far as I can tell, the main question is about the column order in the index. http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
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
October 23, 2011 at 10:10 am
On thing I want to know:
I have only one Clustered Index with columns C2, C3, C6 and C8 on table T1 which contains columns from C1 to C30. There is another table T2 having just 3 columns C2, C3 and C8 (and all the three are part of clustered index as well). Now when I join the table T1 with T2 for a select statement, the execution plan shows clustered index seek with C8 as Predicate. why is it so if the C8 is already a part of clustered index for T1 ?.
Second, I am using row_number() over(order by C22, C25, C28) and this sorting is taking almost 50% of the execution time. I tried creating an index with some columns from clustered index with INCLUDE C22, C25, C28 but it further decreased the performance. Please provide your feedback on how I can improve performance on row_number() with order by function (some generalized tips).
October 23, 2011 at 11:08 am
sqlnaive (10/23/2011)
Now when I join the table T1 with T2 for a select statement, the execution plan shows clustered index seek with C8 as Predicate. why is it so if the C8 is already a part of clustered index for T1 ?.
Please go and read the blog post I referenced.
Second, I am using row_number() over(order by C22, C25, C28) and this sorting is taking almost 50% of the execution time. I tried creating an index with some columns from clustered index with INCLUDE C22, C25, C28 but it further decreased the performance. Please provide your feedback on how I can improve performance on row_number() with order by function (some generalized tips).
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
As for generalised tips, you're sorting on C22, C25, C28. Include columns aren't sorted in any way, so including those columns won't eliminate the sort, it can't. Only key columns are logically sorted.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply