Index Scan

Question

Index Scan

ramana3327

SSCoach

Points: 19572
More actions
March 24, 2014 at 10:11 am

#291271

Hi,
I need some suggestions. I have a table with primary key and also clustered index on that primary key column. I need almost all columns from that table. When I wrote the select column names, it showing that Index scan occurred. How can I avoid that Index scan and change to index seek? When I check the fragmentation of that Index it is showing more than 34%. Is that fragmentation is ok or do I need to reorg the Index? Please let me know the answer.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

A seek is dependent on the WHERE clause being able to use the statistics that define the data stored on the index. Not seeing your query, I can't make suggestions for how to get that to work for you, but since you didn't mention a WHERE clause, I'm assuming you don't have one. Without one, a scan is the best way to retrieve the data.

If you want detailed suggestions, you'll need to post the query, the table structure and an execution plan.

34% fragmentation isn't much. But, if you're not sure if you should fragment or not, I'd suggest tracking down Michelle Ufford's index maintenance scripts, or maybe Ola Hollengren's.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

ramana3327 SSCoach Points: 19572 More actions · Answer 2

Hi Grant,

I am confused. In that case how can you say Index are fragmented? Please provide me that script. Thanks

I went I check the fragmentation of that particular Index by right click on that index and the properties and then choose the fragmentation then it is showing me the total fragmentation. Please let me know is this right method or not to check the fragmentation of Index.

Also I am using the following query to find Indexes are fragmented or not.

SELECT a.index_id, name, avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(N'dbname'), OBJECT_ID(N'tablename'), NULL, NULL, NULL) AS a

JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

The query is the better approach.

The index is fragmented. It's just not very fragmented. 34% is fairly light. As for scripts, just search for the names that I provided. You'll find the scripts you need.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

Fragmentation has nothing to do with whether you get a seek or a scan. A seek requires that you have a where clause, something to search on. If you are retrieving all rows, then a scan is the only sensible approach.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

If you select all columns then you'll get an Index scan not seek unless you have a filter with a where clause.

--

SQLBuddy