June 8, 2010 at 10:40 am
So... My problem is this: I have a table with a few million rows. Every day, I insert another 40-100k records. Every week, I discover 1 or 2 records where a search does a table scan instead of using the index. The search is a stored procedure, so it's the same query format every time. The query looks like this:
SELECT
col1,
col2,
...
col19
FROM
table
WHERE
col1 = 1
AND col2 = 1
AND col3 = 1
AND col4 = 'Jun 8 2010 12:00AM'
AND col5 = 1
AND (col6 != 'A' OR col6 IS NULL)
ORDER BY
col1,
col2,
col3
The index covers the first 4 columns. The data is correct and not out of normal ranges. The table has statistics updated on it everyday, the indexes get rebuilt every week.
If I drop and recreate the index, I might get one of the records in the index. If I drop and recreate 10 times, I usually get all records in the index. I duplicated the table, copied the data, indexed, and renamed the new table to replace the old one: that fixed 2 of the 3 records that were table scanning at the time.
This is a fairly new problem that came up a couple of months ago. I have other tables that do the very same funtion on other servers without error, some with 10 times the records.
Does anyone have any thoughts?
June 8, 2010 at 10:52 am
How much rows (in % of the total rows in the table) are returned by those 1-2 cases?
Are those rows returning more data than the other correct cases?
How are the 40-100K records inserted? A couple at a time throughout the day or all at once at night?
When are the stats updated in relation to those inserts?
June 8, 2010 at 10:53 am
James
I don't understand what you mean by "I usually get all records in the index" and "that fixed 2 of the 3 records that were table scanning". Please will you be more specific? You will probably need to post the full code, table DDL and sample data. Please search this site for "forum etiquette" or "how to get the best answers" to find out what to include in your post to get a tested solution quickest.
John
June 8, 2010 at 11:00 am
Ninja's_RGR'us (6/8/2010)
How much rows (in % of the total rows in the table) are returned by those 1-2 cases?Are those rows returning more data than the other correct cases?
How are the 40-100K records inserted? A couple at a time throughout the day or all at once at night?
When are the stats updated in relation to those inserts?
Each query returns between 1-100 rows. However, the majority don't exceed 10. The same amount is returned for a search that uses the index and for a search that does a table scan.
The record insertion is a single bulk process. The stats are updated immediately afterwards.
June 8, 2010 at 11:01 am
Well just to be sure.
What I understand is that 99.9% of the queries, the plan is an index seek. However with a couple cases you get a different plan that is slow and doesn't seem to work.
Is that pretty much what is happenning?
June 8, 2010 at 11:02 am
Ninja's_RGR'us (6/8/2010)
Well just to be sure.What I understand is that 99.9% of the queries, the plan is an index seek. However with a couple cases you get a different plan that is slow and doesn't seem to work.
Is that pretty much what is happenning?
That's it.
June 8, 2010 at 11:03 am
james.massey (6/8/2010)
Ninja's_RGR'us (6/8/2010)
How much rows (in % of the total rows in the table) are returned by those 1-2 cases?Are those rows returning more data than the other correct cases?
How are the 40-100K records inserted? A couple at a time throughout the day or all at once at night?
When are the stats updated in relation to those inserts?
Each query returns between 1-100 rows. However, the majority don't exceed 10. The same amount is returned for a search that uses the index and for a search that does a table scan.
The record insertion is a single bulk process. The stats are updated immediately afterwards.
Ok, then we need table defs with keys AND index definition.
Also we need the actual query and the correct exec plan (in .sqlplan format, attached to your message), and the wrong plan to debug this.
Anything else we won't be able to answer that one.
June 8, 2010 at 11:04 am
John Mitchell-245523 (6/8/2010)
JamesI don't understand what you mean by "I usually get all records in the index" and "that fixed 2 of the 3 records that were table scanning". Please will you be more specific? You will probably need to post the full code, table DDL and sample data. Please search this site for "forum etiquette" or "how to get the best answers" to find out what to include in your post to get a tested solution quickest.
John
I'll look into that and try and get it posted. In the meantime, what I mean is this: I run a search. The search returns 3 records using a table scan. I reindex and run the same search. Same results: 3 records and a table scan. I reindex again, and search a third time. 3 records, and it used the index.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply