.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
ASPEvil
david stephan
Santhakumar Munuswamy
Fauzul Azmi
Post New Web Links

Query defaults to a Clustered Index Scan instead of using an Index Seek

Posted By:      Posted Date: October 06, 2010    Points: 0   Category :Sql Server
 

I have query that behaves much differently the moment I modify the Where clause

 

The following query runs fast!!

-----------------------

SELECT

 

e51.ENTITY_ID, e51.PERF_FREQ_CODE, e51.END_EFFECTIVE_DATE,


View Complete Post


More Related Resource Links

The query is using a index scan not seek.... how can I get it to use a seek.

  

The query is using a index scan not seek.... how can I get it to use a seek.

I am using SQL 2005


Mr Shaw

Reverts back to Index scan from index seek

  

I have a simple query to select all coumns from a table where primary key column in (list of values).  The primary key has  clustered index on primary key column.  The query was executing very effieicntly until recently (have around 4 million rows in the table and growing steadily around 8-10K rows per month).  Recently this query became a bottleneck (takes 45 secs to a min to execute) when I looked at the plan it was doing index scan on clustered primary key column index instead of index seek,  I checked on index fragmentation it was 1.48 percent.

I created a new non clustered unique index on primary key column with all other columns in include part of the index (coverage index) and created execution plan, it said index seek on new index, when i executed the query it executed in less than a sec (8milli secs to be precise), I thought everything was fine and executed the same query again this time it took 45 secs again, I looked at the plan it says index scan on the new index I created.

This trend continues every time I drop and re-create the new index it does index seek first time and switches over to index scan when I execute the same query after the first try.  I am totally confused and would like to know what is causing the issue.  Any help is highly appreciated.  Thanks in advance for any help.


Reverts back to Index scan from index seek

  

I have a simple query to select all coumns from a table where primary key column in (list of values).  The primary key has  clustered index on primary key column.  The query was executing very effieicntly until recently (have around 4 million rows in the table and growing steadily around 8-10K rows per month).  Recently this query became a bottleneck (takes 45 secs to a min to execute) when I looked at the plan it was doing index scan on clustered primary key column index instead of index seek,  I checked on index fragmentation it was 1.48 percent.

I created a new non clustered unique index on primary key column with all other columns in include part of the index (coverage index) and created execution plan, it said index seek on new index, when i executed the query it executed in less than a sec (8milli secs to be precise), I thought everything was fine and executed the same query again this time it took 45 secs again, I looked at the plan it says index scan on the new index I created.

This trend continues every time I drop and re-create the new index it does index seek first time and switches over to index scan when I execute the same query after the first try.  I am totally confused and would like to know what is causing the issue.  Any help is highly appreciated.  Thanks in advance for any help.


SQL Query index

  
Hi, I am working on WM 6.0 MC75 device.  I am creating database using SQL compact server 3.0.  I have almost 30K records in device. When I execute simple query with Order BY it takes almost a minute to fetch data. Then I used index for where clause. It becomes fast and the time reduced to 7-8 sec.  But it creates problem in Insert/Update. As this takes ____ of time in inserting data to table. I dont know how to proceed further. As i stucked here. Please give ma a direction   Regards  Shruti

SQL Insert with Index Scan Problem....

  
Hi,   I'm having a very strange problem when inserting rows into a table. Let me try to explain a little better: Table1 (for example Book) with a lot of fields and one foreign key to Table2 (idVolume) and a primary key (identity - idBook). This table has a clustered index only on the primary Key. (this table has something like 10 Millions rows) Table2 (for example Volume) with a lot of fields and a primary key (idVolume) identity. (this table has hundreds of rows)   Now, the problem starts when I make a insertion on Table2. When I made an insertion, it was taking minutes to finish, so I stopped it. After looking into the execution plan of the query, there was something very very strange, there seems to be a clustered index scan on Table1... the problem is that since Table1 has so many rows, this index scan takes forever... Can anyone explain me why does the insertion on Table2 makes an clustered index scan on Table1? It really doesn't make any sense to me.   Thanks in advance. João Teixeira

Does inserting a non clustered index reduce IO?

  
I ran a report on my prod server (Performance - Top queries by total IO) and found the following query the 2nd highest.   select * from Table1 where Col1 is null order by Col2, Col3, Col4 Table has 100,000 rows. 1 pk and about 30 columns After running it against engine tuning advisor it advised, if i created a non clustered index on Col1, Col2, Col3 and Col4 it would increase the performance by 77%. But when i run the command just by it self it runs and completes instantaneously. What am i missing? Should i create the index to reduce the IO's as the Advisor suggests?    

Clustered vs NC index storage differences?

  
Does clustered index takes more disk space than NC for the same filed(s) for large tables? Also is CI takes longer (time) to build than NC if you have large tables? Any microsoft articles supporting these aruguments.

Check Index Fragmentation query

  
Hi, I have ran the following query to view the fragmentation on my large table with 1000+ pages. Now the results I got are moslty at index level 0 and 1 and most objects are fragmented 90+%. My question to the experts is that the query is displaying fragmentation mostly at Index level 0 and 1. Do I need to look or just Level = 0 is fine? The query is SELECT DB_NAME(SDDIPS.[database_id]) AS [database_name], OBJECT_NAME(SDDIPS.[object_id], DB_ID()) AS [object_name], sddips.[object_id] as [Object_ID] ,SSI.[name] AS [index_name], SDDIPS.partition_number, SDDIPS.index_level ,SDDIPS.index_type_desc, SDDIPS.alloc_unit_type_desc, SDDIPS.avg_fragmentation_in_percent], SDDIPS.[page_count] FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'detailed') SDDIPS INNER JOIN sys.sysindexes SSI ON SDDIPS.OBJECT_ID = SSI.id AND SDDIPS.index_id = SSI.indid WHERE SDDIPS.page_count > 1000 AND avg_fragmentation_in_percent > 15 AND index_type_desc <> 'HEAP' ORDER BY OBJECT_NAME(SDDIPS.[object_id], DB_ID()), index_id

