Clustered Index Scan Issue | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Clustered Index Scan Issue

Hi All<br /><br />I have this follwoing simple query and have problem to solve clustered Index Scan issue.<br /><br />CREATE TABLE #Temp<br /> (col1 int null, <br /> col2 money null, <br /> col3 smallint null)<br /><br />INSERT INTO #Temp (col1,col2,col3)<br />SELECT ap1, sum(ap2), Max(ap3)<br />FROM dbo.ap<br />GROUP BY ap1<br /><br />This gave me this follwing execution plan:-<br />|–Table Insert(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[tempdb].[dbo].[#Temp___________________________________________________________________________________________________________000000000028]), SET<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[#Temp].[col3]=[Expr1003], [#Temp].[col2]=[Expr1002], [#Temp<br /> |–Top(ROWCOUNT est 0)<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1002]=If ([Expr1013]=0) then NULL else [Expr1014]))<br /> |–Stream Aggregate(GROUP BY<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[ap].[ap1]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Expr1013]=COUNT_BIG([ap].[ap2]), [Expr1014]=SUM([ap].[ap2]), [Expr1003]=MAX([ap].[ap3])))<br /> |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />[Me].[dbo].[ap].[IX_ap]), ORDERED FORWARD)<br />**************************<br />1. I have tried to ckusteed index on ap1 column and covering index on three column which ae in select statement. <br />2. I have also only create composite clustered index on three of column which you can see in above execution plan…<br /><br />But no success.. plesae help in how can make use of "Clustered Index Seek" rather Scan.<br /><br />Thanx in advance..<br /><br />
I might miss something, but why do you expect a seek when you have to read the whole table anyway? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Hi Frank Kalis thanks for rapid response… SEEK will be faster and directly go to the required rows to produce result sets… what do you think??? cos that’s what i have read on this site…or you can guide me on "INSERT INTO Select…." statement tuning.
I think it is happen cos of records are computing, grouping and inserting into temp table.. but i am not sure about it and that’s where i need help from experts on this site.. thanx a lot
Like Frank said, the reason for the index scan is that you are not filtering the data, and so all rows must be read anyway. A seek can only be faster if you are filtering data.
Yes, maybe when you can apply a WHERE clause you get a seek. Are you experiencing any performance issues? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

]]>