I have query that behaves much differently the moment I modify the Where clause
The following query runs fast!!
View Complete Post
The query is using a index scan not seek.... how can I get it to use a seek.
I am using SQL 2005
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.
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.
Can we you create a non-clustered index over a column already having 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,
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?
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