How index will effect in update query

  
Hi All, I have one update query it has lot of joins with other table ,If we add index in the secondary table with this join condition will it improve the performance? eg: UPDATE FunctionalAcknHeaders  SET cStatus = 'E'  FROM FunctionalAcknHeaders fh   LEFT JOIN OrderHeaders oh ON cast(oh.nOrderHeaders_id as varchar(10)) = 'abc'  WHERE oh.nOrderHeaders_id is null   AND fh.cStatus = 'R'   if i create index on OrderHeaders  table nOrderHeaders_id, will it improve the performance ? Thanks, Roshan

Query doesnt use right Index

  
Hi experts, This is really strange issue.  I have created a table on my dev environment first. Copied the data from another table. Created Indices / Pk and then used a query which doesnt want to use right index. I have NC Index on my Lastupdate , Log , type colmns and I have CI on my Identity col called "CluIndex". And when I use a query "select * from table where lastupdate >= '09/16/2010'", it takes for ever to get the result back. And when I check the Display query exec plan, it shows its using CI which is not right. If I use query hint with the right index, the results are quick. So basically query plan is not using the right index. Weird thing is that, this same table (I created myself with same steps) uses the right Index in production. Very strange!!!

Table scan going on apart from having an Index

  

Hi,

I have a stored procedure where the performance is bad. The Execution plan shows 5 table scans each of cost 12%. The data is being pulled from the table using the following where clause.

where (datestamp>getdate() - 'sep28 1902 12:00 AM')

I already have an index on Datestamp column but still Table scan is happening. There is no restriction on avoiding index is specified in the query. 

If the index is place inappropriately there would be index scan but here I have table scan.

Could anyone help me in finding out why this is happening.

 

-Thank you,

Jayasree


Table scan going on apart from having an Index

  

Hi,

I have a stored procedure where the performance is bad. The Execution plan shows 5 table scans each of cost 12%. The data is being pulled from the table using the following where clause.

where (datestamp>getdate() - 'sep28 1902 12:00 AM')

I already have an index on Datestamp column but still Table scan is happening. There is no restriction on avoiding index is specified in the query. 

If the index is place inappropriately there would be index scan but here I have table scan.

Could anyone help me in finding out why this is happening.

 

-Thank you,

Jayasree


non-clustered index

  

 Hi,

Can we you create a non-clustered index over a column already having clustered index

 

 


Do I really need Clustered Index??

  

I read in so many articles, some books and listen some expert's words and even microsoft recommended that, A table with Clustered Index will performs better than Heap in most of the cases. I too believed same until today. But today, I saw in my project, out of 2,000 tables we have, only 50-60 tables have CI only, and remaining tables are all heap only. Still our 90% of stored proc. always will takes less than 1 or 2 seconds, even when they tested with millions of data. 

            I never had chance to look into the DB, as i will not work directly with proj. dev. Today only I saw this. i asked one of my colleague about this. He replied that, when insertion/updation/deletions performed, CI will take more time, If it is heap, it wont take more time. Yes, What ever he is telling is true. It needs to adjust the indexes, page splits also might happens...  

a Heap has following advantages over CI.

1. Insertion/Updations/Deletions faster...

        Yes, as its an web application, which will be continously use by the people, no of DML operations will happen. 

2. Coming to Selections, I saw the tables have Non-CI, which almost covers all the queries.... and more over the Because of the Paging implementation, always, only 25-50 records will be selected,  


Guid clustered index fast select and slow insert

  

Hi,

Don't know if this question is in wrong forum but I didnt find a better place.

I have a table called "CompanyApplications". The first column is a guid and is the PK (not clustered). The other colum is also a guid and is the company identifier. This is a guid because it comes from another table (the "company" table), an old table used for other stuff. Then I have a third and forth column for "ApplicationURL" and "ApplName.

My application will do alot of (since 30 000+ users) "select ApplName, ApplicationURL from CompanyApplications where companyId='<the company guid of the user>' order by ApplName). It will make very few inserts into this table. Only when one is inserting a new application.

I have some questions about this since I know it's not good to have a guid as clustered since it will have SQL Server to always resort the clusted index (because guids are "random"):

1. Is it wrong to have the companyId column as clusted in this example? If so, what is better?

2. I know inserts will be relatively slow when using guid as clustered. But in my case the insert does not need to be fast. Will the slow insert also affect selects from other users. I mean, will select's be slow for other users when the index is resorted becuase one user make an insert?

 


clustered index

  

hi,

i have a query which is running slow so i've been adding index's to various fields to test to see if i can find any improvements.

i have a problem though as a program runs and instead of updating values deletes a record the by the id field and then re-adds with the new values (yes stupid i know).

what i've noticed is that there is an index on that field set to 'create as clustered'.

I've always avoided the clustered index as i've heard its a bad option, but am wondering that if this value is being deleted and re-created and is set to the clustered mode if this might be a problem?

sql 2000.

thanks


is there a query for index data in RAM?

  
Essentially I'm looking for a query that will give me the size of each table and index and the percentage of that object that is in RAM presently. I've got several queries to report the total size of the tables and indexes. I can't see how to determine if any of those data pages are presently in RAM. Is that information available with a performance query of some kind?
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend