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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Query doesnt use right Index

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
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!!!

View Complete Post

More Related Resource Links

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

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 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!!





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?

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

select query doesnt return values


hi all, i need your assistance please.


select Comments.Id,SuroundId,ArticleId,PosterId,PostRime,Subject,Body,Visible,Users.Username
from dbo.Comments
inner join Users on Comments.PosterId=Users.Id
where SuroundId=@suround and ArticleId=@articleId

for some reason it doesnt return any values.

before i've added the inner join it worked perfectly.

i couldn't find my mistake, though i passed over it several times.



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?

MDX simple query doesnt show the years in the rows but shows (all) with value




this mdx script should return asset value for the individual years but instead it return one line with a value.

When i use the browser in ssas and select the year in rows and measure, it gives the result back as expected ie. years in the rows and values.

Can you help me out please im stuck here:




      [Measures].[Appraisal Forecasted Value] ON COLUMNS,

     [Asset Value Date].[Year] ON ROWS

FROM [CMSX_DWH_OLAP_AssetExposure]

// Result:


// Appraisal Forecasted Value

//All 1,931,290,000  I miss the Years on rows?



// This is from the browser which is correct because the year is added

Year Appraisal Forecasted Value









MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

sql query that don't run



I'm going crazy... i've got a piece of code that don't update the database... Could somebody tel me why it doesn't run??

Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdstring As String = "Update Corsi Set [Contenuto]='" & TxtText.Content & "' Where [ID]='" & Request.QueryString("Id").Replace("'", "") & "'"
        conn = New SqlConnection(SqlAnni.ConnectionString)
        cmd = New SqlCommand(cmdstring, conn)

Need help with a linq to sql query


I want to display some stats in a listview contril.  The table contains a record for every impression and for every click, and every record has a datetime field for the time and date of the impression of click.  I want to be able to display how many impressions and clicks there were in a given day and hour, something like below:

Date              Time                              Impressions    Clicks
8/20/2010                                               450              14            
                     Midnight -1:00am               150               5

How to send an email with information in query string


First I will say I googled (Binged to make Bill happy) for this but could not find anything, perhaps I am not stating what I need correctly;

I have a website that a customer creates orders (for a printing company) and after the order, I would like to email the customer a link to view the status of their order;

I need to send the CustomerID in the link so on the returning page if a CustomerID is detected in the querystring, I "auto login" the client and open the "Dashboard" for the order page for that customer.

Can anyone point me in the right direction as to how to send the link and how to parse the querystring to get the values from the link;

Thank you if you can point me in the right direction.

MDX Query parameter from SSRS


I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )



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