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


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

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

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

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


View Complete Post


More Related Resource Links

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

  

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,

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

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


.net 4.0 breaks serialization test - MemoryStream that cannot seek

  

Folks,

I'm serializing a Dictionary<string,string> using BinaryFormatter to a class that extends MemorySteam that I call CannotSeekStream (source below).  CannotSeekStream follows the MSDN guidlines for throwing NotSupportedException from a few properties/methods.  In .net 3.5 I can serialize just fine.  In 4.0, I get a NotSupportedException from the Length property.  stacktrace is below.  At a high level, its strange that I cannot serialize to a stream that cannot seek.  Is this a bug in the framework?

 

at CannotSeekStream.get_Length() at System.IO.MemoryStream.set_Capacity(Int32 value) at System.IO.MemoryStream.EnsureCapacity(Int32 value) at System.IO.MemoryStream.WriteByte(Byte value) at System.IO.BinaryWriter.Write(Byte value) at System.Runtime.Serialization.Formatters.Binary.SerializationHeaderRecord.Write(__BinaryWriter sout) at System.Runtime.Serialization.Formatters.Binary.ObjectWriter.WriteSerializedStreamHeader(Int64 topId, Int64 headerId) at System.Runtime.Serialization.Formatters.Binary.ObjectWriter.Serialize(Object graph, Header[] inHeaders, __BinaryWriter serWriter, Boolean fCheck) at System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Serialize(Stream serializationStrea

1.)Duplicating VB Right() functionality 2.) Seek and replace multiple expressions in a single pass?

  
Two questions:
1. I want to replicate the functionality of the Right() string function in Regex in order to lop off the first four characters of certain 20 character strings if those leading characters happen to be "K3AH". Right(string,16) would be part of the VB code I'd use to do it but this is what I've come up with using Regex:

Dim re As New Regex("\bK3AH+(?=\d{16})", RegexOptions.Multiline)

Dim source$ = "SRA-7138-29		K3AH1241091742015390	K3AH1241170743153764	6454	dawni001"

source$ = re.Replace(source$, "")
And it seems to work, returning "SRA-7138-29  1241091742015390AH1241170743153764 6454 dawni001"

Is there a better (i.e.,faster, safer, whatever) way to do this?


2. Is it possible, using Regex, to search for multiple (different) expressions and make replacement for all of them while only makin a single pass through a text file?

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?

How to tell if an index exists using a SQL query on SQL CE

  
On an SQL CE based mobile app, I need to be able to tell if an index has been created using a TSQL query.  Can some one point me in the right direction on this?

How to tell if an index exists using a SQL query on SQL CE

  
On an SQL CE based mobile app, I need to be able to tell if an index has been created using a TSQL query.  Can some one point me in the right direction on this?

Parser error while using Excel Analysis Tools - Goal Seek Algorithm

  

I'm running Table Analysis Tools in Excel using the example spreadsheet DMAddins_SampleData.xlsx.  Since I'm new to these tools I’m not much familiar with the errors triggered by SSAS.  While running the "Scenarios Analysis: Goal Seek" algorithm, I'm getting the following error in the Goal Seek dialogue box:

Query (1, 46) Parser: The syntax for 'value' is incorrect.

Any idea what is causing this.

Thanks,

Zafar

 
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